brussell Posted December 6, 2006 Share Posted December 6, 2006 Trying to insert and update to 2 tables (customerdetails and rackallocation) but the foreign key appears to be causing an issue: Please can you help! PS. Have to excuse my ignorance but I'm new at this and learning php, mysql as we speak. Any help would be much appreciated.CREATE TABLE customerdetails ( companyname varchar(50) NOT NULL, contactname varchar(30) default NULL, PhoneNumber varchar default NULL, EmailAddress varchar(50) default NULL, PRIMARY KEY (companyname)) ENGINE=InnoDB;CREATE TABLE rackallocation( racklocation VARCHAR(15) NOT NULL, racktype CHAR(4) default NULL, companyname VARCHAR(50) NOT NULL, gwname VARCHAR(30) default NULL, PRIMARY KEY (racklocation), FOREIGN KEY (companyname) REFERENCES customerdetails (companyname) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=INNODB;<?phpif (!$_POST) { //haven't seen the form, so show it $display_block = " <form method=\"post\" action=\"".$_SERVER["PHP_SELF"]."\"> <p><h2><strong><u>Customer Details:</u></strong></h2><br/> <p><strong>Company Name:</strong><br/> <input type=\"text\" name=\"companyname\" size=\"50\" maxlength=\"100\"> </p> <p><strong>Contact Name:</strong><br/> <input type=\"text\" name=\"contactname\" size=\"30\" maxlength=\"100\"></p> <p><strong>Phone Number:</strong><br/> <input type=\"text\" name=\"phonenumber\" size=\"30\" maxlength=\"50\"> </p> <p><strong>Email Address:</strong><br/> <input type=\"text\" name=\"emailaddress\" size=\"50\" maxlength=\"100\" </p> <p><h2><strong><u>Rack Allocation</u></strong></h2><br/></p> <p><strong>Rack Location:</strong>(E.G mc3/12)<br/> <input type=\"text\" name=\"racklocation\" size=\"15\" maxlength=\"20\"></p> <p><strong>Rack Type:</strong><br/> <input type=\"text\" name=\"racktype\" size=\"4\" maxlength=\"5\"> </p> <p><input type=\"submit\" name=\"submit\" value=\"Add Entry\"></p> </form>"; } else if ($_POST) { //time to add to tables, so check for required fields if (($_POST["companyname"] == "")) { header("Location: addentry.php"); exit; } //connect to database $mysqli = mysqli_connect("localhost", "root", "*******", "easyrack"); //add to master_name table $add_master_sql = "REPLACE INTO customerdetails (companyname, contactname, phonenumber, emailaddress) VALUES ('".$_POST["companyname"]."', '".$_POST["contactname"]."', '".$_POST["phonenumber"]."', '".$_POST["emailaddress"]."')"; $add_master_res = mysqli_query($mysqli, $add_master_sql) or die(mysqli_error($mysqli)); //something relevant, so add to rackallocation table $add_rackallocation_sql = "REPLACE INTO rackallocation (racklocation, racktype) VALUES ('".$_POST["racklocation"]."','".$_POST["racktype"]."')"; $add_rackallocation_res = mysqli_query($mysqli, $add_rackallocation_sql) or die(mysqli_error($mysqli)); mysqli_close($mysqli); $display_block = "<p>Your entry has been added. Would you like to <a href=\"addentry.php\">add another</a>?</p>";}?><html><head><title>Add an Entry</title></head><body><h1><u>Add an Entry</u></h1><?php echo $display_block; ?></body></html>ERROR:Cannot add or update a child row: a foreign key constraint fails (`easyrack/rackallocation`, CONSTRAINT `rackallocation_ibfk_1` FOREIGN KEY (`companyname`) REFERENCES `customerdetails` (`companyname`) ON DELETE CASCADE ON UPDATE CASCADE)Thanks in advance Quote Link to comment Share on other sites More sharing options...
joquius Posted December 6, 2006 Share Posted December 6, 2006 As far as I can see you haven't selected a database. Never worked with mysqli functions but I'm pretty sure they're not far from mysql functions. Also you need to be using either INSERT or UPDATE, not REPLACE which i'm not sure i've ever seen. Quote Link to comment Share on other sites More sharing options...
brussell Posted December 6, 2006 Author Share Posted December 6, 2006 HI there,The database is selected in connect to database - mysqli_connect() (database is called easyrack).REPLACE deletes then inserts the specified data.I think I'm going to have to rethink my database layout as I'm stumbling across errors where certain rows are being deleted when they should infact just be UPDATED. UPDATE is going to be what I need but I'm going to have to rethink my keys also as it is again causing problems.....back to the drawing board as they say!Any other advice much appreciated. 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.