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>
