[SOLVED] Temporary tables on php

[wsantos]

Im trying to implement temporary table on one of my applications.  I run this queries in succession directly from mysql.

 

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

 

The above gave me the result that I expected.  However, via php

 

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

mysql_query($qry1,$con)
mysql_query($qry2,$con)
$result = mysql_query($qry3,$con)

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $daRec[] = $row;
}
mysql_free_result($result);
print_r($daRec);

 

That returned an empty array.

[rarebit]

Is 'lookup_callid' table setup correctly? Can you display results from that using:

$qry3 = "SELECT callid FROM lookup_callid";

[wsantos]

I couldn't post the actual result (classified data).  But I'll assure you that the result is what I expected and is properly set up on the database side.  Unfortunately, it's a different case on the php script.

[cooldude832]

well first off php doesn't have temporary table (mysql does) secondly what are you deleting and recreating a table when a simple truncating of data completes it much easier. 

[wsantos]

well first off php doesn't have temporary table (mysql does)

 

That answers the question. Any way around this? Perhaps treating an array as a table and doing a query on that?

As for your second point, This is just the first part of 7 joins from a very big db with many processes running in it.  Usually with intervals of 2 minutes. Using joins in this case is not our option.

 

Thanks for the help though.

[rarebit]

If your on the same server and have high enough privileges then use something like 'exec' to run a bash script your original way??

[wsantos]

Thats the problem. I'm not.