mysql "random"
i have a "random" tips database that we can add and delete tips from as we desire. the idea of the db is that it will randomly return a tip, (or two) from the db in the sidebar of each page visit. the problem is that 99 out of 100 refreshes, the same tip is at the top of the list... soooo... if i set it to echo only one result, 99 times out of 100, its the same one...
is there a better way to do this?
hers a sample of what the code looks like:
php
mysql vers: 3.23.54
is there a better way to do this?
hers a sample of what the code looks like:
php
echo ' Here are 2 tips from our database: ';
// Request the ID and text of all the tips
$result = @mysql_fetch_row(mysql_query("SELECT * FROM tips ORDER BY RAND() limit 2"));
if (!$result) {
exit('Error performing query: ' .
mysql_error());
}
// Display the text of each tip in a paragraph
while ($row = mysql_fetch_array($result)) {
$id = $row;
$tiptext = $row;
echo $tiptext ;
}
/php// Request the ID and text of all the tips
$result = @mysql_fetch_row(mysql_query("SELECT * FROM tips ORDER BY RAND() limit 2"));
if (!$result) {
exit('Error performing query: ' .
mysql_error());
}
// Display the text of each tip in a paragraph
while ($row = mysql_fetch_array($result)) {
$id = $row;
$tiptext = $row;
echo $tiptext ;
}
mysql vers: 3.23.54
0
Comments
--addition
from the mysql docs :
mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d -> ORDER BY RAND() LIMIT 1000;
Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.
RAND() is not meant to be a perfect random generator, but instead is a fast way to generate ad hoc random numbers which is portable between platforms for the same MySQL version.
you have no where clasue, therefore rand() will bring back the same tips almost every time. throw a where clause in there if you can, it might help with more 'randomness'. if i were doing this though, i'd do my original key idea if they are numeric keys.
would i have to pull all the id's out of the db, then have php pick one of those somehow?
here's what i've got:
with 9 entries
the following query :
brings back random results, as i think you are looking for.
here's my php test code:
it works quite well for me thus far... tip2, tip7, tip3, tip1
maybe try more data?
**shrugs**
--edit
oh ps you are right, i wasnt thinking... in order to do my idea you'd have to know the range of ids. you could pull each id and choose a random one from that array but this way seems to be working just fine for me anyway.
http://www.gatewaycomgp.com/staging/displaytwotips.php
and i have obviously also been able to pull up the id's alone, the question is this - can i pull up the id's in one script, use an include to pull a random number from that file and echo out the corresponding text?
it seems like a basic simple thing to do, i am just not getting it...
http://www.gatewaycomgp.com/staging/displaytwotips.zip
i think my lack of understanding some of the functions is whats blowing it...
refresh:
refresh:
i really don't know what to say, the code looks just fine boss. it looks like your code should work just fine. i can't figure out why your first resulting tip is almost always the same...
http://akinas.com/pages/en/blog/mysql_random_row/
i think i am printing the right thing at the end of the code...
echo $result;
(that actually returns "Resource id #4")
maybe my db table is set up strange?
note i commented out the original query. alos note that this solution will only work when bringing back just *one* result from the db, no more.
in fact, it looks like all of those solutions only pull back one result... and sol 4 probably wont even work on mysql 3.23... why such an old version anyway?
from the stuff shwaip found is more in tune with what i was saying about fetching ids and then querying from that set. if you wanna pull back multiple tips, this might be the better route.
i have no idea why the server uses such an old version, prob cuz its a shared one and i have no control over it. even if i did, im pretty sure i wouldnt know what to do.
this code (if i am using it right) isnt working, it returns the exact same list everytime.
i think im getting more and more lost the more i think about it.
i did accomplish this though;
http://www.gatewaycomgp.com/staging/tips_id.php
which is accomplished by this;
but im still at a complete loss right now.
http://www.gatewaycomgp.com/staging/randoms.php
i have no idea what the deal is... i am completely lost. i get the same result everytime. is it a server setting that is being screwey? i looked and its php vers 5 on the server. its gotta be a server setting right? is it because the id's in the db are non-sequential?
it uses the PHP random number generator to set the speed of the MySQL RAND() instead of the system time.
- solution by tim shoda from www.jakprints.com