random record

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sinewave
Forum Commoner
Posts: 41
Joined: Tue Sep 10, 2002 4:35 pm
Location: Canada

random record

Post 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
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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.
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post 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()
User avatar
~J~R~R
Forum Newbie
Posts: 20
Joined: Wed Sep 18, 2002 12:19 pm
Location: Amsterdam, the Netherlanda

Post 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
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post 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.
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post 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?
JPlush76
Forum Regular
Posts: 819
Joined: Thu Aug 01, 2002 5:42 pm
Location: Los Angeles, CA
Contact:

Post 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.
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post 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?
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post 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?
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post by hob_goblin »

change

Code: Select all

$random = rand($num);
to

Code: Select all

$random = rand(0,$num);
User avatar
Crashin
Forum Contributor
Posts: 223
Joined: Mon May 06, 2002 3:42 pm
Location: Colorado

Post by Crashin »

Thank you, hob_goblin. That did the trick. :D
Post Reply