make a temporary table and select from it

Question

I'm getting an error 'undeclared variable: temp' when I run this...

<?php 

$maketemp = "CREATE TEMPORARY TABLE temp(`itineraryId` int NOT NULL, `live` varchar(1), `shipCode` varchar(10), `description` text, `duration` varchar(10), PRIMARY KEY(itineraryId))"; 

mysql_query( $maketemp, $connection ) or die ( "Sql error : " . mysql_error ( ) );

$inserttemp = "SELECT live, id AS itineraryId, ship AS shipCode, description AS description, duration AS length FROM cruises WHERE live ='Y' INTO temp";

mysql_query( $inserttemp, $connection ) or die ( "Sql error : " . mysql_error ( ) );

$select = "SELECT intineraryId, shipCode, description, duration FROM temp";

$export = mysql_query ( $select, $connection ) or die ( "Sql error : " . mysql_error( ) );

Any ideas ?

I think you may need to do your select into in the same query as the create. — Tom Hallam, Apr 27 '12 at 09:33
Where did `AS length` come from? You didn't create a column `length` in the temp table... (not that this is likely to fix the error) — DaveRandom, Apr 27 '12 at 09:35
it doesn't say which line, just Sql error : Undeclared variable: temp — Rich, Apr 27 '12 at 09:35
yes I need to put the length field in too, well spotted @DaveRandom — Rich, Apr 27 '12 at 09:36
Try `INSERT INTO ... SELECT` instead of `SELECT ... INTO` (idea lifted from [here](http://forge.mysql.com/tools/tool.php?id=1)) — DaveRandom, Apr 27 '12 at 09:36
Ok that made a difference @DaveRandom, now I get Sql error : Duplicate entry '0' for key 1 — Rich, Apr 27 '12 at 09:38
Sounds like `cruises.id` has more than one row with `0` in it... — DaveRandom, Apr 27 '12 at 09:39
field cruises.id should all be unique. Maybe the temp table has a row in it with id of 0 before my insert is running ? — Rich, Apr 27 '12 at 09:45
@Rich the fields `live` and `id AS itineraryId` are in the wrong order in the `SELECT`, I'm guessing `live` is a effectively bool and as such has more than one `0` value — DaveRandom, Apr 27 '12 at 09:49
yes that seems to have fixed that, now I get Sql error : Unknown column 'intineraryId' in 'field list' — Rich, Apr 27 '12 at 09:57
@Rich try my code below, and check field spelling in yours ;-) — DaveRandom, Apr 27 '12 at 10:00

score 14 · Accepted Answer · edited May 25 '19 at 11:58

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

This code should work:

<?php

  $maketemp = "
    CREATE TEMPORARY TABLE temp_table_1 (
      `itineraryId` int NOT NULL,
      `live` varchar(1),
      `shipCode` varchar(10),
      `description` text,
      `duration` varchar(10),
      PRIMARY KEY(itineraryId)
    )
  "; 

  mysql_query($maketemp, $connection) or die ("Sql error : ".mysql_error());

  $inserttemp = "
    INSERT INTO temp_table_1
      (`itineraryId`, `live`, `shipCode`, `description`, `duration`)
    SELECT `id`, `live`, `ship`, `description`, `duration`
    FROM `cruises`
    WHERE `live` = 'Y'
  ";

  mysql_query($inserttemp, $connection) or die ("Sql error : ".mysql_error());

  $select = "
    SELECT `itineraryId`, `shipCode`, `description`, `duration`
    FROM temp_table_1
  ";
  $export = mysql_query($select, $connection) or die ("Sql error : ".mysql_error());

I guess you are going to do more stuff with the temporary table, or are just playing with it, but if not be aware that the whole code could be summed up with:

<?php

  $query = "
    SELECT `id` AS 'itineraryId', `ship`, `description`, `duration`
    FROM `cruises`
    WHERE `live` = 'Y'
  ";
  $export = mysql_query($query, $connection) or die ("Sql error : ".mysql_error());

ok I tried that, and it produces the same Sql error : Unknown column 'intineraryId' in 'field list' — Rich, Apr 27 '12 at 10:02
I have just made a very small change to above code, try again (there is a spelling mistake in your last `SELECT` - look carefully for the extra `n`) — DaveRandom, Apr 27 '12 at 10:03
got ya... extra n! perfect @DaveRandom, you are the man! Kudos :) — Rich, Apr 27 '12 at 10:06

score 2 · Answer 2 · answered Apr 27 '12 at 10:10

I like to use heredoc to help me construct embedded sql query (just to help make any subtle error glaring); so your first query would look something like this:

$maketemp =<<<s
CREATE TEMPORARY TABLE temp(
`itineraryId` int NOT NULL,
`live` varchar(1), 
`shipCode` varchar(10),
`description` text,
`duration` varchar(10),
PRIMARY KEY(itineraryId));
s;

Then if you want to correct the 2nd query without listing the fields of the table you want to insert the records, you have to list the fields in the same order.

Just the query this time:

INSERT INTO temp
SELECT id, live, ship, description, duration
FROM cruises
WHERE live = 'y';

And last thing about temporary variable is this: Check out the part about its visibility. You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. http://dev.mysql.com/doc/refman/5.5/en/create-table.html

What that means is this: when you're connected to MySQL directly, e.g. through a command line interface like this:

mysql> #our query here line-by-line

Then you're essentially on the same connection through all of your multiple queries as long as your session is active.

But in an external script (like PHP, for example), just because it's on the same script file doesn't necessarily mean it's the same connection so that by the time you execute your insert query, your temp table is not visible to that connection session.

Try to concat all the queries, send it all within a single command/query execution.

Good luck.

score 1 · Answer 3 · answered Apr 27 '12 at 09:37

The second query is not correct.

From the reference -

MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension. Instead, MySQL Server supports the INSERT INTO ... SELECT. standard SQL syntax, which is basically the same thing.

Try this one instead -

INSERT INTO temp
SELECT live
     , id AS itineraryId
     , ship AS shipCode
     , description AS description
     , duration AS length
FROM
  cruises
WHERE
  live = 'Y'

make a temporary table and select from it

3 Answers3

Linked