delete from 3 tables with 1 query

I tried this for 2 tables but no luck...

$deleteSQL = sprintf("DELETE FROM products WHERE ProductID=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));
	$deleteSQL = sprintf("DELETE FROM sizes WHERE productid=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));

Select allOpen in new window

Did you get "GetSQLValueString()" from Adobe because it's not part of PHP?  http://kb2.adobe.com/cps/300/30037473.html

In addition, I would assign that value to variable before creating $deleteSQL and skip the need for "sprintf()".

And, in the "mysql_query()", you can only do one query at a time.  To do three, you have to have three separate "mysql_query()" statments.

You can only delete from one table with one query. So you need to run 3 queries.

Tried this to...

if ((isset($_GET['ProductID'])) && ($_GET['ProductID'] != "")) {
  $deleteSQL = sprintf("DELETE products, sizes FROM products, sizes WHERE products.ProductID=%s AND sizes.productid=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));

Select allOpen in new window

Still won't delete the records from the sizes table... :(

Right. This is the protections from sql injection. You have to run them separately.

I tried running the two mysql_queries but it still doesn't delete?

if ((isset($_GET['ProductID'])) && ($_GET['ProductID'] != "")) {
  $deleteSQL = sprintf("DELETE FROM products IWHERE ProductID=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));
					   
					   mysql_select_db($database_evs, $evs);
  $Result1 = mysql_query($deleteSQL, $evs) or die(mysql_error());
					   
					   
					   $deleteSQL = sprintf("DELETE FROM sizes WHERE productid=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));

  mysql_select_db($database_evs, $evs);
  $Result1 = mysql_query($deleteSQL, $evs) or die(mysql_error());

Select allOpen in new window

Does the "one" deleting run ok ?

Yes, the first one works, but won't delete the sizes. Tried this too..

if ((isset($_GET['ProductID'])) && ($_GET['ProductID'] != "")) {
  $deleteSQL = sprintf("DELETE FROM products WHERE ProductID=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));
  $deletesizes = sprintf("DELETE FROM sizes WHERE productid=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));

  mysql_select_db($database_evs, $evs);
  $Result1 = mysql_query($deleteSQL, $evs) or die(mysql_error());
  $Result2 = mysql_query($deletesizes, $evs) or die(mysql_error());

Select allOpen in new window

Sorry, this is the full code..

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($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['ProductID'])) && ($_GET['ProductID'] != "")) {
  $deleteSQL = sprintf("DELETE FROM products WHERE ProductID=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));
  $deletesizes = sprintf("DELETE FROM sizes WHERE productid=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));

  mysql_select_db($database_evs, $evs);
  $Result1 = mysql_query($deleteSQL, $evs) or die(mysql_error());
  $Result2 = mysql_query($deletesizes, $evs) or die(mysql_error());
  

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

Select allOpen in new window

If the tables are all in the same database and you have logged into that database, there is no need to 'reselect' the database.  Have you done the 'mysql_connect()' before this code?

What happens when you move the sizes to the first query ?

Yes, at the very top of the code is reference to an include file with database details and there is a line that has

mysql_pconnect($hostname_test, $username_test, $password_test) or trigger_error(mysql_error(),E_USER_ERROR);

I tried this and it worked!

if ((isset($_GET['ProductID'])) && ($_GET['ProductID'] != "")) {
  $deleteSQL = sprintf("DELETE FROM sizes WHERE productid=%s",
                       GetSQLValueString($_GET['ProductID'], "text"));
  $deleteprod = sprintf("DELETE FROM products WHERE ProductID=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));

  mysql_select_db($database_evs, $evs);
  $Result1 = mysql_query($deleteSQL, $evs) or die(mysql_error());
  $Result2 = mysql_query($deleteprod, $evs) or die(mysql_error());

Select allOpen in new window

And this worked for all three tables!

if ((isset($_GET['ProductID'])) && ($_GET['ProductID'] != "")) {
  $deleteSQL = sprintf("DELETE FROM sizes WHERE productid=%s",
                       GetSQLValueString($_GET['ProductID'], "text"));
  $deleteprod = sprintf("DELETE FROM products WHERE ProductID=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));
  $deletecolour = sprintf("DELETE FROM colours WHERE productid=%s",
                       GetSQLValueString($_GET['ProductID'], "int"));

  mysql_select_db($database_evs, $evs);
  $Result1 = mysql_query($deleteSQL, $evs) or die(mysql_error());
  $Result2 = mysql_query($deleteprod, $evs) or die(mysql_error());
  $Result3 = mysql_query($deletecolour, $evs) or die(mysql_error());

Select allOpen in new window

Try with 3

Maybe your data is somehow related within the tables - deleting the data from the first could delete the reference ?

Okay, it was working. I changed something in some other code that is completely seperate and it doesn't work now. NOOOOOOOOO!!!

ctrl+z :)

haha. Yes, but now I have a problem. At first it would delete from one table and delete the related image off the server. Then I got it to delete data from three tables but not the image on the server. I fiddled and then it deleted the images off the server but not from the 3 tables. I changed it back and now it deletes from the 3 tables but not the image on the server!!!!

Could you post the whole "ok" code with the problematic image deletion ?

Well, I am trying to change the method as the one that was working initially uses a lot of include files and stuff. I am trying to make my code shorter...

Original code...

<?php
// Load the common classes
require_once('../includes/common/KT_common.php');

// Load the tNG classes
require_once('../includes/tng/tNG.inc.php');

// Make a transaction dispatcher instance
$tNGs = new tNG_dispatcher("../");

// Make unified connection variable
$conn_som = new KT_connection($test, $database_test);

//start Trigger_FileDelete trigger
//remove this line if you want to edit the code by hand 
function Trigger_FileDelete(&$tNG) {
  $deleteObj = new tNG_FileDelete($tNG);
  $deleteObj->setFolder("../images/");
  $deleteObj->setDbFieldName("ProductImage");
  return $deleteObj->Execute();
}
//end Trigger_FileDelete trigger

// Make an instance of the transaction object
$del_gallery = new tNG_delete($conn_som);
$tNGs->addTransaction($del_gallery);
// Register triggers
$del_gallery->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "GET", "ProductID");
$del_gallery->registerTrigger("END", "Trigger_Default_Redirect", 99, "cpanel.php");
$del_gallery->registerTrigger("AFTER", "Trigger_FileDelete", 98);
// Add columns
$del_gallery->setTable("products");
$del_gallery->setPrimaryKey("ProductID", "NUMERIC_TYPE", "GET", "ProductID");

// Execute all the registered transactions
$tNGs->executeTransactions();
?>
<?php
	echo $tNGs->getErrorMsg();
?>

Select allOpen in new window

However, I would rather not use that and try use something like this which I can't get to work..

$target = $row_Recordset1['ProductID'];
// See if it exists before attempting deletion on it
if (file_exists($target)) {
    unlink($target); // Delete now
} 
// See if it exists again to be sure it was removed
if (file_exists($target)) {
    echo "Problem deleting " . $target;
} else {
    echo "Successfully deleted " . $target;
}

Select allOpen in new window

I am guessing I need to have the path to the file in there somewhere..

$path = '..images/';
$target = $row_Recordset1['ProductImage'];
// See if it exists before attempting deletion on it
if (file_exists($target)) {
    unlink($target); // Delete now
} 

Select allOpen in new window

Got it...

chdir('../images/'); 
    $do = unlink($row_Recordset1['ProductImage']); 
    if($do=="1"){ 
        echo "The file was deleted successfully."; 
    } else { echo "There was an error trying to delete the file."; } 

Select allOpen in new window

okay, last question though. That works for one file, but I have a thumbnail that must also be deleted. I can't seem to use chdir on two seperate lines eg:

chdir('../images/');
chdir('../images/thumb_');

after thumb_ is the name of th actual file.