I like to use heredoc to help me build an inline sql query (just to help make any subtle mistake); so your first request will 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 second query without specifying the fields of the table into which you want to insert records, you must specify the fields in the same order .
This time just a request:
INSERT INTO temp SELECT id, live, ship, description, duration FROM cruises WHERE live = 'y';
And the last thing about the temporary variable is: Check the part about its visibility. You can use the TEMPORARY keyword when creating a table. The TEMPORARY table is visible only for the current connection and is automatically discarded when the connection is closed. http://dev.mysql.com/doc/refman/5.5/en/create-table.html
What does this mean: when you connect directly to MySQL, for example, through the command line interface, for example:
mysql> #our query here line-by-line
Then you essentially use the same connection through all of your multiple requests while your session is active.
But in an external script (for example, PHP), just because it in the same script file does not necessarily mean the same connection, so by the time your request for insertion is executed your temp table does not appear in the connection session.
Try to fulfill all the requests, send all this as part of the execution of a single command / request.
Good luck.