Gossamer Forum
Quote Reply
ORDER BY RAND()
Anyone know why this isn't 'ordering' by rand() ? There are 3 entries in the SQL database, of which 2 of them should match. The appropriate code is;

Code:
# get the category ID we wanna use...
my $CatID = shift;

print "Checking for category $CatID<BR>";

my $banner;

# grab the stuff form the database...
my $table = $DB->table('CatAdverts');
$table->select_options ("ORDER BY RAND()",'LIMIT 1');
my $sth = $table->select( GT::SQL::Condition->new('Categories', 'LIKE', "% $CatID %") );

And the data in the MySQL database/table is;

Code:
SetID Title URL Categories
1 Test Title http://www.google.com 1 2 3 4 7 9 11
2 Test Title 2 http://www.google.com 1 2 3 4 7 9 11
3 test again http://www.google.com 3 4 1 2

Anyone got any ideas? I always brings up SetID 1 Unsure

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] ORDER BY RAND() In reply to
Uh oh, I spy bad database design.

Please tell me you aren't storing multiple category id's all in one column.

You should be creating a new table like:

Code:
SetID CatID
1 1
1 3
1 5
1 7
1 9
1 11
Quote Reply
Re: [Paul] ORDER BY RAND() In reply to
So , what you are saying, is have a new table, with the data in it like you just suggested, and then do a RAND() call on that, and then do a specific call for that with a GT::SQL::Condition?

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] ORDER BY RAND() In reply to
Ok...I've done what you suggested Paul. For some reason, the following code isn't working;

Code:
my ($banner, $sth2, $table2, $cond);

# grab the stuff form the database...
$table2 = $DB->table('CatAdvertsIDs');
$table2->select_options ("ORDER BY RAND()");
$sth2 = $table2->select( GT::SQL::Condition->new('SetID', '=', 4) );


# only do this if we actually got a result from above...
my $id_got;
if ($sth2) {
while (my $hit = $sth2->fetchrow_hashref) {

$id_got = $hit->{ID}; # get the advert ID...
print "here"; # $hit->{ID} is the category ID...
}
}

If I run the following via MySQLMonitor, I get a result;

SELECT * FROM lsql_CatAdvertsIDs WHERE SetID = 4

Unsure I've spent the last 2+ hours trying to figure this one out. I've even got to the point of physically printing the ID looked for, rather than it besing passed as a variable, but it still ain't working! ARGH!

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] ORDER BY RAND() In reply to
Hmm. I will not able to tell the problem, without trying out.
However at first fight I see 1 thing which is unnecessary:
$sth2 = $table2->select( GT::SQL::Condition->new('SetID', '=', 4) );

Would be same to use:
$sth2 = $table2->select( { SetID => 4 } );

Best regards,
Webmaster33


Paid Support
from Webmaster33. Expert in Perl programming & Gossamer Threads applications. (click here for prices)
Webmaster33's products (upd.2004.09.26) | Private message | Contact me | Was my post helpful? Donate my help...
Quote Reply
Re: [webmaster33] ORDER BY RAND() In reply to
Eugh..duh! The reason I have a GT::SQL::Condition was becauase I WAS using a LIKE condition. Anyway, this is what I have now;

Code:
# grab the stuff form the database...
$table2 = $DB->table('CatAdvertsIDs');
$table2->select_options ("ORDER BY RAND()","LIMIT 1");
$sth2 = $table2->select( { SetID => 4 } );

# only do this if we actually got a result from above...
my $id_got;
if ($sth2) {
while (my $hit = $sth2->fetchrow_hashref) {
print $hit->{ID}; # get the advert ID...
}
}

It has 4 entires in the database;

SetID ID
4 1
4 2
4 3
4 4


For some reason, it's idea of RAND(), is choosing the first entry iun the database every time now Unsure

This RAND function is really starting to get on my nerves Unimpressed

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] ORDER BY RAND() In reply to
I remember you asking a question like this already.

http://gossamer-threads.com/...rname=Alex&mh=50

Last edited by:

Paul: Jan 29, 2003, 3:10 AM
Quote Reply
Re: [Paul] ORDER BY RAND() In reply to
Yeah, I know about the RAND(NOW()) function. I tried this already, but its still giving the same number Frown See the 'random' work in action;

http://www.ace-host.com/...s/index.html&d=1

As you will see, it always shows '4' (2nd line down).

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] ORDER BY RAND() In reply to
Is your version of MySQL new enough to use this? There were some problems with using this feature in earlier stable versions of MySQL.


PUGDOG´┐Ż Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] ORDER BY RAND() In reply to
Yup, its the newest version I believe. I've managed to find a bit more of long winded way to do it, but its working now Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!