[RESOLVED] weird error undeclared variable sql query

[jrough]

I am getting a undeclared variable error on the SELECT INTO . It thinks inventory_$time is an undeclared variable.
I want to create a table with a timestamp and copy the inventory data into
the backup table. The cREATE bu TABLE with timestamp works.
--error msg------
Invalid query:Undeclared variable: inventory_2010_06_2110_02_56
SELECT * INTO inventory_2010_06_2110_02_56 FROM inventory WHERE cart_id='111111' and bu='00005'
--code---

$time= date('Y_m_dH_i_s');
$link = mysql_connect("myserver","pwd","");
mysql_select_db("mydb",$link);

  $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 `inventory_$time` ";
 $sql.="(`No` text, `nm` text, `id` text, `sku` text, `supplier` text, `ndc` text, `brand` text, `generic` text, `class` text, `qty` text, `unit` text, `control_id` text, `bu` text, `cart_id` text, `error_flag` tinyint, `id` int, `cost` text, `extCost` text) ";
 $result=mysql_query($sql);
 if ( !$result ) die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br />$sql");
  $sql="SELECT * INTO `inventory_$time`  ";  *********
  $sql.="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");

THanks,:bemused::bemused:

[bradgrafelman]

jrough wrote:

It thinks inventory_$time is an undeclared variable.

Well of course it does, because your 'SELECT * INTO' query is telling MySQL to store something into a variable that doesn't exist.

If you're trying to create a new table that is a duplicate of another table, then you should use the 'CREATE TABLE ... SELECT * FROM' syntax instead. Here's the relevant text from the MySQL manual page for CREATE TABLE Syntax:

MySQL Manual wrote:

You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL creates new columns for all elements in the SELECT.

[jrough]

thanks,