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]
