rouvas wrote:
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
Actually, not a ";" but brackets around the SELECT statement were missing.
Also, I needed to give the User PHP was using to connect to MySQL the
CREATE_TMP_TABLE privilage - obviously... :0)
So here's the final bit of code in PHP. I needed to split up the
queries as they wouldn't work in one go, but as Shawn pointed out this
all must happen on the same connection thread to MySQL:
[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 for the SQL Shawn!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
