Jump to content

Archived

This topic is now archived and is closed to further replies.

witham

deleting from multiple tables

Recommended Posts

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 list

echo '<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 headings
echo '<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 index

while ($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 tag
echo "</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 now
mysql_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 help

Thanks

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Anytime... :)

This a great place for PHP help, so stop by any time you have a question!

;)

Share this post


Link to post
Share on other sites
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 list

echo '<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 headings
echo '<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 index

while ($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 tag
echo "</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 now
mysql_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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Sorry but rewrite all the code agin dam that bad lol....................................

This is a sql problam not php.

Share this post


Link to post
Share on other sites
finally the "penny has dropped" it is about calling things in order, thank you all very much for your help

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.