Reading from a file

I have started playing with a new database, that will allow the costing of simple recipes. Here's the schema (so far):

DROP TABLE IF EXISTS recipe.ingredient;
DROP TABLE IF EXISTS recipe.item;
DROP TABLE IF EXISTS recipe.unit;

CREATE TABLE recipe.unit (
 id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 PRIMARY KEY (id)
) TYPE = INNODB;

CREATE TABLE recipe.item (
 code VARCHAR(10) NOT NULL,
 name VARCHAR(50) NOT NULL,
 unitid INT NOT NULL,
 cost DECIMAL(10,2) NOT NULL,
 PRIMARY KEY (code),
 FOREIGN KEY (unitid) REFERENCES unit(id)
) TYPE = INNODB;

CREATE TABLE recipe.ingredient (
 recipe_code VARCHAR(10) NOT NULL,
 ingred_code VARCHAR(10) NOT NULL,
 quantity DECIMAL(10,2) NOT NULL,
 FOREIGN KEY (recipe_code) REFERENCES item (code),
 FOREIGN KEY (ingred_code) REFERENCES item (code)
) TYPE = INNODB;

First thing to do is to populate the unit table.

C:\Documents and Settings\Tim\My Documents\DB\Recipe\sql>more unit.txt
1,litre
2,each
3,gram

C:\Documents and Settings\Tim\My Documents\DB\Recipe\sql>

mysql> LOAD DATA LOCAL INFILE 'unit.txt' REPLACE INTO TABLE recipe.unit FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
    -> ;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from unit;
+----+-------+
| id | name  |
+----+-------+
|  1 | litre |
|  2 | each  |
|  3 | gram  |
+----+-------+
3 rows in set (0.00 sec)

mysql>

Note: the stupid 'LINES TERMINATED' option used because I'm running on a stupid OS that uses Character Return Line Feeds for it's line termination.

Then the PHP script to load some raw materials:

C:\Documents and Settings\Tim\My Documents\DB\Recipe\php>more rawingred.txt
EGG,Egg,2,15
FLOUR,Flour,3,0.01
MILK,Milk,1,75

C:\Documents and Settings\Tim\My Documents\DB\Recipe\php>more rawingredload.php
<?php
function openMyDB($dbname)
{
include("DBInfo.inc");
$con = mysql_connect($mydbhost,$mydbuser,$mydbpassword);
if (!$con) {
        die('Could not connect: ' . mysql_error());
}
mysql_select_db($dbname, $con);
return $con;
}

$con = openMyDB("recipe");

$file=fopen("rawingred.txt","r") or exit("Unable to open file!");
while(!feof($file)) {
  $ingred = fgets($file);
  list($code, $name, $unitid, $cost) = split(',', $ingred);
  echo "$code\t$name\t$unitid\t$cost\n";
  $sql="INSERT INTO item (code, name, unitid, cost)
          VALUES ('$code','$name','$unitid','$cost')";
  if (!mysql_query($sql,$con)) {
     die('Error: ' . mysql_error());
  }
  echo "1 record added\n";
  }
fclose($file);

?>
C:\Documents and Settings\Tim\My Documents\DB\Recipe\php>php rawingredload.php
EGG     Egg     2       15

1 record added
FLOUR   Flour   3       0.01

1 record added
MILK    Milk    1       75
1 record added


C:\Documents and Settings\Tim\My Documents\DB\Recipe\php>

mysql> select * from item;
+-------+-------+--------+-------+
| code  | name  | unitid | cost  |
+-------+-------+--------+-------+
| EGG   | Egg   |      2 | 15.00 |
| FLOUR | Flour |      3 |  0.01 |
| MILK  | Milk  |      1 | 75.00 |
+-------+-------+--------+-------+
3 rows in set (0.00 sec)

mysql>