problems trying to fetch random row from mysql!

[daveh33]

The error I get

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in members.php on line 52

 

The code is: -

 

if ($promoused=="USED" && $siddate==$today) {
$promotion = $promoid;
$result2 = mysql_query("SELECT id from promotions WHERE id='promotion' Order by id") or die(mysql_error());
$row = mysql_fetch_array( $result2 ) or die(mysql_error());
} else {
$result2 = mysql_fetch_row(mysql_query("select * from promotions WHERE used='n' order by rand() limit 1"));
52. [u]$row = mysql_fetch_array( $result2 ) or die(mysql_error());[/u]
} 
$title = $row['title'];

 

Can anyone see whats wrong?

[enoyhs]

First error I see is:

if ($promoused=="USED" && $siddate==$today) {

$promotion = $promoid;

$result2 = mysql_query("SELECT id from promotions WHERE id='$promotion' Order by id") or die(mysql_error());

$row = mysql_fetch_array( $result2 ) or die(mysql_error());

} else {

$result2 = mysql_fetch_row(mysql_query("select * from promotions WHERE used='n' order by rand() limit 1"));

52. $row = mysql_fetch_array( $result2 ) or die(mysql_error());

}

$title = $row['title'];

 

Second error from your message is:

You can't order rand(). You have to order by one of rows. So one of the ways is write all rows in array and then $arr[rand()].

Something like this:

$arr[0]='row1';
$arr[1]='row2';
$arr[2]='row3';
$arr[3]='row4';
// And so on...
$result2 = mysql_fetch_row(mysql_query("select * from promotions WHERE used='n' order by " . $arr[rand(0,count($arr)) . " limit 1"));

[daveh33]

OK changed 1st error - how do you use $arr[rand()]?

[enoyhs]

Edited my post above before you posted...

 

Also note that this won't really give you very random results because it will have 1/number of tables(mistakenly mentioned as rows before).

I can't currently think of any better way I but I will try and think...

[daveh33]

So would it be

 

$arr[0]='row1';
$arr[1]='row2';
$arr[2]='row3';
$arr[3]='row4';
$arr[4]='row5';
$arr[5]='row6';
$arr[6]='row7';
$result2 = mysql_fetch_row(mysql_query("select * from promotions WHERE used='n' order by " . $arr[rand(0,count($arr)) . " limit 1"));
$row = mysql_fetch_array( $result2 ) or die(mysql_error());
} 

 

[GingerRobot]

Actually, if you are trying to retrieve rows in a random order, then you can indeed 'order by rand()':

 

mysql_query("SELECT * FROM promotions WHERE used='n' ORDER BY rand()") or die(mysql_error());

 

But im a little confused. Are you trying to return your rows in a random order, are you trying to sort by a random column?

[enoyhs]

Yes. It will give you one of rows (tables) to order by so you will have a chance of getting specific result in 1/count($arr) times.

 

But I think better way to pick random would be by randoming id number and then picking specific id. If there are missing id just pick one closest...

 

PS. Oh... Didn't know that GingerRobot. Ok so my statement isn't correct I suppose.

[daveh33]

OK - I have a field called id in the table.  there is 6 records I want to select a random row which can be any where used='n'

 

I then want to assign $row['title'] etc..

[Barand]

Closing, as you've opened another thread on this topic. No point people wasting their time answering both.

 

Don't double post.