CODEWITHSUNDEEP
phpmysqlsql
pette
pette

Reputation: 87

Error SQL create temp table with php

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

Upvotes: 0

Views: 678

Answers (2)

pette
pette

Reputation: 87

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

Upvotes: 0

Jay Blanchard
Jay Blanchard

Reputation: 34426

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.

Upvotes: 1

Related Questions