List:General Discussion« Previous MessageNext Message »
From:SGreen Date:November 30 2005 5:02pm
Subject:Re: Rewriting subquery for old MySQL
View as plain text  
rouvas <rouvas@stripped> wrote on 11/30/2005 10:18:33 AM:

> On Wednesday 30 November 2005 18:11, Alex Gemmell wrote:
> > 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
> 
> Hi Alex,
> 
> a ";" after the tmpSubquery above would be nice...:-)
> 
> -Stathis

Uhhh --- NO! The CREATE ... SELECT... is all one complete single 
statement. If we did it your way, you would be asking MySQL to create a 
table with no columns in it. RTFM if you don't beleive me:
http://dev.mysql.com/doc/refman/4.1/en/create-table.html

I don't know PHP well enough to say for certain what your problem might 
be. Can you post your error codes? 

My suspicion is the trailing semicolons are causing the problems. Those 
are used in the CLI but my copy of the PHP docs say that the query string 
should not end a semicolon.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



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