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

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"> </td>
<!--
get news items to display on the homepage
--><?php
if ($rowїnews] == '1') {
$news_query = "SELECT * FROM news ORDER BY RAND() LIMIT 1";
}
else {
$news_query = "SELECT * FROM news ORDER BY date_modified DESC LIMIT 1";
}
$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їnews_title]) > 100) ? substr($news_rowїnews_title], 0, 99)."..." : $news_rowїnews_title]);?></div></td>
<td height="42" width="11"> </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"> </td>
<!--
get news items to display on the homepage
--><?php
if ($rowїnews] == '1') {
$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";
}
else {
$news_query = "SELECT * FROM news ORDER BY date_modified DESC LIMIT 1";
}
$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їnews_title]) > 100) ? substr($news_rowїnews_title], 0, 99)."..." : $news_rowїnews_title]);?></div></td>
<td height="42" width="11"> </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
Posted: Wed Sep 25, 2002 9:55 am
by Crashin
Thank you, hob_goblin. That did the trick.
