random record
Moderator: General Moderators
random record
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
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
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);
?>-
JPlush76
- Forum Regular
- Posts: 819
- Joined: Thu Aug 01, 2002 5:42 pm
- Location: Los Angeles, CA
- Contact:
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:
if you have mysql 3.23.2 or newer you can just do
ORDER BY RAND()
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 ");
?>ORDER BY RAND()
- ~J~R~R
- Forum Newbie
- Posts: 20
- Joined: Wed Sep 18, 2002 12:19 pm
- Location: Amsterdam, the Netherlanda
Code: Select all
<?php
$result = mysql_query("SELECT cust_first, cust_email FROM customers ORDER BY RAND() LIMIT 1 ");
?>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?
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?
Sure...here's the code:
There's more code to the page (obviously), but this is the problem area. Did you need to see more?
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>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:
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?
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>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?
- hob_goblin
- Forum Regular
- Posts: 978
- Joined: Sun Apr 28, 2002 9:53 pm
- Contact:
change
to
Code: Select all
$random = rand($num);Code: Select all
$random = rand(0,$num);