Re: MySQL: Ordering Random Records - Ignatius Reilly - com.mysql.lists.win32

15 messages in com.mysql.lists.win32Re: MySQL: Ordering Random Records

From	Sent On	Attachments
Stuart M. Robinson	05 Oct 2003 10:15
Ignatius Reilly	05 Oct 2003 11:00
Stuart M. Robinson	05 Oct 2003 12:14
Ignatius Reilly	05 Oct 2003 14:33
Stuart M. Robinson	06 Oct 2003 13:13
Ignatius Reilly	06 Oct 2003 14:13
Stuart M. Robinson	06 Oct 2003 15:24
Ignatius Reilly	06 Oct 2003 22:39
Stuart M. Robinson	07 Oct 2003 13:26
Stuart M. Robinson	07 Oct 2003 13:29
Stuart M. Robinson	07 Oct 2003 16:00
jbon...@sola.com.au	07 Oct 2003 18:08
Matt W	07 Oct 2003 21:59
Stuart M. Robinson	04 Dec 2003 01:01
Armando	04 Dec 2003 04:49

Subject:	Re: MySQL: Ordering Random Records
From:	Ignatius Reilly (igna...@free.fr)
Date:	10/06/2003 02:13:01 PM
List:	com.mysql.lists.win32

With 4.1 you will be able to drop temporary tables with a simple DELETE grant.

Until then, since you absolutely must not grant DROP TABLE rights to your PHP user, you have to re-establish a fresh MySQL connection whenever you want to run the script again.

Or if, you need all the results in the same MySQL connection, increment your table name T1...Tn. Rather inefficient memorywise, though.

HTH Ignatius

_________________________

Ignatius,

Many thanks for the pointer and outline, I have the page running and it's doing exactly what I want, pulling twenty random records from the database and then ordering them alphabetically. However, I've hit a snag.

After the test page has been viewed successfully, the second time it is loaded an error is produced:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [MySQL][ODBC 3.51 Driver][mysqld-4.0.14-nt]Table 't1' already exists /test.asp, line 13

This looks like the temporary table isn't being dropped after the routine, so do I need that command in my SQL? This is why I have for the recordset routine:

<% Dim rsTest Dim rsTest_numRows Set rsTest = Server.CreateObject("ADODB.Recordset") rsTest.ActiveConnection = MM_test_STRING rsTest.Source = "CREATE TEMPORARY TABLE T1 SELECT * FROM MyDatabase.MyTable ORDER BY RAND() LIMIT 20" rsTest.CursorType = 0 rsTest.CursorLocation = 2 rsTest.LockType = 3 rsTest.Open() rsTest.Source = "SELECT record1, record2, record3 from T1 ORDER BY record1 ASC" rsTest.Open rsTest_numRows = 0 %>

For what it's worth, if I change the "T1" name to "T2" the routine will run, but only once or twice before the same error appears, this time stating that T2 already exists.

The user permissions for my connection are currently set to allow: Select, Insert, Update, Delete, Create, Drop and Create Temp Tables. I'm not sure how many are needed, but I want the SQL to work before tightening up the permissions.

Stuart.

At 23:33 05/10/2003 +0200, Ignatius Reilly wrote:

OK, Stuart, I understand now what you want to do.

You can not do it in one step (subqueries not yet supported). Therefore:

CREATE TEMPORARY TABLE T1 SELECT record1, ... ORDER BY RAND() LIMIT 20

then

SELECT ... FROM T1 ORDER BY record1

HTH Ignatius