Error SQL create temp table with php

Question

I try to select multiple values from a table, playlist_generate, with a condition, create a temp table, update a field and than insert in my table

$data_tabella_duplicata = $_POST['data_duplicata'];
$data_iniziale_originale = $_GET['data_iniziale'];

$query_duplica_playlist = "
DROP TABLE IF EXISTS temp_table;
CREATE TEMPORARY TABLE temp_table LIKE playlist_generate;
SELECT data_playlist, giorno_playlist, orario_playlist, nome_evento,nome_programma FROM playlist_generate WHERE data_playlist = '".$data_iniziale_originale."';
UPDATE temp_table SET data_playlist='".$data_tabella_duplicata."';
INSERT INTO playlist_generate SELECT null,data_playlist, giorno_playlist,orario_playlist, nome_evento, nome_programma FROM temp_table;
DROP TABLE temp_table;
";

$esegui_query_duplica_playlist = $connessione->query($query_duplica_playlist);
if ($connessione->error) {
try {
    throw new Exception("MySQL error $connessione->error <br> Query:<br> $query_duplica_playlist", $connessione->errno);
} catch (Exception $e) {
    echo "Error No: ".$e->getCode()." - ".$e->getMessage()."<br >";
    echo nl2br($e->getTraceAsString());
}
}

but i have an error I don't understand

Error No: 1064 - MySQL error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TEMPORARY TABLE temp_table LIKE playlist_generate; SELECT data_playlist, ' at line 2

I try the code in phpmyadmin and it works

if I use this

$query_duplica_playlist = "
DROP TABLE IF EXISTS temp_table;
CREATE TEMPORARY TABLE temp_table ENGINE = MEMORY;
SELECT data_playlist, giorno_playlist, orario_playlist, nome_evento,nome_programma FROM playlist_generate WHERE data_playlist = '".$data_iniziale_originale."';
UPDATE temp_table SET data_playlist='".$data_tabella_duplicata."';
INSERT INTO playlist_generate SELECT null,data_playlist, giorno_playlist,orario_playlist, nome_evento, nome_programma FROM temp_table;
DROP TABLE temp_table;
";

$esegui_query_duplica_playlist = $connessione->multi_query($query_duplica_playlist);

I have no result

You can only run ONE query using `->query()` you are trying to run 6 — RiggsFolly, Jul 25 '18 at 11:26
Why are you coding a try/catch block inside a `If error` test. Why not just output the error mesage — RiggsFolly, Jul 25 '18 at 11:28
Thats possible but I would check out @JayBlanchard answer. I would personally do it in 6 seperate queries using `->query()` as processing the results of a multi_query can get a little painful IMHO — RiggsFolly, Jul 25 '18 at 11:35
I want to speed up the process with unique query, in my case I want duplicate some values and a different date, in this query I have more than 700 rows — pette, Jul 25 '18 at 11:39

Jay Blanchard · Answer 1 · 2018-07-25T11:45:07.863

1

From the docs -

You cannot use CREATE TEMPORY TABLE ... LIKE to create an empty table based on the definition of a table that resides in the mysql tablespace, InnoDB system tablespace (innodb_system), or a general tablespace. The tablespace definition for such a table includes a TABLESPACE attribute that defines the tablespace where the table resides, and the aforementioned tablespaces do not support temporary tables.

In addition, you appear to be trying to run multiple queries at once. If you're using MySQLi you will want to use multi_query(). Using multi_query(), especially in a situation such as this is not ideal so consider your logic carefully should you find yourself wanting to use this function.

edited Jul 25 '18 at 11:45

answered Jul 25 '18 at 11:28

Jay Blanchard

34,243
16
77
119

1

Mornin' Jay. I did not know that :) – RiggsFolly Jul 25 '18 at 11:30
1

Mornin' @RiggsFolly. I found this out quite by accident one late and very frustrating night, much as I do all of my programming problems :) – Jay Blanchard Jul 25 '18 at 11:33
Hi, so I have to use CREATE TEMPORARY TABLE temp_table ENGINE = MEMORY; – pette Jul 25 '18 at 11:34
Yes, to create a blank table.you could perform a `SELECT` like `CREATE TEMPORARY TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;` – Jay Blanchard Jul 25 '18 at 11:38
1

IMHO, it's a bad idea to recommend `multi_query()`. From what I can tell, it makes things more complicated for little benefit. – Barmar Jul 25 '18 at 11:38
The create temporary table observation is useful, but irrelevant here (the OP wrote the queries worked in phpmyadmin). The multiple query issue has already been answered dozens of time here on SO. – Shadow Jul 25 '18 at 11:40
@JayBlanchard sorry, I don't understand – pette Jul 25 '18 at 11:41
Why irrelevant @Shadow? Has the `LIKE` syntax been covered before? The error being generated for the OP is because of the syntax, not because of the multiple queries. I could not find a duplicate for that syntax issue. – Jay Blanchard Jul 25 '18 at 11:41
I agree @Barmar, only mentioning it in order that the OP might continue to learn. – Jay Blanchard Jul 25 '18 at 11:42
What do you not understand @user1712790 – Jay Blanchard Jul 25 '18 at 11:42
So the best way is make more single query? – pette Jul 25 '18 at 12:00
Maybe if I create a real table and I use this table as a “temp table” and I separate the queries is a Bad solution? – pette Jul 25 '18 at 12:07
You should separate the queries, no matter what. then test your temp table creation. If it works separately you'll be good to go. If it doesn't use a CREAT TABLE query as I have shown in comments. – Jay Blanchard Jul 25 '18 at 12:39
If the error was related to the issue you describe, then 1) it would not be a syntax error 2) the code would not have run in phpmyadmin. If you use the query() method in mysqli to execute multiple sql statements, then you get a syntax error for the 2nd statement - just like here. So yes, the error in the question is caused by the multiple sql statements and you were wrong to remove the dupe hammer. – Shadow Jul 25 '18 at 12:48
See for example: https://stackoverflow.com/questions/40844497/why-error-occurs-when-i-send-multiple-queries-into-mysqli-query – Shadow Jul 25 '18 at 12:54

score 0 · Answer 2 · answered Jul 26 '18 at 07:49

Thanks to all. So the best way to do this is make more queries. Thanks

// 1 query
$query_seleziona_data_controllo        = "SELECT data_generata FROM data_generata WHERE data_generata ='".$data_tabella_duplicata."' ";
$esegui_query_seleziona_data_controllo = $connessione->query($query_seleziona_data_controllo);

$numero_righe_query_controllo = $esegui_query_seleziona_data_controllo->num_rows;
if ($numero_righe_query_controllo == 0) {

// 2 query
$query_duplica_playlist = "INSERT INTO playlist_generate_temp
SELECT null,data_playlist, giorno_playlist, orario_playlist, nome_evento,nome_programma FROM playlist_generate
WHERE data_playlist = '".$data_iniziale_originale."' ";

$esegui_query_duplica_playlist = $connessione->query($query_duplica_playlist);

// 3 query
$query_update_data_nuova = "UPDATE playlist_generate_temp SET data_playlist='".$data_tabella_duplicata."' WHERE data_playlist = '".$data_iniziale_originale."' ";

$esegui_query_update_data_nuova = $connessione->query($query_update_data_nuova);

// 4 query
$query_inserisci_duplicato = "INSERT INTO playlist_generate
SELECT null,data_playlist, giorno_playlist, orario_playlist, nome_evento,nome_programma FROM playlist_generate_temp
WHERE data_playlist = '".$data_tabella_duplicata."' ";

$esegui_query_inserisci_duplicato = $connessione->query($query_inserisci_duplicato);

// 5 query
$query_truncate_temp = "TRUNCATE TABLE playlist_generate_temp";

$esegui_query_truncate_temp = $connessione->query($query_truncate_temp);

mysqli_close($connessione);

} else {

echo "";
}

Error SQL create temp table with php

2 Answers2