Parent - Child relationship

[shop73]

I have two tables: categories and products.

categories: categoryID, categoryName
products: prodID, prodName, categoryID

I tried with delete record server behaviors like below

("DELETE FROM categories WHERE categoryID=%s", GetSQLValueString($_GET['catID'], "int"));

and that delete only selected category (parent) without products (child)!

How to delete one category and all products for that category?

 

[ChrisHirst]

two methods

first is two queries that are run concurrently

DELETE FROM products WHERE categoryID = ID

DELETE FROM categories WHERE categoryID = ID

or you can use a join delete query (implied join)

DELETE P,C FROM products AS P, categories AS C WHERE P.categoryID = ID AND C.categoryID = ID

Test the join one on dummy data or a test server before setting it loose on the production server

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[shop73]

I tried but without success!

I make 2 pages: delete_category.php and confirm_delete.php. I pass with url parametar categoryID (confirm_delete.php?catID=<?php echo $row_rsCategories['categoryID']; ?>) from delete_category.php to confirm_delete.php when delete one category.

In confirm_delete.php I have made recordset rsDetails (categoryID,categoryName) and add delete server behaviors with this code:

<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}

if ((isset($_GET['categoryID'])) && ($_GET['categoryID'] != "")) {
  $deleteSQL = sprintf("DELETE FROM categories WHERE categoryID=%s",
                       GetSQLValueString($_GET['categoryID'], "int"));

  mysql_select_db($database_conndatabase, $conndatabase);
  $Result1 = mysql_query($deleteSQL, $conndatabase) or die(mysql_error());

  $deleteGoTo = "admin.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $deleteGoTo .= (strpos($deleteGoTo, '?')) ? "&" : "?";
    $deleteGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $deleteGoTo));
}

$colname_rsDetails = "-1";
if (isset($_GET['catID'])) {
  $colname_rsDetails = (get_magic_quotes_gpc()) ? $_GET['catID'] : addslashes($_GET['catID']);
}
mysql_select_db($database_conndatabase, $connRCTdatabase);
$query_rsDetails = sprintf("SELECT * FROM categories WHERE categoryID = %s", $colname_rsDetails);
$rsDetails = mysql_query($query_rsDetails, $connRCTdatabase) or die(mysql_error());
$row_rsDetails = mysql_fetch_assoc($rsDetails);
$totalRows_rsDetails = mysql_num_rows($rsDetails);
?>

This delete only selected category without products for that category!

What's next?