brussell

Trying to SELECT * from 2 tables (customerdetails & rackallocation (as below)) but am getting ambiguous errors for the 'companyname' column due to it being a primary key in 'customerdetails' and foreign key in 'rackallocation'. Please can you help spot my error or advise on additional code required. CREATE TABLE `customerdetails` ( `companyname` varchar(50) NOT NULL,  `contactname` varchar(30) default NULL, `phonenumber` varchar(15) default NULL, `emailaddress` varchar(50) default NULL, PRIMARY KEY  (`companyname`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 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`), KEY `companyname` (`companyname`), FOREIGN KEY (`companyname`) REFERENCES `customerdetails` (`companyname`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1 QUERY: $query = "select * from customerdetails, rackallocation where ".$searchtype." like '%".$searchterm."%' and customerdetails.companyname = rackallocation.companyname"; $result = mysql_query($query) or die('Problem with query: ' . $query . '<br />' . mysql_error()); $num_results = mysql_num_rows($result);

Have you considered something like 'server2go' or 'XAMPP' which is a webserver which can run from cd, or usb. They generally include PHP, MySql, PHPMyAdmin, Apache. A simple plug and play. You can then store your database locally on the CD and people can use it as they see fit....dont know if this may help.

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.

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; <?php if (!$_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