• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

delete from 3 tables with 1 query

I am trying to delete data from three tables but not sure how to achieve this. At the moment I have

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

Open in new window


The other tables are SIZES & COLOURS which hold a size and colour name as well as the product id.
0
jonofat
Asked:
jonofat
  • 14
  • 8
  • 2
  • +1
1 Solution
 
jonofatAuthor Commented:
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"));

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
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.
0
 
hernst42Commented:
You can only delete from one table with one query. So you need to run 3 queries.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
jonofatAuthor Commented:
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"));

Open in new window


Still won't delete the records from the sizes table... :(
0
 
Lukasz ChmielewskiCommented:
Right. This is the protections from sql injection. You have to run them separately.
0
 
jonofatAuthor Commented:
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());

Open in new window

0
 
Lukasz ChmielewskiCommented:
Does the "one" deleting run ok ?
0
 
jonofatAuthor Commented:
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());

Open in new window

0
 
jonofatAuthor Commented:
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));
}
?>

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
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?
0
 
Lukasz ChmielewskiCommented:
What happens when you move the sizes to the first query ?
0
 
jonofatAuthor Commented:
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);
0
 
Lukasz ChmielewskiCommented:
What does this do:

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

  mysql_select_db($database_evs, $evs);
  $Result2 = mysql_query($deletesizes, $evs) or die(mysql_error());
  $Result1 = mysql_query($deleteSQL, $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));
}

Open in new window

0
 
jonofatAuthor Commented:
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());

Open in new window

0
 
jonofatAuthor Commented:
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());

Open in new window

0
 
Lukasz ChmielewskiCommented:
Try with 3
0
 
Lukasz ChmielewskiCommented:
Maybe your data is somehow related within the tables - deleting the data from the first could delete the reference ?
0
 
jonofatAuthor Commented:
Okay, it was working. I changed something in some other code that is completely seperate and it doesn't work now. NOOOOOOOOO!!!
0
 
Lukasz ChmielewskiCommented:
ctrl+z :)
0
 
jonofatAuthor Commented:
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!!!!
0
 
Lukasz ChmielewskiCommented:
Could you post the whole "ok" code with the problematic image deletion ?
0
 
jonofatAuthor Commented:
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();
?>

Open 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;
}

Open in new window

0
 
jonofatAuthor Commented:
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
} 

Open in new window

0
 
jonofatAuthor Commented:
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."; } 

Open in new window

0
 
jonofatAuthor Commented:
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.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 14
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now