List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 30 2005 4:56pm
Subject:Re: Rewriting subquery for old MySQL
View as plain text  
Alex Gemmell wrote:
<snip>
> 
> 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.

 From the manual <http://dev.mysql.com/doc/refman/5.0/en/create-table.html>:
"You must have the CREATE TEMPORARY TABLES  privilege to be able to create 
temporary tables."

See the manual for details 
<http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html>.

> 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]

Even if that would work, it's not a good idea.  One query at a time, so you know 
what to check when you get an error.

> I tried splitting up the queries to see if it helped but it didn't work 
> either:

Yes, that's the way to go.

> [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]

No semicolons (;) needed in the actual queries from php.  The semicolon is the 
end-of-query delimiter in the mysql client -- it isn't needed from php.

> Perhaps I am missing something obvious here?  I hope you or anyone else 
> can offer some more help.

You are missing the code which verifies each query worked and prints the error 
message if it didn't.  If you don't print the error from mysql, how will you 
know what to fix?

I recommend following Example 1 in the PHP manual
<http://www.php.net/manual/en/ref.mysql.php#mysql.examples>, but I'd add that
you should actually print the query string in addition to the error.  That way,
you can verify the query is actually what you intended.  With that in mind, I'd 
do something like this:

   $query = "CREATE TEMPORARY TABLE tmpSubquery
             SELECT * FROM tblactivities
             WHERE Assignment_ID='".$row['Assignment_ID']."'
             ORDER BY Date DESC LIMIT 10";
   mysql_query($query) or die("Query: $query failed: " . mysql_error());

   $query = "SELECT * FROM tmpSubquery ORDER BY Date ASC";
   $result = mysql_query($query)
           or die("Query: $query failed: " . mysql_error());

   $query = "DROP TEMPORARY TABLE tmpSubquery";
   mysql_query($query) or die("Query: $query failed: " . mysql_error());

> Alex

Michael

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