Re: Rewriting subquery for old MySQL - SOLVED - correction!

[Alex Gemmell <suppressed>]

Alex Gemmell wrote:
 > Thanks Micheal - yeah, I managed to figure out the CREATE_TMP_TABLE
> privilage problem myself.
>
> I should have used those "die on errors" too - will do next time!
>
> To recap here's the working final solution (after MySQL User has 
> CREATE_TMP_TABLES and DROP privilages):
>
> [PHP]
> $query1 = "CREATE TEMPORARY TABLE tmpSubquery
>     (SELECT * FROM tblactivities
>      WHERE Assignment_ID='".$row['Assignment_ID']."'
>      ORDER BY Date DESC LIMIT 10);";
> mysql_query($query1);
>
> $query2 = "SELECT * FROM tmpSubquery ORDER BY Date ASC;";
> $result_activities = mysql_query($query2);
>
> $query3 = "DROP TEMPORARY TABLE tmpSubquery;";
> mysql_query($query3);
> [/PHP]
>
> Thanks all!

Correction - the "Query1" above failed due to a syntax error.  I removed 
the brackets I (stupidly) added and also the trailing semi-colon (which 
some people recommeded I do).

After doing that Query1 worked and is now an acceptable way of getting 
my orginal subquery to work on an old version 4.0 MySQL.

[PHP]
$query1 = "CREATE TEMPORARY TABLE tmpSubquery
    SELECT * FROM tblactivities
     WHERE Assignment_ID='".$row['Assignment_ID']."'
     ORDER BY Date DESC LIMIT 10";
mysql_query($query1);

$query2 = "SELECT * FROM tmpSubquery ORDER BY Date ASC;";
$result_activities = mysql_query($query2);

$query3 = "DROP TEMPORARY TABLE tmpSubquery;";
mysql_query($query3);
[/PHP]

Solved for real!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/suppressed