witham Posted July 20, 2006 Share Posted July 20, 2006 I have a manufacturers database with two tables one containing the manufacturers manid and manname and the other holds the product information. They are linked using the manid & manno which are common to both. I am having no problem deleting the manufacturer:$sqlDELETE = "DELETE from man where manid = '$Manufacturer';";But this still leaves the product information in the other table that I would like to get rid of even though it is not displayed it takes up room and is untidy.This is the code where the user selects the manufacturer to delete:<title>Add Prod</title></head><table align = "center" border=0><tr><td colspan = 3 align = "center"><table width="100" border="0" cellspacing="10" cellpadding="10"> <tr> <th scope="row"><img src="car.jpg" width="150" height="150"></th> <td><img src="gears.jpg" width="150" height="150"></td> <td><img src="wheel.jpg" width="150" height="150"></td> <td><img src="metalworking.jpg" width="150" height="150"></td> </tr></table></td></tr><!-- open the form and send the results to welddelman.php for processing --><form action="welddelman.php" method="get"><hr><tr><td colspan = 3><h3 align = "center"><i><b>MANUFACTURER DELETION</b></i></h3></td><tr><br><br><br><br<tr><td colspan = 2><h2 align=center><i>This screen allows the deletion of Manufacturers within W.E.L.D.</h2></td></tr><tr><td><b>Manufacturer:</b></td><?//initiate the $result variable and populate it with the data from the $sqlMANUFACTURERS table$result = mysql_query($sqlMANUFACTURERS, $dblink) or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");// mysql_fetch_array fetches the results from the query a row at a time each time it's called// the result is returned as an array that can be referenced either by field name or by it's index// this data is then put into a drop down box popualted by the updated manufacturers listecho '<td><select name="Manufacturer">';while ($row = mysql_fetch_array ($result)) // loop through the rows outputing them as html table rows { echo '<option value="' . $row["MANID"] . '">' . $row["MANNAME"] . "</option>"; } ?><!-- put in a submit button to send the data to welddelman.php --><tr><td colspan = 2 align = center><button type="submit">DELETE MANUFACTURER FROM EQUIVALENTS DATABASE</button></td></tr><!-- put in links to bring the site together --><tr><td colspan = 2 align = center><a href="querytry1.php">SEARCH FOR A PRODUCT</td></tr><tr><td colspan = 2 align = center><a href="addprod.php">ADD A PRODUCT</td></tr></form><!-- close out the form --></body></html> And this is the code for the handling page://$sqlCOMPARE = "SELECT * from man where '$addman' = manname;";// the sql query to send to the database so that the entry can be added into it$sqlDELETE = "DELETE from man where manid = '$Manufacturer';";//$sqlINSERT = strtoupper($sqlINSERT);//put in the sql statement that will display the results$sqlSELECT = "SELECT * from man order by manname;";// mysql_connect connects to the database server and returns a link to the the resource$dblink = @mysql_connect("$dbhost","$dbuser","$dbpass") or die("<p><b>Could not connect to database server: ($dbhost)</b></p>\n");// mysql_select_db selects a database to use on the database server pointers to by $dblink// the @ sign before the command supresses any error messages@mysql_select_db ($dbname , $dblink) or die ("<p><b>Could not connect to database ($dbname)</b></p>\n");// now execute the query to compare the table data to display and if the number of rows returned// is less than zero send the user straight back to Addman.html as the entry is a duplicate//$result = mysql_query($sqlCOMPARE, $dblink)// or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");//if ( mysql_num_rows($result) > 0 ){ // header("Location: Addman.html"); // die ("opps"); //}// now execute the next query to insert the data into the table$result = mysql_query($sqlDELETE, $dblink) or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");// now execute the next query to return the table data to display$result = mysql_query($sqlSELECT, $dblink) or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><head><title>W.E.L.D.</title><!-- set the css style for the page --><style type="text/css">tr {color: white; font-family: arial; font-weight: bold}body {background: navy}</style></head><body><?// output the table and the first row headingsecho '<table border="1" align="center">' . "\n";echo '<tr><td colspan = 3 align = "center"><img alt="addmanufact.JPG" src="addmanufact.JPG" height="155" width="550"></td></tr>';echo "<tr align=center bgcolor = red><td>UPDATED MANUFACTURER LIST</td></tr>\n";// mysql_fetch_array fetches the results from the query a row at a time each time it's called// the result is returned as an array that can be referenced either by field name or by it's indexwhile ($row = mysql_fetch_array ($result)) // loop through the rows outputing them as html table rows { // $row["fieldname"] returns the content for the field in the current row echo "<tr><td align = center>" . $row['MANNAME']. "</td></tr>"; }// close html table tagecho "</table>\n";// the mysql_free_result command removes any resources relating to the query results// this happens automatically at the end of the script but still better to free up nowmysql_free_result ($result);// the mysql_close command severs the link to the database, with scripts that make multiple// queries on the same database the command only needs to be done once after all queries are completed@mysql_close ($dblink) or die( "<p><b>Error while closing connection to database server:" . "($dbhost)</b></p>");?><!-- put in a link that brings the site together --><p><a href="Addman.php">Return to the data entry page </a></p></body></html>I would really appreciate any helpThanks Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/ Share on other sites More sharing options...
cmgmyr Posted July 20, 2006 Share Posted July 20, 2006 Why do you have the id and name of the manufacturer in both tables, it would be a lot easier to just have the name in the "man" table. It will also cut down the amount of space your databse uses and actually make it faster. you can use $sql = ("DELETE FROM man WHERE manid = '$Manufacturer'"); to delete the manufacturer and $sql = ("DELETE FROM products WHERE manid = '$Manufacturer'");Hope that helps.-Chris Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/#findComment-61290 Share on other sites More sharing options...
witham Posted July 20, 2006 Author Share Posted July 20, 2006 Chris Thanks for that and sorry fro my lack of knowledge but how do I include these statements into the code do they follow each other? Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/#findComment-61308 Share on other sites More sharing options...
cmgmyr Posted July 20, 2006 Share Posted July 20, 2006 no problem, you'll get the hang of it after a while...just keep asking your self "is this as efficient as I can make it?"Just add the delete products under your delete man and it will take care of everything.-Chris Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/#findComment-61314 Share on other sites More sharing options...
HeyRay2 Posted July 20, 2006 Share Posted July 20, 2006 Because both queries rely on the same variable, [B]$Manufacturer[/B], you can run both queries one directly after the other.Also, please contain any pasted code into {code}{/code} (replace { and } with [ and ]) for easier reading for other forum readers. Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/#findComment-61319 Share on other sites More sharing options...
witham Posted July 20, 2006 Author Share Posted July 20, 2006 thanks for the advice I will in future Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/#findComment-61323 Share on other sites More sharing options...
HeyRay2 Posted July 20, 2006 Share Posted July 20, 2006 Anytime... :)This a great place for PHP help, so stop by any time you have a question! ;) Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/#findComment-61326 Share on other sites More sharing options...
witham Posted July 20, 2006 Author Share Posted July 20, 2006 Funny you should mention that the two delete statements are working but the manufacturer is still displaying when the $Select is called ?bear with me I will try to get the code readable:{code}<form action="welddelman.php" method="get"><hr><tr><td colspan = 3><h3 align = "center"><i><b>MANUFACTURER DELETION</b></i></h3></td><tr><br><br><br><br<tr><td colspan = 2><h2 align=center><i>This screen allows the deletion of Manufacturers within W.E.L.D.</h2></td></tr><tr><td><b>Manufacturer:</b></td><?//initiate the $result variable and populate it with the data from the $sqlMANUFACTURERS table$result = mysql_query($sqlMANUFACTURERS, $dblink) or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");// mysql_fetch_array fetches the results from the query a row at a time each time it's called// the result is returned as an array that can be referenced either by field name or by it's index// this data is then put into a drop down box popualted by the updated manufacturers listecho '<td><select name="Manufacturer">';while ($row = mysql_fetch_array ($result)) // loop through the rows outputing them as html table rows { echo '<option value="' . $row["MANID"] . '">' . $row["MANNAME"] . "</option>"; } ?><!-- put in a submit button to send the data to welddelman.php --><tr><td colspan = 2 align = center><button type="submit">DELETE MANUFACTURER FROM EQUIVALENTS DATABASE</button></td></tr><!-- put in links to bring the site together --><tr><td colspan = 2 align = center><a href="querytry1.php">SEARCH FOR A PRODUCT</td></tr><tr><td colspan = 2 align = center><a href="addprod.php">ADD A PRODUCT</td></tr></form><!-- close out the form --></body></html>{/code}and the handling page that displays is:{code}$sqlDELETE = "DELETE from man where manid = '$Manufacturer'";$sqlDELETE = "DELETE FROM prodname WHERE manno = '$Manufacturer'";//$sqlINSERT = strtoupper($sqlINSERT);//put in the sql statement that will display the results$sqlSELECT = "SELECT * from man order by manname;";// mysql_connect connects to the database server and returns a link to the the resource$dblink = @mysql_connect("$dbhost","$dbuser","$dbpass") or die("<p><b>Could not connect to database server: ($dbhost)</b></p>\n");// mysql_select_db selects a database to use on the database server pointers to by $dblink// the @ sign before the command supresses any error messages@mysql_select_db ($dbname , $dblink) or die ("<p><b>Could not connect to database ($dbname)</b></p>\n");// now execute the query to compare the table data to display and if the number of rows returned// is less than zero send the user straight back to Addman.html as the entry is a duplicate//$result = mysql_query($sqlCOMPARE, $dblink)// or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");//if ( mysql_num_rows($result) > 0 ){ // header("Location: Addman.php"); // die ("opps"); //}// now execute the next query to insert the data into the table$result = mysql_query($sqlDELETE, $dblink) or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");// now execute the next query to return the table data to display$result = mysql_query($sqlSELECT, $dblink) or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><head><title>W.E.L.D.</title><!-- set the css style for the page --><style type="text/css">tr {color: white; font-family: arial; font-weight: bold}body {background: navy}</style></head><body><?// output the table and the first row headingsecho '<table border="1" align="center">' . "\n";echo '<tr><td colspan = 3 align = "center"><img alt="addmanufact.JPG" src="addmanufact.JPG" height="155" width="550"></td></tr>';echo "<tr align=center bgcolor = red><td>UPDATED MANUFACTURER LIST</td></tr>\n";// mysql_fetch_array fetches the results from the query a row at a time each time it's called// the result is returned as an array that can be referenced either by field name or by it's indexwhile ($row = mysql_fetch_array ($result)) // loop through the rows outputing them as html table rows { // $row["fieldname"] returns the content for the field in the current row echo "<tr><td align = center>" . $row['MANNAME']. "</td></tr>"; }// close html table tagecho "</table>\n";// the mysql_free_result command removes any resources relating to the query results// this happens automatically at the end of the script but still better to free up nowmysql_free_result ($result);// the mysql_close command severs the link to the database, with scripts that make multiple// queries on the same database the command only needs to be done once after all queries are completed@mysql_close ($dblink) or die( "<p><b>Error while closing connection to database server:" . "($dbhost)</b></p>");?>{/code}I cannot seem to figure way the data is still displaying? By the way this the correct way of displaying pasted code? Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/#findComment-61340 Share on other sites More sharing options...
ryanlwh Posted July 20, 2006 Share Posted July 20, 2006 should be [code\]. HayRey kind of explained it in a little confusing way :) but thanks for encouraging people to use the code tags, HayRey.you have to call query() before assigning another string to $sqlDELETE. Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/#findComment-61347 Share on other sites More sharing options...
redarrow Posted July 20, 2006 Share Posted July 20, 2006 Sorry but rewrite all the code agin dam that bad lol....................................This is a sql problam not php. Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/#findComment-61350 Share on other sites More sharing options...
witham Posted July 20, 2006 Author Share Posted July 20, 2006 finally the "penny has dropped" it is about calling things in order, thank you all very much for your help Quote Link to comment https://forums.phpfreaks.com/topic/15191-deleting-from-multiple-tables/#findComment-61358 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.