SGreen@stripped wrote:
> Alex Gemmell <agemmell@stripped> wrote on 11/30/2005 10:06:09 AM:
>
>
>>Hi,
>>
>>Unfortunately I need to use a query on an old MySQL (4.0.xx) and the one
>
>
>>I currently have uses a subquery. So it works on my 4.1 but not with
>>this 4.0. I have read in the MySQL manual that I can rewrite subqueries
>
>
>>using joins but I don't see how with this query because I only have one
>>table to check.
>>
>>Basically all I'm doing is pulling out the most recent 10 activities
>>stored in a table, then reversing the order so it appears oldest first,
>>to newest (note the "$row['Assignment_ID']" is just a PHP variable):
>>
>>SELECT * FROM
>>(SELECT * FROM tblactivities
>> WHERE Assignment_ID=$row['Assignment_ID']
>> ORDER BY Date DESC LIMIT 10) AS tblTemp
>>ORDER BY Date ASC
>>
>>This works perfectly on 4.1. How can I rewrite this to get the same
>>effect without using a subquery and so allow it to work on 4.0?
>>
>>Any help gratefully recieved!
>>
>>Alex
>>
>
>
> To make this work on 4.0, you have to do manually something the engine did
> for you implicitly: create a temporary table.
>
> CREATE TEMPORARY TABLE tmpSubquery SELECT *
> FROM tblactivites
> WHERE Assignment_ID=$row['Assignment_ID']
> ORDER BY Date DESC LIMIT 10;
>
> SELECT * from tmpSubquery ORDER BY Date ASC;
>
> DROP TEMPORARY TABLE tmpSubquery;
>
>
> There are only two things to remember to make this work.
> 1) All of these statements must be on the same, continuously connected
> connection. You cannot close the connection between the
> statements.
> 2) If you do not drop the temp table and you try to execute this set of
> commands on the same continuous connection, you will get an error.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
Thanks Shawn but I couldn't seem to get this to work.
I made the point of checking the MySQL User that PHP connects with. I
gave it CREATE and DROP privilages on the database schema in question.
It previously only had SELECT privilages.
Here's my PHP code to show you how I tried it:
[PHP]
$query = "CREATE TEMPORARY TABLE tmpSubquery
SELECT * FROM tblactivities
WHERE Assignment_ID='".$row['Assignment_ID']."'
ORDER BY Date DESC LIMIT 10;
SELECT * FROM tmpSubquery ORDER BY Date ASC;
DROP TEMPORARY TABLE tmpSubquery;";
$result_activities = mysql_query($query);
[/PHP]
I tried splitting up the queries to see if it helped but it didn't work
either:
[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]
Perhaps I am missing something obvious here? I hope you or anyone else
can offer some more help.
Alex
