[SOLVED] Implementing Drop/Create Temporary Table

[wsantos]

Im trying to implement a series of drop/create temporary tables on php.  Here is the steps via mysql:

 

mysql>DROP TEMPORARY TABLE IF EXISTS tmp_tbl1;
mysql>CREATE TEMPORARY TABLE tmp_tbl1 SELECT uid,src FROM tblSource WHERE start BETWEEN '2007-11-16 00:00:00' AND '2007-11-16 23:59:59';
mysql>SELECT * FROM tblMain JOIN tmp_tbl1 ON tmp_tbl1.uid=tblMain.uid;

 

This is my php script:

 

$qryDrop = "DROP TEMPORARY TABLE IF EXISTS tmp_tbl1";
$qryCreate = "CREATE TEMPORARY TABLE tmp_tbl1 SELECT uid,src FROM tblSource WHERE start BETWEEN '2007-11-16 00:00:00' AND '2007-11-16 23:59:59'";
$qryMain = "SELECT * FROM tblMain JOIN tmp_tbl1 ON tmp_tbl1.uid=tblMain.uid";

mysql_query($qryDrop,$con);
mysql_query($qryCreate,$con);
$res=mysql_query($qryCreate,$con);
; --- snip

 

Thank you for pointing me in the right direction.

[teng84]

$qry = "DROP TEMPORARY TABLE IF EXISTS tmp_tbl1; ";
$qry. = "CREATE TEMPORARY TABLE tmp_tbl1 SELECT uid,src FROM tblSource WHERE start BETWEEN '2007-11-16 00:00:00' AND '2007-11-16 23:59:59'; ";
$qry. = "SELECT * FROM tblMain JOIN tmp_tbl1 ON tmp_tbl1.uid=tblMain.uid; ";

mysql_query($qry,$con);

 

you can do it that way....

not tested but that should work

[wsantos]

gave me this error:

 

Invalid query: 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 tmp_tbl1 SELECT uid,src FROM cdr WHERE start BETWEEN '2' at line 1

 

[cmw]

$qry = "DROP TEMPORARY TABLE IF EXISTS tmp_tbl1; ";
$qry. = "CREATE TEMPORARY TABLE tmp_tbl1 SELECT uid,src FROM tblSource WHERE start BETWEEN '2007-11-16 00:00:00' AND '2007-11-16 23:59:59'; ";
$qry. = "SELECT * FROM tblMain JOIN tmp_tbl1 ON tmp_tbl1.uid=tblMain.uid; ";

mysql_query($qry,$con);

 

you can do it that way....

not tested but that should work

That won't work with mysql_query() as there are multiple queries delimited by a semi-colon ; That was the cause of the error wsantos found.

 

Try mysqli_multi_query to run such a query instead.

 

wsantos, I haven't have a problem using mysqli and consecutive queries:

<?php
$dbc = new mysqli($host, $user, $pass, $name);

$dbc->query("DROP TEMPORARY TABLE IF EXISTS tmp1");
$dbc->query("CREATE TEMPORARY TABLE tmp1(id int, name char(5))");
$dbc->query("INSERT INTO tmp1 (name) VALUES ('test')");

$result = $dbc->query("SELECT * FROM tmp1");

$dbc->query("DROP TEMPORARY TABLE IF EXISTS tmp1");

if ($result AND ($result->num_rows > 0)) {
    while (($row = $result->fetch_object())) {
        print serialize($row) ."<br>\n";
    }
}
?>

produces the following output:

O:8:"stdClass":2:{s:2:"id";N;s:4:"name";s:4:"test";}

 

Hope that may help.