I'm kinda new to PHP and I'm trying to copy information just passed into one table, and copy it to another, but I keep getting an error when I run the script.

the code goes like this:

// populate the table with the values
		$query = "INSERT INTO zones (store, locale, template, zoneName, content, editDate, editor) VALUES ('".$pageName."', '".$multLocales."', '".$tempName."', 'zone1', '".$zone1."', '".$editTime."', '".$editor."')";
		mysql_query($query)
		or die('Query failed: '.mysql_error());	
		
		// put some of this info into the history table
		$query2 = "SELECT recnum, store, editor, zoneName, template, editDate INTO history FROM zones WHERE editDate='$editTime' AND zoneName='zone1'";
		mysql_query($query2)
		or die('Zone1 Copy Query Failed: '.mysql_error());

The first query runs ok, and populates into the 'zones' table. the problem happens when the script gets to the second query - at that point, I get: "Zone1 Copy Query Failed: Undeclared variable: history"

'history' is the name of the second table, and it's not a variable. Both tables (zones, history) have these fields (although each has some other fields too), so they should be able to match up. Do they have to be in the exact same order? Does that matter?

$query2 = "SELECT recnum, store, editor, zoneName, template, editDate FROM history WHERE editDate='$editTime' AND zoneName='zone1'";

does that work?

// put some of this info into the history table
$query2 = "SELECT recnum, store, editor, zoneName, template, editDate INTO history FROM zones WHERE editDate='$editTime' AND zoneName='zone1'";
mysql_query($query2)
or die('Zone1 Copy Query Failed: '.mysql_error());
[/code]

According to http://dev.mysql.com/doc/refman/5.0/en/select.html, I do not believe you can use "SELECT ... INTO table2" to insert values into a second table. You can insert into a file or a list of variables, but not another table. You will have to use separate commands to SELECT FROM table1 and INSERT INTO table2. You could use mysql_insert_id() to grab the last insert id for your SELECT statement.

I hope that helps.

Stan

According to http://dev.mysql.com/doc/refman/5.0/en/select.html, I do not believe you can use "SELECT ... INTO table2" to insert values into a second table. You can insert into a file or a list of variables, but not another table. You will have to use separate commands to SELECT FROM table1 and INSERT INTO table2. You could use mysql_insert_id() to grab the last insert id for your SELECT statement.

I hope that helps.

Stan

I thought you could copy from one table to another with the SELECT ... INTO statement: http://www.w3schools.com/Sql/sql_select_into.asp

this page shows as an example:

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename

so if that doesn't work, should I be pulling in the query, extracting the variables, and then passing those on to the second table? It just seems there should be a faster way of doing that

I've never used the method you are trying to use so I wouldn't be able to help.

I would just run a query to fetch the data then run another query to insert it into another table.

To be honest with you mate you might save yourself about minute using the method you are tryin, it's not worth it.

I swear the world is turning lazy :P

This article has been dead for over six months. Start a new discussion instead.