OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
 
Re: Rewriting subquery for old MySQL - SOLVED - correction!

From: Alex Gemmell (agemmellgmail.com)
Date: Tue Dec 06 2005 - 09:27:39 CST

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