Willo009 Posted February 17, 2011 Share Posted February 17, 2011 I have a problem with Foriegn Keys, ive been trying to work it out now for over a week. Ive watched Tutorials after Tutorials, copied what they have said but its not working. And its only a simple Database with 2 Tables which require a FK join. I have 2 tables as shown below: CustomerData (parent table) ID Name Address Email PhoneNumber MobileNumber Country City Password PetData (child table) CustomerID --foreign key linked to ID form parent table Type Breed Age Colour Price SalesPoint Description Image1 Image2 But i just cannot get it to work i keep getting this error: Cannot add or update a child row: a foreign key constraint fails (`petseen`.`petdata`, CONSTRAINT `petdata_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `customerdata` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE) I need the customer to be able to upload as many pets as they wish. any help would be great and many thanks. Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 17, 2011 Share Posted February 17, 2011 Hi Can you post the SQL you are getting the error with. Also if poss the actual table definitions (so I can be lazy and just paste them in to have a play). All the best Keith Quote Link to comment Share on other sites More sharing options...
Willo009 Posted February 17, 2011 Author Share Posted February 17, 2011 Hi Keith Thanks for your help with this, many sleepless nights trying i have had. Its my 1st time using a Database so i am newer than new to all this. As will show here lol, could you tell me how to copy and paste all this info as i do not know where i would find it. Thanks Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 17, 2011 Share Posted February 17, 2011 Hi Are you using phpmyadmin (makes life easy if you are). From phpmyadmin go to the database you are using and at the top you will see an export tab. Click on this. That will come up with a page where you can select the tables to export from that database and on the right options for what to export. Select "Structure" for the table definitions (can export data as well, which might help if you just have a few test records) and then click "Go" at the bottom of the screen. All the best Keith Quote Link to comment Share on other sites More sharing options...
Willo009 Posted February 17, 2011 Author Share Posted February 17, 2011 Thankyou hope i got it right. Its a pet classifieds website im trying to get up and running, and trying to create a database that will allow a user to insert as many Ads as they wish. I have followed tutorials but its still not running right and im lost. Thanks for your help Keith [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 17, 2011 Share Posted February 17, 2011 Hi Can see the problem. In the petdata table the primary key is customerid which is set up as not null auto increment, but which is also linked as a foreign key to customer data. What you need is a separate primary key on petdata. CREATE TABLE IF NOT EXISTS `petdata` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `CustomerID` int(11) NOT NULL, `Type` varchar(20) NOT NULL, `Breed` varchar(20) NOT NULL, `Age` varchar(20) NOT NULL, `Colour` varchar(20) NOT NULL, `Price` varchar(20) NOT NULL, `SalesPoint` mediumtext NOT NULL, `Description` longtext NOT NULL, `Image1` blob NOT NULL, `Image2` blob NOT NULL, `VideoClip` blob NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; Al the best Keith Quote Link to comment Share on other sites More sharing options...
Willo009 Posted February 17, 2011 Author Share Posted February 17, 2011 Keith Thankyou so much for your help. Quote Link to comment Share on other sites More sharing options...
Willo009 Posted February 17, 2011 Author Share Posted February 17, 2011 I have set-up a new Primary key as PetID and set both PetID & CustomerID as shown but im getting this error Column 'CustomerID' cannot be null Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 17, 2011 Share Posted February 17, 2011 Hi Can you post the INSERT statement that you are using? You will need to specify the value of customerid (ie, in your test data 5 as that is the id of the only customer on the table). All the best Keith Quote Link to comment Share on other sites More sharing options...
Willo009 Posted February 17, 2011 Author Share Posted February 17, 2011 Hope this is what you asked for Keith, what i did i cleared the tables of all data. And then registered again so the id=1 // WA Application Builder Insert if (isset($_POST["Insert_x"])) // Trigger { $WA_connection = $petsCon; $WA_table = "petdata"; $WA_sessionName = "WADA_Insert_petdata"; $WA_redirectURL = "petdataAdmin_Results.php"; if (function_exists("rel2abs")) $WA_redirectURL = $WA_redirectURL?rel2abs($WA_redirectURL,dirname(__FILE__)):""; $WA_keepQueryString = true; $WA_indexField = "CustomerID"; $WA_fieldNamesStr = "CustomerID|Type|Breed|Age|Colour|Price|SalesPoint|Description|Image1|Image2|VideoClip"; $WA_fieldValuesStr = "".$_SESSION['id'] ."" . "|" . "".((isset($_POST["Type"]))?$_POST["Type"]:"") ."" . "|" . "".((isset($_POST["Breed"]))?$_POST["Breed"]:"") ."" . "|" . "".((isset($_POST["Age"]))?$_POST["Age"]:"") ."" . "|" . "".((isset($_POST["Colour"]))?$_POST["Colour"]:"") ."" . "|" . "".((isset($_POST["Price"]))?$_POST["Price"]:"") ."" . "|" . "".((isset($_POST["SalesPoint"]))?$_POST["SalesPoint"]:"") ."" . "|" . "".((isset($_POST["Description"]))?$_POST["Description"]:"") ."" . "|" . "".((isset($_POST["Image1"]))?$_POST["Image1"]:"") ."" . "|" . "".((isset($_POST["Image2"]))?$_POST["Image2"]:"") ."" . "|" . "".((isset($_POST["VideoClip"]))?$_POST["VideoClip"]:"") .""; $WA_columnTypesStr = "none,none,NULL|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''|',none,''"; $WA_fieldNames = explode("|", $WA_fieldNamesStr); $WA_fieldValues = explode("|", $WA_fieldValuesStr); $WA_columns = explode("|", $WA_columnTypesStr); $WA_connectionDB = $database_petsCon; mysql_select_db($WA_connectionDB, $WA_connection); if (!session_id()) session_start(); $insertParamsObj = WA_AB_generateInsertParams($WA_fieldNames, $WA_columns, $WA_fieldValues, -1); $WA_Sql = "INSERT INTO `" . $WA_table . "` (" . $insertParamsObj->WA_tableValues . ") VALUES (" . $insertParamsObj->WA_dbValues . ")"; $MM_editCmd = mysql_query($WA_Sql, $WA_connection) or die(mysql_error()); $_SESSION[$WA_sessionName] = mysql_insert_id(); if ($WA_redirectURL != "") { if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) { $WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"]; } header("Location: ".$WA_redirectURL); } } ?> Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 17, 2011 Share Posted February 17, 2011 Hi That gets a bit complex. I suspect that somewhere $_SESSION['id'] is losing its value (remember that it is case sensitive so 'id' is not the same as 'Id'). However it could also be lost in the object you are storing the data in. If you echo out $WA_Sql just after it is set up then we can see what it is trying to insert. All the best Keith Quote Link to comment Share on other sites More sharing options...
xylex Posted February 17, 2011 Share Posted February 17, 2011 You're calling session_start() after your using $_SESSION['id']. Move the session_start() line up to the top of your script. Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 17, 2011 Share Posted February 17, 2011 Hi Good spot. I agree that needs sorting. All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.