Beginner:Need help with Foreign Keys.

My current php can be found bellow. (I’m a complete beginner and have no idea really what I am doing).

I have a database using MySql which has two tables. Customer and Orders, of which I have a primary key of CustID which auto increments in Customer and a primary key of OrderID which auto increments in Orders. What I’m trying to do is get the foreign key of OrderID to represent its value it has in the Orders table in the Customer table. As currently it always stays at 0.

E.g. I post my form from my website and the values go into their designated columns. Both primary keys increment by 1 but the foreign key of OrderID always stays at 0. Please help :slight_smile:

[php]

<?php //connecting to lamp and my database $con = mysql_connect("lamp.soi.city.ac.uk", "ABMD628", "100030519"); mysql_select_db("ABMD628"); //establishing my variables. $FirstName=$_POST['firstname']; $SurName=$_POST['surname']; $HouseNameNo=$_POST['housenameno']; $StreetName=$_POST['streetname']; $City=$_POST['city']; $PostCode=$_POST['postcode']; $TelephoneNumber=$_POST['telephonenumber']; $EMail=$_POST['email']; $DesktopOrdered=$_POST['desktopordered']; $QuantityOrdered=$_POST['quantityordered']; $Overclocking=$_POST['overclocking']; $CableMngmnt=$_POST['cablemngmnt']; $SystemCooling=$_POST['systemcooling']; $TechSupport=$_POST['techsupport']; //inserting the data into my customer database. $sql1 ="INSERT INTO Customer (FirstName,SurName,HouseNameNo,StreetName,City,PostCode,TelephoneNumber,EMail) VALUES('$FirstName','$SurName','$HouseNameNo','$StreetName','$City','$PostCode','$TelephoneNumber','$EMail');"; //inserting the data into my orders database $sql2 ="Insert INTO Orders(DesktopOrdered,QuantityOrdered,Overclocking,CableMngmnt,SystemCooling,TechSupport) VALUES ('$DesktopOrdered','$QuantityOrdered','$Overclocking','$CableMngmnt','$SystemCooling','$TechSupport');"; //runs the first query of inserting into customer database. mysql_query($sql1, $con); //runs the second query of inserting into the orders database. mysql_query($sql2, $con); ?>

[/php]

Did you create foreign key in the Customer table with reference to corresponding OrderID field in the Orders table? Here is the example from dev.mysql.com:

CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;

Heya, I used the command,

ALTER TABLE Customer ADD FOREIGN KEY (OrderID) REFERENCES Orders(OrderID);

As I had made the Customer table previously with a column for OrderId to be set once I created the Orders table.

To be honest I do not use foreign keys myself. But looks like in your case, “parent” table is Orders. So, you need to create Order record first, then Customer record. Also, somewhere in the “insert into Customer…” you need to refer to a foreign key value created in the Orders table.

Heya,

I’ve changed it around slightly in my database to only have the CustID reflect in my Orders table as the foreign key making Customer the parent table again I believe?

Sofar I’ve been reading and found this, bit of php, which I’m trying to adapt to help solve my problem.
[php]
$sql2 = “SELECT MAX(CustID) AS ‘CustID’ from Customer;”;
$custID = “”;
$Result = mysql_query($sql2,$con);
while($row = mysql_fetch_object($Result)){
$custID = $row->CustID;
}
[/php]

The method I inserted in the previous post worked :).

Sponsor our Newsletter | Privacy Policy | Terms of Service