Programming Forums - A random entry from MYSQL database

Programming Forums (http://www.programmingforums.org/forumindex.php)

- PHP (http://www.programmingforums.org/forum29.html)

- - A random entry from MYSQL database (http://www.programmingforums.org/showthread.php?t=1807)

scorpiosage

Jan 11th, 2005 5:44 PM

A random entry from MYSQL database

php.net doesn't help it's in none of my books, and I am having some issues, cause I need to do this.

Here's the project,
I am doing work on a Realtor website where on the index page there is a featured listing section.

The database is set up to where there are the fields, mls, streetnum, streetname, city, state, zip
as well as some other fields, too many to mention I guess for now.

Well, I could just place a button on the admin and on a daily basis mark one of the listings as featured, however, I thought it would be better if on loading the page a random entry would appear.

here's the issue. The following came from php.net but does not work.

$sql = "SELECT * FROM listings ORDER BY RAND() LIMIT 1";

$result = @mysql_query($sql, $connection) or die(mysql_error());

$mlsran = $row['mls'];

?>

</head>

<body>

<? echo $mlsran;

?>

And this really doesn't echo the mls entry at all.

Any thoughts?
Thanks
Mike

kurifu

Jan 11th, 2005 6:34 PM

Umm... I am not sure you can use RAND() in that context. RAND() is a function, not a description as to how to go about sorting your entires. Wouldn't it be more convenient to select a random number, and just do "LIMIT <random_number>, 1" just verify rand is not greater than your row count (simple task).

Pizentios

Jan 11th, 2005 7:04 PM

where does $row get set? it look like you need to fill $row with your data. So somthing like this:

[php]
<?PHP
$sql = "SELECT * FROM listings ORDER BY RAND() LIMIT 1";
$result = @mysql_query($sql, $connection) or die(mysql_error());
$row = mysql_fetch_object($result);
?>
</head>
<body>
<?PHP
echo $row->mls;
?>
[/php]

try that. i haven't worked that much with MySQL, but it should work for you. I could be wrong, though, since you might have set $row above the code that you posted.

scorpiosage

Jan 12th, 2005 2:37 AM

It took some doing but I figured it out. I am posting it so that anyone who has this question can come and see...

$connection = mysql_connect("host","user","pass") or die(mysql_error());

$db_name = dbname;

$db = @mysql_select_db($db_name,$connection) or die(mysql_error());

$sql = "SELECT * FROM table condition";

$result = @mysql_query($sql, $connection) or die(mysql_error());

$num_rows = mysql_num_rows($result);

$mlsrandom = rand(1,$num_rows);

for ($i = 1; $i <= $mlsrandom; $i++) {

$row = mysql_fetch_array($result);

this is a realestate thing, so anything that is called mls anything is specific to my database.
Hope this helps someone
Mike

codetaino

Jan 12th, 2005 1:32 PM

Hi just to make you people know... I had done the same task using the original sql query posted. It is supposed to work on mysql servers latter than MySQL 3.23. Maybe the problem was in how you mannaged the connection or the variable resutl. I dont think you submited the whole code... again this is just for info.

happy to read you solve the problem! :D

codetaino

All times are GMT -5. The time now is 3:50 AM.