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
– RiggsFolly, Commented Jul 25, 2018 at 11:26
Why are you coding a try/catch block inside a If error test. Why not just output the error mesage — RiggsFolly
– RiggsFolly, Commented Jul 25, 2018 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
– RiggsFolly, Commented Jul 25, 2018 at 11:35

Jay Blanchard · Accepted Answer · 2018-07-25 11:45:07Z

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, 2018 at 11:45

answered Jul 25, 2018 at 11:28

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

RiggsFolly Over a year ago

Mornin' Jay. I did not know that :)

Jay Blanchard Over a year ago

Mornin' @RiggsFolly. I found this out quite by accident one late and very frustrating night, much as I do all of my programming problems :)

pette Over a year ago

Hi, so I have to use CREATE TEMPORARY TABLE temp_table ENGINE = MEMORY;

Jay Blanchard Over a year ago

Yes, to create a blank table.you could perform a SELECT like CREATE TEMPORARY TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

Barmar Over a year ago

IMHO, it's a bad idea to recommend multi_query(). From what I can tell, it makes things more complicated for little benefit.

|

pette · Accepted Answer · 2018-07-26 07:49:00Z

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 "";
}

Collectives™ on Stack Overflow

Error SQL create temp table with php

2 Answers 2

15 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related