Page 1 of 1

random record

Posted: Fri Sep 20, 2002 12:43 pm
by sinewave
what is a simple way to retrieve a random record from a database (mySQL)...i understand RAND() but i dont think that would really help me.

Essentially i want to create a script for managing banners and would like a random one displayed, so i'd like it to grab a random record...makes sense?

thx

Posted: Fri Sep 20, 2002 12:48 pm
by Takuma

Code: Select all

<?php
mysql_connect("host","user","password");
mysql_select_db("db");

$result = mysql_query("SELECT id FROM banners");
$num = mysql_num_rows($result);

$random = rand($num);
$result = mysql_qeury("SELECT * FROM banners WHERE id = $random");
$banner = mysql_fetch_array($result);
?>
This will code will put all the details of banner into $banner.

Posted: Fri Sep 20, 2002 1:14 pm
by JPlush76
you don't want to do that actually .. now you have to make 2 calls to your database and it drops your performance

you can generate a random row directly in your mysql call

example:


Code: Select all

<?php

$result = mysql_query("SELECT cust_first, cust_email, RAND( ) as rand_col FROM customers ORDER BY rand_col LIMIT 1  "); 
?>
if you have mysql 3.23.2 or newer you can just do

ORDER BY RAND()

Posted: Fri Sep 20, 2002 1:37 pm
by ~J~R~R

Code: Select all

<?php 

$result = mysql_query("SELECT cust_first, cust_email FROM customers ORDER BY RAND() LIMIT 1  ");  
?>
Always try to optimize :P

Posted: Fri Sep 20, 2002 1:43 pm
by JPlush76
remember though you can only use ORDER BY RAND() if you have mysql 3.23.2 or newer , otherwise you have to use the example I coded above.

While you might have newer on your local machine, make sure you know what your client's machine has.

Posted: Tue Sep 24, 2002 4:39 pm
by Crashin
I've been trying the above suggestions on my site and keep getting the following (query echoed before error):

SELECT * FROM news ORDER BY RAND() LIMIT 1
Warning: Supplied argument is not a valid MySQL result resource in /u/web/chec36/development/website/index.php on line 44

My MySQL version is 3.23.32 (as of last April, anyway...). What am I missing?

Posted: Tue Sep 24, 2002 4:47 pm
by JPlush76
thats weird, it must be in your code then because I just cut and paste your query into my php myadmin and it worked fine after I changed the table name to one in my database

can you post your section of code? also note that line number that you're getting the error on.

Posted: Tue Sep 24, 2002 4:59 pm
by Crashin
Sure...here's the code:

Code: Select all

<!--
get preferences for page display
--><?php
$query = "SELECT * FROM preferences_web";
$result = mysql_query($query) or die('Cannot get PREF - Please try again later.');
$row = mysql_fetch_array($result);?>

<tr>
	<td height="42" width="11">&nbsp;</td>
	
	<!--
	get news items to display on the homepage
	--><?php
	if ($row&#1111;news] == '1') &#123;
		$news_query = "SELECT * FROM news ORDER BY RAND() LIMIT 1";
	&#125;
	else &#123;
		$news_query = "SELECT * FROM news ORDER BY date_modified DESC LIMIT 1";
	&#125;
	$news_result = mysql_query($news_query) /*or die('Cannot get NEWS - Please try again later.')*/;
	echo $news_query;
	$news_row = mysql_fetch_array($news_result);?>
	
	<td height="42" width="155" valign="center"><div class="body"><?php echo ((strlen($news_row&#1111;news_title]) > 100) ? substr($news_row&#1111;news_title], 0, 99)."..." : $news_row&#1111;news_title]);?></div></td>
	<td height="42" width="11">&nbsp;</td>
</tr>
There's more code to the page (obviously), but this is the problem area. Did you need to see more?

Posted: Tue Sep 24, 2002 6:01 pm
by Crashin
Um...here's a lesson in NOT relying on the technical support of your friendly neighborhood web hosting technical support. I was told by them that they are running MySQL 3.23.32. I checked phpMyAdmin to discover, alas, they are running 3.22.23. Ugh!!!

Anyway, I'm trying one of the methods from above for earlier versions of MySQL and still without luck. Eg:

Code: Select all

<tr>
<td height="42" width="11">&nbsp;</td>

<!--
get news items to display on the homepage
--><?php
if ($row&#1111;news] == '1') &#123;
	$news_count = "SELECT * FROM news";
	$news_count_result = mysql_query($news_count);
	$num = mysql_num_rows($news_count_result);
	$random = rand($num);
	$news_query = "SELECT * FROM news LIMIT ".$random.", 1";
&#125;
else &#123;
	$news_query = "SELECT * FROM news ORDER BY date_modified DESC LIMIT 1";
&#125;
$news_result = mysql_query($news_query) /*or die('Cannot get NEWS - Please try again later.')*/;
echo $num;
echo $news_query;
$news_row = mysql_fetch_array($news_result);?>

<td height="42" width="155" valign="center"><div class="body"><?php echo ((strlen($news_row&#1111;news_title]) > 100) ? substr($news_row&#1111;news_title], 0, 99)."..." : $news_row&#1111;news_title]);?></div></td>
<td height="42" width="11">&nbsp;</td>
</tr>
I'm getting:

Warning: Wrong parameter count for rand() in /u/web/chec36/development/website/index.php on line 40
16SELECT * FROM news LIMIT , 1
Warning: Supplied argument is not a valid MySQL result resource in /u/web/chec36/development/website/index.php on line 55

Any ideas?

Posted: Tue Sep 24, 2002 7:36 pm
by hob_goblin
change

Code: Select all

$random = rand($num);
to

Code: Select all

$random = rand(0,$num);

Posted: Wed Sep 25, 2002 9:55 am
by Crashin
Thank you, hob_goblin. That did the trick. :D