[RESOLVED] error on SELEC.. on sql statement

jrough

I am getting this sql error in my delete_function:

Invalid query:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO cart_order_archive.cart_inventory_.2010_06_19-10-52-29 SELEC' at line 1
CREATE TABLE cart_order_archive.cart_inventory_.2010_06_19-10-52-29 INSERT INTO cart_order_archive.cart_inventory_.2010_06_19-10-52-29 SELECT * FROM cart_order.cart_inventory

I am trying to make a copy of the table before I do the delete.
It stops on the line creating the archive table, more specifically the SELEC...
I added the database name on INSERT INTO instead of using mysql_select_db()
because that sql uses both the cart_order and the cart_order_archive databases. I didn't know which one to use in the mysql_select_db for that query.
Does the SELECT FROM cart_order.cart_inventory need to be on a separate
$sql.= SELECT from ... line?

thanks,

function delete_cart($cart_id, $bu)  {
$time= date('Y_m_d-H-i-s');


$sql="SELECT count(*) AS cnt FROM cart_inventory ";
$sql.="WHERE cart_id='$cart_id' AND bu='$bu' ";
mysql_select_db("cart_order");
$result=mysql_query($sql);
    echo "<!--\n$sql\n-->";
if ( !$result ) {die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");}
$dbarray = mysql_fetch_array($result);
if ( $dbarray['cnt']>0 ){

    $sql="CREATE TABLE  cart_order_archive.`cart_inventory_.$time` ";
    $sql.="INSERT INTO cart_order_archive.`cart_inventory_.$time` SELECT * FROM cart_order.cart_inventory ";

    $result=mysql_query($sql);
            echo "<!--\n$sql\n-->";
    if ( !$result ) die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");
    $sql="DELETE  FROM cart_inventory  WHERE cart_id ='$cart_id' AND bu= '$bu' ";
    mysql_select_db("cart_order");
    $mysql_query($sql);
    $result=mysql_query($sql);
    echo  '<pre>'  ;
    print_r($result);
    echo '</pre>' ;
    if ( !$result ) die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");
            echo "The cart_id: $cart_id and bu:$bu has been deleted</b>";
      }
}

NogDog

You cannot have a CREATE and INSERT in a single query (or if you can, I'm extremely surprised).

Also, it seems non-normal to need to create a new table each time, anyway.

jrough

okay, I decided to take out the CREATE database. ACtually that was wrong because I had a CREATE TABLE instead of CREATE DB but I meant it to be create db. I don't need to create a new database named archive. All I need is a backup of the portion of the table I am deleting in another table. It doens't have to be in another database. This doesn't happen very often only if the bu and cart_id need to be redone. I just need the copy to the archive table and it would be nice if it was only the portion WHERE bu=$bu and cart_id=$cart_id in the WHERE but I could also copy the whole table into the archive table.
Now I am getting INVALID query without the WHERE??

I just want the archive table to have a timestamp . The INSERT INTO should copy the data and create the table over right?

I was under the impression you could do a INSERT INTO db.table1 _timestamp SELECT FROM db.table
to make a backup and it would create the new table1_timestamp
Here is what I changed I just took out the first sql query. It should work but it doesn't:
The error just says INVALID QUERY in red.
tia,

if ( !$result ) die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");
    $sql="SELECT count(*) AS cnt FROM inventory ";
    $sql.="WHERE cart_id='$cart_id' AND bu='$bu' ";
    $result=mysql_query($sql);
    	echo "<!--\n$sql\n-->";
    if ( !$result ) {die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");}
    $dbarray = mysql_fetch_array($result);
    if ( $dbarray['cnt']>0 ){

    //$sql="CREATE TABLE  pharm_cart_order_archive.`cart_inventory_.$time` ";
    //$result=mysql_query($sql);
    //if ( !$result ) die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");

    $sql="INSERT INTO `inventory_.$time` SELECT * FROM inventory ";
   // $sql.="WHERE cart_id='$cart_id' and bu='$bu' ;

    $result=mysql_query($sql);
            echo "<!--\n$sql\n-->";
    if ( !$result ) die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");   
    $sql="DELETE  FROM inventory  WHERE cart_id ='$cart_id' AND bu= '$bu' ";
    $mysql_query($sql);
    $result=mysql_query($sql);
    echo  '<pre>'  ;
    print_r($result);
    echo '</pre>' ;
    if ( !$result ) die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");
             echo "The cart_id: $cart_id and bu:$bu has been deleted</b>";
      }

jrough

sorry I found some issues with the sQL. One is I need to create the table first before copying into the table.