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

Thanks Micheal - yeah, I managed to figure out the CREATE_TMP_TABLE 
privilage problem myself.

I should have used those "die on errors" too - will do next time!

To recap here's the working final solution (after MySQL User has 
CREATE_TMP_TABLES and DROP privilages):

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

Thanks all!
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