On Wednesday 30 November 2005 18:11, Alex Gemmell wrote:
> [EMAIL PROTECTED] wrote:
> > Alex Gemmell <[EMAIL PROTECTED]> 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
Hi Alex,
a ";" after the tmpSubquery above would be nice...:-)
-Stathis
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
