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!
