Jump to content

deleting from multiple tables


witham

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.