List:General Discussion« Previous MessageNext Message »
From:Alex Gemmell Date:December 6 2005 3:27pm
Subject:Re: Rewriting subquery for old MySQL - SOLVED - correction!
View as plain text  
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!
Thread
Rewriting subquery for old MySQLAlex Gemmell30 Nov
  • Re: Rewriting subquery for old MySQLSGreen30 Nov
    • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
      • Re: Rewriting subquery for old MySQLrouvas30 Nov
        • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
        • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
        • Re: Rewriting subquery for old MySQLSGreen30 Nov
      • Re: Rewriting subquery for old MySQLDan Nelson30 Nov
        • Re: Rewriting subquery for old MySQLAlex Gemmell30 Nov
          • Re: Rewriting subquery for old MySQLMichael Stassen30 Nov
      • Re: Rewriting subquery for old MySQLMichael Stassen30 Nov
        • Re: Rewriting subquery for old MySQL - SOLVEDAlex Gemmell30 Nov
          • Re: Rewriting subquery for old MySQL - SOLVED - correction!Alex Gemmell6 Dec