February 11th, 2005, 12:33 PM
|
|
Registered User
|
|
Join Date: Apr 2004
Posts: 11
Time spent in forums: 2 h 6 m 33 sec
Reputation Power: 0
|
|
|
running 'CREATE TEMPORARY TABLE' under php
Need your help. Have the following mysql query works fine under Mysql Control Center. I haven't had much exposure to Temporary tables so that could be part of my problem. Someone suggested to me that I should consider using temporary table for complex queries. Since the query below works fine, I want to convert it so that it runs under php. At the buttom the code for the failed attempt. Thanks a lot.
PHP Code:
DROP TABLE TempTable0;
DROP TABLE TempTable1;
DROP TABLE TempTable2;
DROP TABLE TempTable3;
DROP TABLE TempTable4;
CREATE TEMPORARY TABLE TempTable0 SELECT uniqueID, firstname, lastname from user;
CREATE TEMPORARY TABLE TempTable1 SELECT a.uniqueID, count(b.uniqueID) AS total1 FROM TempTable0 a LEFT JOIN tbl1 b on a.uniqueID = b.uniqueID GROUP BY a.uniqueID;
CREATE TEMPORARY TABLE TempTable2 SELECT a.uniqueID, count(b.uniqueID) AS total2 FROM TempTable0 a LEFT JOIN tbl2 b on a.uniqueID = b.uniqueID GROUP BY a.uniqueID;
CREATE TEMPORARY TABLE TempTable3 SELECT a.uniqueID, count(b.uniqueID) AS total3 FROM TempTable0 a LEFT JOIN tbl3 b on a.uniqueID = b.uniqueID GROUP BY a.uniqueID;
CREATE TEMPORARY TABLE TempTable4 SELECT a.uniqueID, count(b.uniqueID) AS total4 FROM TempTable0 a LEFT JOIN tbl4 b on a.uniqueID = b.uniqueID GROUP BY a.uniqueID;
SELECT a.firstname, a.lastname, a.uniqueID, b.total1, c.total2, d.total3, e.total4 FROM TempTable0 a
LEFT JOIN TempTable1 b ON a.uniqueID = b.uniqueID
LEFT JOIN TempTable2 c ON a.uniqueID = c.uniqueID
LEFT JOIN TempTable3 d ON a.uniqueID = d.uniqueID
LEFT JOIN TempTable4 e ON a.uniqueID = e.uniqueID where a.uniqueID='jeff';
The above code is similar to:
PHP Code:
select field1,field2 from table1 left join table2 on table2.id=table2.id left join table3 left join table1.id ON table3.id=table1.id where id='jeff' group by id.
The idea behind is to get all the data for all users even if there are no records. That's is why all I use left join on table1. Table1 will always have a user.
The problem with this is that for some reason I get the wrong results. Using the temporary tables gives me the right results.
Here is my failed attemt to convert to php which generates a "supplied argument is not a valid MySQL result resource in line "
PHP Code:
$qry1 = "CREATE TEMPORARY TABLE TempTable0 IF NOT EXISTS SELECT uniqueID, field1, field2 from user";
$qry2 = "CREATE TEMPORARY TABLE TempTable1 IF NOT EXISTS SELECT a.uniqueID, count(b.uniqueID) AS total1 FROM TempTable0 a LEFT JOIN table1 b on a.uniqueID = b.uniqueID GROUP BY a.uniqueID";
$qry3 = "CREATE TEMPORARY TABLE TempTable2 IF NOT EXISTS SELECT a.uniqueID, count(b.uniqueID) AS total2 FROM TempTable0 a LEFT JOIN table2 b on a.uniqueID = b.uniqueID GROUP BY a.uniqueID";
$qry4 = "CREATE TEMPORARY TABLE TempTable3 IF NOT EXISTS SELECT a.uniqueID, count(b.uniqueID) AS total3 FROM TempTable0 a LEFT JOIN table3 b on a.uniqueID = b.uniqueID GROUP BY a.uniqueID";
$qry5 = "CREATE TEMPORARY TABLE TempTable4 IF NOT EXISTS SELECT a.uniqueID, count(b.uniqueID) AS total4 FROM TempTable0 a LEFT JOIN table4 b on a.uniqueID = b.uniqueID GROUP BY a.uniqueID";
$merge_qry = mysql_query("SELECT a.field1, a.field2, a.uniqueID, b.total1, c.total2, d.total3, e.total4 FROM $qry1 a
LEFT JOIN $qry2 b ON a.uniqueID = b.uniqueID
LEFT JOIN $qry3 c ON a.uniqueID = c.uniqueID
LEFT JOIN $qry4 d ON a.uniqueID = d.uniqueID
LEFT JOIN $qry5 e ON a.uniqueID = e.uniqueID where a.uniqueID='jeff'");
if ($merge_qry){echo 'Yeah!';}else{echo 'error....';};
|
Dev Articles Community Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
