Programming Forums > Web Development and Design > PHP

scorpiosage · Jan 11th, 2005, 6:44 PM

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.

(Toggle Plain Text)

$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, 7: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, 8: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, 3: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...

(Toggle Plain Text)

$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, 2: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!

codetaino

Jan 11th, 2005, 6:44 PM	#1
scorpiosage Programmer Join Date: Aug 2004 Location: Austin, Tx Posts: 55 Rep Power: 5	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. (Toggle Plain Text) $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; ?> $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 __________________ Here's my latest project still in the works, and I need to get rid of this "frame" situation for real. www.prideofaustin.com

Jan 11th, 2005, 7:34 PM	#2
kurifu Expert Programmer Join Date: Jul 2004 Location: Halifax, Nova Scotia (Canada) Posts: 784 Rep Power: 5	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). __________________ Clifford Matthew Roche <geek@cliffordroche.com> Web Hosting: http://www.crd-hosting.com Consulting: http://www.crdev-consulting.com

Jan 11th, 2005, 8:04 PM	#3
Pizentios Programming Guru Join Date: May 2004 Location: Brandon, Manitoba, Canada Posts: 2,023 Rep Power: 7	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. __________________ Profanity is the one language that all programmers understand. Check out my Blog <---updated Nov 30 2007!

Jan 12th, 2005, 3:37 AM	#4
scorpiosage Programmer Join Date: Aug 2004 Location: Austin, Tx Posts: 55 Rep Power: 5	It took some doing but I figured it out. I am posting it so that anyone who has this question can come and see... (Toggle Plain Text) $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); $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 __________________ Here's my latest project still in the works, and I need to get rid of this "frame" situation for real. www.prideofaustin.com

Jan 12th, 2005, 2:32 PM	#5
codetaino Programmer Join Date: Jan 2005 Location: Bayamon, Puerto Rico Posts: 71 Rep Power: 4	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! codetaino __________________ "God bless u all" :)

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Programming Forums > Web Development and Design > PHP

A random entry from MYSQL database