[RESOLVED] function to delete and archive a portion of a table

[jrough]

I want to know if my function is right at the asterisks **?
I don't know if the TRUNCATE works for a subset of records or only on the whole table?
I am loading a database with records from excel csv files from a form post that also has 2 input fields 'cart_id' and 'bu'.
If a sheet that has been uploaded gets redone then those records should be copied to another table with the time stamp and then deleted. There should be only unique cart_id's.
thanks,

$time=date("D, d M Y H:i:s O");

function delete_cart($cart_id, $bu)  {

$sql="SELECT count(*) AS cnt FROM pharm_cart_order.cart_inventory ";
$sql.="WHERE cart_id='$cart_id' AND bu='$bu'";
$result=mysql_query($sql);
 if ( !$result ) {die("<font color='red'>Invalid query:</font>" .
mysql_error() . "<br />$sql");}
 $dbarray = mysql_fetch_array($result);
if ( $dbarry['cnt']>0 ){
$sql="CREATE TABLE pharm_cart_order.cart_inventory.$time
SELECT * FROM pharm_cart_order.cart_inventory";
$result=mysql_query($sql);
if ( !$result ) die("<font color='red'>Invalid query:</font>" .
mysql_error() . "<br />$sql");
$sql="TRUNCATE TABLE pharm_cart_order.cart_inventory WHERE 'cart_id' = $cart_id"; **
$result=mysql_query($sql);
if ( !$result ) die("<font color='red'>Invalid query:</font>" .
mysql_error() . "<br />$sql");

  }

}

[dagon]

jrough;10954792 wrote:

I don't know if the TRUNCATE works for a subset of records or only on the whole table?

how about reading the manual

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

[bradgrafelman]

In other words, no, TRUNCATE is all or nothing - you should be using a DELETE query instead.

Also note that single quotes denote strings, thus you shouldn't surround an identifier (e.g. the name of a column) with single quotes.

[jrough]

I do apologize for not reading the manual on TRUNCATE. My main concern was whether I was doing the backup to a new table correctly and the delete was okay.

function delete_cart($cart_id, $bu)  {
//flag records with no matching ndc in cart_inventory matched to onelink
	$time=date("D, d M Y H:i:s O");
	$sql="SELECT count(*) AS cnt FROM pharm_cart_order.cart_inventory ";
	$sql.="WHERE cart_id='$cart_id' AND bu='$bu'";
	$result=mysql_query($sql);
 	if ( !$result ) {die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");}
 	$dbarray = mysql_fetch_array($result);
	if ( $dbarry['cnt']>0 ){
		$sql="CREATE TABLE  pharm_cart_order.cart_inventory.'$time' SELECT * FROM pharm_cart_order.cart_inventory";
		$result=mysql_query($sql);
                var_dump($result). "RESULT <br />";
		if ( !$result ) die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");
		$sql="DELETE FROM pharm_cart_order.cart_inventory WHERE cart_id=$cart_id AND  bu= $bu";
		$result=mysql_query($sql);
		if ( !$result ) die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");
                 echo "The cart_id: $cart_id has been deleted</b>";
  		}
	}

[bradgrafelman]

First problem I see is here:

CREATE TABLE  pharm_cart_order.cart_inventory.'$time'

That's not valid syntax for a table name. The dot syntax is "db_name.table_name", yet you have three groups in there? What are you trying to use for the table name? If you want "table_name.time_value" as the actual name of the table, then you'll have to surround that name with backticks, since the "." has special meaning for SQL identifiers (e.g. it separates two of them).

Also:

jrough wrote:

I have max_execution time set to:
ini_set("max_execution",120000);

What you have there is a call to set the directive "max_execuction" to a given value. Note, however, that there is no such PHP directive as "max_execution", thus that line is superfluous. Perhaps you instead meant to set the directive called "max_execution_time" ? 😉 For that matter, note that you can use the function [man]set_time_limit/man instead.