Using multiple temporary tables in a single MySQL query (works in phpMyAdmin, but not PHP)

Question

I would like to avoid using real tables as a work-around. I am trying to join two tables and get unique values for the column I'm calling unique_values_needed, while also prioritizing the value 11 when it is present in duplicate real_secondary_table_primary_key.

Here is the code (it works in phpMyAdmin, but not in PHP):

CREATE TEMPORARY TABLE IF NOT EXISTS temporary_table_1
    AS (SELECT real_main_table.*, 
               real_secondary_table.real_secondary_table_primary_key 
          FROM real_main_table
               LEFT JOIN real_secondary_table 
                         ON real_secondary_table.real_main_table_primary_key = real_main_table.real_main_table_primary_key
      ORDER BY real_main_table.date DESC);

CREATE TEMPORARY TABLE IF NOT EXISTS temporary_table_2
    AS (SELECT real_main_table.*, 
               real_secondary_table.real_secondary_table_primary_key 
          FROM real_main_table
               LEFT JOIN real_secondary_table 
                         ON real_secondary_table.real_main_table_primary_key = real_main_table.real_main_table_primary_key
      ORDER BY real_main_table.date DESC);

UPDATE temporary_table_1 t1, temporary_table_2 t2
   SET t1.real_secondary_table_primary_key = t2.real_secondary_table_primary_key
 WHERE     t1.real_main_table_primary_key = t2.real_main_table_primary_key 
       AND t1.real_secondary_table_primary_key  11 
       AND t2.real_secondary_table_primary_key = 11;

  SELECT * 
    FROM temporary_table_1 
GROUP BY unique_values_needed
ORDER BY temporary_table_1.date DESC;

I have also tried the following (again, it works in phpMyAdmin, but still not in PHP):

CREATE TEMPORARY TABLE IF NOT EXISTS temporary_table_1
    AS (SELECT real_main_table.*, 
               real_secondary_table.real_secondary_table_primary_key 
          FROM real_main_table
               LEFT JOIN real_secondary_table 
                         ON real_secondary_table.real_main_table_primary_key = real_main_table.real_main_table_primary_key
      ORDER BY real_main_table.date DESC);

UPDATE temporary_table_1 t1, (
       SELECT real_main_table.*, 
              real_secondary_table.real_secondary_table_primary_key 
         FROM real_main_table
              LEFT JOIN real_secondary_table 
                        ON real_secondary_table.real_main_table_primary_key = real_main_table.real_main_table_primary_key
     ORDER BY real_main_table.date DESC) t2
   SET t1.real_secondary_table_primary_key = t2.real_secondary_table_primary_key
 WHERE     t1.real_main_table_primary_key = t2.real_main_table_primary_key 
       AND t1.real_secondary_table_primary_key  11 
       AND t2.real_secondary_table_primary_key = 11;

  SELECT * 
    FROM temporary_table_1 
GROUP BY unique_values_needed
ORDER BY temporary_table_1.date DESC;

When I checked using mysql_query($query) or die(mysql_error()), the error is supposedly either when creating the second temporary table (in the first code example), or in the UPDATE when using (SELECT ...) t2 (in the second code example).

Provide `SHOW CREATE TABLE real_main_table;`, `SHOW CREATE TABLE real_secondary_table;` and desired output. Also [read this](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Alexander, Feb 20 '14 at 12:28
I'm confused--doesn't that just output the SQL needed to make the tables? Do you want me to post the tables here? EDIT: Nevermind, I see your question's been clarified. — jperezov, Feb 20 '14 at 12:30
Are you sure you use the same connection (session) to the db from php side ? also post the error you receive — Stephan, Feb 20 '14 at 12:30
`mysql_query` is supposed to execute only one statement. You should be using [mysqli_multi_query](http://www.php.net/manual/en/mysqli.multi-query.php) , sorry I don't know a mysql_* equivalent. — bansi, Feb 20 '14 at 12:31
Deprecated: The mysql extension is deprecated and will be removed in the future: use mysqli or PDO — Muhammad, Feb 20 '14 at 12:40
It won't let me edit the question any more--someone else edited it and now when I try it tells me that I have code that is not properly formatted. The error from `mysql_error()` is simply a syntax error in the locations I specified. — jperezov, Feb 20 '14 at 12:44
Also, is there any way of doing this while continuing to use `mysql_query`? I can't go through all the code and change everything to `mysqli_*`. Alternatively, is using both a problem? (i.e. not removing the `mysql_connect`, while also adding `mysqli_connect`?) — jperezov, Feb 20 '14 at 12:46
@user3191820 check my comment. you cannot execute multiple queries with `mysql_query()` so you are getting error on the second query part. on both cases. using both in the same code is not a problem but you cannot mix them like open with `mysql_open()` and query with `mysqli_query()` and vice versa. — bansi, Feb 20 '14 at 12:47
@bansi Yes, I saw and threw you another question. It seems that `mysql_*` does not have a `mysqli_multi_query` equivalent though. I'll accept this as the answer if you post it. — jperezov, Feb 20 '14 at 12:52

score 0 · Accepted Answer · answered Feb 20 '14 at 12:59

You cannot execute multiple queries with mysql_query() You should use mysqli_multi_query to do this. There is no equivalent of mysqli_multi_query in mysql_* functions.

You can use both mysql_* and mysqli_* in the same code but be careful not to mix them up like open with mysql_open() and query with mysqli_query() and vice versa. Once the connection is opened with mysqli_open() you should use only mysqli_* functions on that connection. This applies for mysql_* connection also. But you can have both connections open at the same time from the same code.

Warning: mysql_ function is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used.

Using multiple temporary tables in a single MySQL query (works in phpMyAdmin, but not PHP)

1 Answers1