Getting a random result from a database
I'm not well versed in either mySQL or php but I do use them to call information from my database. I don't know if this code below is helpful but here's an example of the code I use on my homepage to list the last 10 people added to the database:
<?php
$db = mysql_connect(\"localhost\", \"XXXXX\", \"XXXXX\");
mysql_select_db(\"XXXXX\",$db);
$query = \"SELECT * FROM alumni ORDER BY lastupdated DESC LIMIT 10\";
$result = mysql_query ($query);
while($row = mysql_fetch_array($result)) {
print \"<a href=\\"/directory/info.php?id=\".$row['id'].\"\\"> \".$row['firstname'];
if (!empty($row['maidenname'])) print \" (\".$row['maidenname'].\")\n\";
print \" \".$row['lastname'].\"</a>\";
$class = substr($row[\"classyear\"], 2, 2);
print \" ('\".$class.\")<br />\";
}
?>What I need now is to generate a list of people in the database at random (with no care for when their records were updated). I want a completely random list of ten people from the directory. Can anybody help me with this? Thanks.
Busy posted this at 07:54 — 29th November 2005.
He has: 6,151 posts
Joined: May 2001
... order by lastupdated,RAND() ...
Greg K posted this at 15:15 — 29th November 2005.
He has: 2,145 posts
Joined: Nov 2003
You might have to do it this way:
SELECT *,RAND() AS sorter FROM alumni ORDER BY sorter LIMIT 10
I just did a test fo this on one of my tables, and it seems to work. Note that if by chance you have a field called "sorter" you will have to change that name above.
Also, if your table has more than the fields you are requesting, you should modify it to be
SELECT `firstname`,`maidenname`,`lastname`,`classyear`, RAND() AS sorterFROM alumni
ORDER BY sorter
LIMIT 10
fifeclub posted this at 15:45 — 29th November 2005.
He has: 688 posts
Joined: Feb 2001
Thanks very much. I'll try that as soon as I get a chance.
fifeclub posted this at 03:34 — 1st December 2005.
He has: 688 posts
Joined: Feb 2001
Thanks again. The code you gave worked perfectly in the simplified example code I posted. But I'm having unexpected trouble making it work with additional variables.
The good code that randomly picks 10 entries from the database is
<?phpSELECT *,RAND() AS sorter FROM alumni ORDER BY sorter LIMIT 10
?>
The code I'm trying to make that work with is
<?phpSELECT *, (TO_DAYS(NOW())-TO_DAYS(lastupdated)) AS days_stale FROM alumni LIMIT 10
?>
What this code does is determine how many days old an entry is, which is displayed in the results. If you need more of the code I can post it but I try to keep my posts very simple.
Here's the page I'm testing on. http://www.pstvalumni.com/directory/random.php As you keep hitting refresh you'll notice the top section does generate random results each time while the bottom just starts with the first entry. I need the list on the bottom to be 10 random entries rather than the first 10 in order.
Thanks again in advance.
Greg K posted this at 04:44 — 1st December 2005.
He has: 2,145 posts
Joined: Nov 2003
Just to make sure I am understanding you correctly, the bottom code sample you give is what you used to get the bottom table on the page. If so you still need to add the random field and the sort option:
SELECT *,(TO_DAYS(NOW())-TO_DAYS(lastupdated)) AS days_stale, RAND() AS sorter FROM alumni ORDER BY sorter LIMIT 10'That should work.
-Greg
PS. Not related, but I was wondering, what code/calculations are you using to generate the graph to show how recent someone is?
fifeclub posted this at 14:21 — 1st December 2005.
He has: 688 posts
Joined: Feb 2001
Awesome. I tried many variants of how to combine the elements of code #1 into code #2 but I guess I didn't hit that exact string. Thanks you so much!
Click here to see my long winded explanation.
http://www.pstvalumni.com/forum/viewtopic.php?p=1348#1348
which involves the following code to make it work (which somebody here probably helped me with years ago)
<?php
while($row = mysql_fetch_array($result)) {
if ($row['days_stale'] == '') $stalerating = \"Unknown\";
elseif ($row['days_stale'] < 30) $stalerating = \"New\";
elseif ($row['days_stale'] < 180) $stalerating = \"10\";
elseif ($row['days_stale'] < 360) $stalerating = \"9\";
elseif ($row['days_stale'] < 540) $stalerating = \"8\";
elseif ($row['days_stale'] < 720) $stalerating = \"7\";
elseif ($row['days_stale'] < 900) $stalerating = \"6\";
elseif ($row['days_stale'] < 1080) $stalerating = \"5\";
elseif ($row['days_stale'] < 1260) $stalerating = \"4\";
elseif ($row['days_stale'] < 1440) $stalerating = \"3\";
elseif ($row['days_stale'] < 1620) $stalerating = \"2\";
elseif ($row['days_stale'] < 1800) $stalerating = \"1\";
else $stalerating = \"0\";
?>
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.