Temporary Tables Challenge!
Hello all you PHP/MySQL experts out there;
I am trying to write a script in PHP which will create a temporary table, insert some data into it from an existing table, and then run a select statement on the temporary table data.
I can do this without a problem on my MySQL server when I use the following in the SQL window against the database and not a table;
CREATE TEMPORARY TABLE l_table SELECT home_team AS Team, home_score AS Pts
FROM tblResults WHERE date >= '2007-09-13' AND division = '2';
INSERT INTO l_table SELECT away_team AS Team, away_score AS Pts FROM tblResults WHERE date >='2007-09-13' AND division = '2';
SELECT Team, Count(Pts)AS Pld, Sum(Pts) AS Pts FROM l_table
GROUP BY TEAM ORDER BY Pts DESC
But when I put this into PHP like this;
$query = "CREATE TEMPORARY TABLE l_table (Team varchar(50), pld integer (2), Pts integer (3))TYPE=HEAP";
"LOCK TABLE l_table WRITE";
"SELECT home_team AS Team, home_score AS Pts FROM tblResults WHERE date >= '2007-09-13' AND division = '" . $_GET['division_id'] ."'";
"INSERT INTO l_table SELECT away_team AS Team, away_score AS Pts FROM tblResults WHERE date >='2007-09-13' AND division = '2'";
"SELECT Team, Count(Pts)AS Pld, Sum(Pts) AS Pts FROM l_table GROUP BY Team ORDER BY Pts DESC";
"DROP TABLE l_table";
"UNLOCK TABLE l_table";
$result = mysql_query($query, $conn)
or die(mysql_error());
$num = mysql_num_rows($result);
But but get php error messages;
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in #### on line 26
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in #### on line 46
Where am I going wrong???
Is it that the table is not being created and populated?
Do I need to include the USE statement against the database?
Any advice would be much, much, much, appreciated!
|
