portabletelly Posted May 14, 2007 Share Posted May 14, 2007 Hi I am a first timer at posting to this forum but I was wondering if someone can help me. I am running PHP 5 mysql 5.0 on apache on windows xp os. I have a database created called portal which has two tables. customer_site_notes and customer_tb. The issue I am having is trying to delete a customer who's primary key=id is in customer_tb and is a forigen key in in customer_site_notes table. I have the following page called delete_customer.php which posts the selected customer to delete_customer_fromdb.php. I belive the issues is with this bit of code in delete_customer_fromdb.php. mysql_query("DELETE FROM customer_tb WHERE name='$delcust'"); mysql_query("DELETE FROM customer_site_notes WHERE id='$custid'"); echo "<p><b>Deleting customer $delcust !!!! </b></p>"; The issue is that when I submit the form to delete the customer sometimes it deletes and sometimes it does not. I belive php is sending the query to mysql and mysql cant seem to catch up or somthing. You see sometimes it seems to work and delete the customer out of both tables. Other times I have to delete the customer twice from the page. Is it possible to put a like a 5 second pause in between the two mysql_query statements? Or is there a better solution. On a side note all the meta tags in delete_customer.php is to try and always display current customers in the dynamic list doesnt seem to work unlesss I refresh the page. delete_customer.php <html> <head> <meta http-equiv="cache-control" content="no-cache"> <!-- tells browser not to cache --> <meta http-equiv="expires" content="0"> <!-- says that the cache expires 'now' --> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="refresh" content="15"> </head> <body> <?php include("connect.php"); include("selectdb.php"); echo '<form method="POST" action="delete_customer_fromdb.php">'; echo '<select name=del_customer>'; $query = "SELECT name FROM customer_tb"; $result = mysql_query($query, $link); if(mysql_num_rows($result)) { // we have at least one user, so show all users as options in select form while($row = mysql_fetch_row($result)) { print("<option value=\"$row[0]\">$row[0]</option>"); } } else { $test="else"; print("<option value=\"\">No users created yet</option>"); } echo '<input type="submit" value="Delete">'; echo '</select>'; echo '</form>'; ?> <p> </p></body> </html> delete_customer_fromdb.php <html> <head> <meta http-equiv="refresh" content="5;URL=delete_customer.php"> </head> <body> <?php include("connect.php"); include("selectdb.php"); $delcust =$_POST["del_customer"]; //Get the customers Id for the customers name $query = "SELECT id FROM customer_tb WHERE name='$delcust'"; $result = mysql_query($query, $link); if(mysql_num_rows($result)) { while($row1 = mysql_fetch_row($result)) { $custid=$row1[0]; } } else { //print("<option value=\"\">Pick Customer</option>"); } mysql_query("DELETE FROM customer_tb WHERE name='$delcust'"); mysql_query("DELETE FROM customer_site_notes WHERE id='$custid'"); echo "<p><b>Deleting customer $delcust !!!! </b></p>"; ?> </body> </html> Quote Link to comment Share on other sites More sharing options...
bubblegum.anarchy Posted May 15, 2007 Share Posted May 15, 2007 Consider using a join, something like: DELETE customer_tb, customer_site_notes FROM customer_tb LEFT JOIN customer_site_notes ON customer_tb.id = customer_site_notes.id WHERE customer_tb.name = '$delcust'; Use an INNER JOIN with an enforced 1:1 relationship between customer_tb and customer_site_notes. Quote Link to comment Share on other sites More sharing options...
portabletelly Posted May 15, 2007 Author Share Posted May 15, 2007 Thanks, I will give it a go tonight and see how it goes! Quote Link to comment Share on other sites More sharing options...
portabletelly Posted May 15, 2007 Author Share Posted May 15, 2007 Thanks Bubblegum, I tried this but it didnt seem to work. Maybe my php syntax is wrong. ************************************************ $Delete_Query = "DELETE customer_tb, customer_site_notes FROM customer_tb LEFT JOIN customer_site_notes ON customer_tb.id = customer_site_notes.id WHERE customer_tb.name ='$delcust'"; //mysql_query("DELETE FROM customer_tb WHERE name='$delcust'"); //mysql_query("DELETE FROM customer_site_notes WHERE id='$custid'"); echo $Delete_Query; mysql_query($Delete_Query) or die('Error, insert query failed'); *************************************************** The page keeps coming back with Error, insergt query failed. The echo is reporting as DELETE customer_tb, customer_site_notes FROM customer_tb LEFT JOIN customer_site_notes ON customer_tb.id = customer_site_notes.id WHERE customer_tb.name ='new customer' Quote Link to comment Share on other sites More sharing options...
fenway Posted May 15, 2007 Share Posted May 15, 2007 Check the output of mysql_error() in your die() statement. Quote Link to comment Share on other sites More sharing options...
portabletelly Posted May 15, 2007 Author Share Posted May 15, 2007 echo is DELETE customer_tb, customer_site_notes FROM customer_tb LEFT JOIN customer_site_notes ON customer_tb.id = customer_site_notes.id WHERE customer_tb.name ='test customer' mysql error is Cannot delete or update a parent row: a foreign key constraint fails (`portal/customer_site_notes`, CONSTRAINT `customer_site_notes_ibfk_1` FOREIGN KEY (`id`) REFERENCES `customer_tb` (`id`)) Quote Link to comment Share on other sites More sharing options...
portabletelly Posted May 15, 2007 Author Share Posted May 15, 2007 Ended up fixing this by changing the customer_site_notes id to cascade on delete. 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.