Selecting ONE Random Row

[jstarkweather]

Ok...I know this in an old subject. I have read NUMEROUS threads here and even more search results on this, yet none seem to apply to my needs.

I want to select a single random row WHERE selected=yes.

I have tried various incarnations of the "SELECT * FROM table_name WHERE $selected=yes ORDER BY RAND() LIMIT 1", but nothing seems to work.

Any ideas would be great! If I can't get the RAND() function working my backup is to just specify the field_id's manually and have it randomly select from those. So any ideas on that front would also be helpful as again using PHP random number generation (which is how everyone seems to come at this question) doesn't really work on an array solution either.

Thanks,
Jim

[hand]

"SELECT FROM table_name WHERE selected='yes' ORDER BY RAND() LIMIT 1" if selected is a db field
or
"SELECT FROM table_name WHERE $selected='yes' ORDER BY RAND() LIMIT 1" if $selected is a variable

[jstarkweather]

hand,
'selected' is a field in the table. The syntax you gave me is what I tried first though. I'm not sure what is flubbing up the output but I keep getting db errors.

Warning: Supplied argument is not a valid MySQL result resource in file.php on line 81 (the "while" line)

$query = "SELECT * FROM dbname_features WHERE featured='1' ORDER BY RAND() LIMIT 1";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
echo "$row[artid]";
}

Thanks,
Jim

[hand]

I added an or statement for getting more informations. Please try it again with this code.

$query = "SELECT * FROM dbname_features WHERE featured='1' ORDER BY RAND() LIMIT 1";
$result = mysql_query($query)
	or print "&gt;&gt;&gt; MySQL-Error: ".mysql_errno()." -&gt; ".mysql_error()."<br>\n";

while ($row = mysql_fetch_array($result)) {
	echo "$row[artid]";
}

[wsh]

New to this and not too sure exactly what you want but... maybe this will help????

$res1=mysql_query("SELECT FROM table_name WHERE selected='yes'");
$num=mysql_num_rows($res1);
$randnum=rand(0,$num);

$res=mysql_query("SELECT FROM table_name WHERE selected='yes' LIMIT $randnum, 1");

Not even sure if that works... if you have an ID column you can do it by:

$res1=mysql_query("SELECT FROM table_name WHERE selected='yes'");

while($row=mysql_fetch_array($res1)) {
$IDarray=$num.",'$row[id]'";
}
$count1=count($IDarray);

$randid=rand(0,$count1);

$randomid=$IDarray[$randid];

$res=mysql_query("SELECT FROM table_name WHERE id=$randomid");

Heh... dont count on my spelling or typing and double check it all... but it mite work (have no idea).

[jstarkweather]

error detail:

MySQL-Error: 1064 -> You have an error in your SQL syntax near 'RAND() LIMIT 1' at line 1

I will try Wsh's idea I guess.

Thanks for the help guys.

Jim

[jstarkweather]

Hmmm.....well that wasn't working as I had it written. I backtracked a bit to check the variables. The $IDarray has only one number (,'81') and there are 4 matches in the db.

$result = mysql_query("SELECT artid FROM $table[seccont] WHERE featured='1'");

while ($row=mysql_fetch_array($result)) {
$IDarray=$num.",'$row[artid]'";
}
echo "$IDarray";

echos: ,'81'

Any ideas??

Jim

[wsh]

$result = mysql_query("SELECT artid FROM $table[seccont] WHERE featured='1'");

while ($row=mysql_fetch_array($result)) {
$IDarray=$IDarray.",'$row[artid]'";
}
echo "$IDarray";

should work

[jstarkweather]

Ok...

now $IDarray spits out: ,'49','56','80','81'

but $count1 gives me: 1

🙂 progress though.

Jim

[wsh]

LOL... Simpler way:

$result=mysql_query("SELECT artid FROM $table[seccont] WHERE featured='1'");

$IDarray=mysql_fetch_array($result);

$count1=mysql_num_rows($result);

[wsh]

THEN comes

$randid=rand(0,$count1);

$randomid=$IDarray[$randid];

$res=mysql_query("SELECT FROM table_name WHERE id=$randomid");

[wsh]

maybe? maybe not?😕

[wsh]

probably not... maybe this?

$i=1;
while($i < mysql_num_rows($res)) {
$IDarray=$IDarray."'$row[id]',";
$i++;
}
$IDarray=$IDarray."'$row[id]'";

$count1=count($IDarray);

$randid=rand(0,$count1);

$randomid=$IDarray[$randid];

if not.. try setting $i to 0... am really confused. trying to figure out a search engine.. so my brain is almost off right now.

[jstarkweather]

Nope...$count is still = 1

🙁

Thanks for the help.

Jim

[Flameeyes]

mysql_free_result($result); // if you have uset $result before

$query = "SELECT * FROM dbname_features WHERE featured='1' ORDER BY RAND() LIMIT 1";

$result = mysql_query($query);
if ( mysql_errno() )
  die( "Error executing query $query<br>".mysql_errno().": ".mysql_error());

if ( mysql_num_rows($result) > 0 )
  die("Error! The LIMIT 1 doesn't work!");

$row = mysql_fetch_array($result);

echo $row['artid'];

try this and post the error if it die.

[jstarkweather]

Sorry for taking so long to get back to this. Here is the error I get with the error checks in place.

Error executing query SELECT * FROM dbname_features WHERE featured='1' ORDER BY RAND() LIMIT 1
1064: You have an error in your SQL syntax near 'RAND() LIMIT 1' at line 1

And yes I have the correct dbname in there. 🙂

Here is the code:

$query = "SELECT * FROM dbname_features WHERE featured='1' ORDER BY RAND() LIMIT 1";

$result = mysql_query($query);
if ( mysql_errno() )
die( "Error executing query $query<br>".mysql_errno().": ".mysql_error());

if ( mysql_num_rows($result) > 0 )
die("Error! The LIMIT 1 doesn't work!");

while ($row = mysql_fetch_array($result)) {
$content .= "blah$row[artid]blah";

}

Thanks for da help!

Jim