mysql "random"

youragainyouragain
edited May 2006 in Internet & Media
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
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


mysql vers: 3.23.54

Comments

  • lightninlightnin
    edited May 2006
    how are the tips id'd? if they are int keys, generate a 'random' int with php and pull that id out. that way you'll have a farily more generic way of doing things.

    --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.
  • youragainyouragain
    edited May 2006
    there is an int id attached to each entry. right now, the are about 9 entries in the db. but the id's range from 2-23. as we add or subtract entries from the db, id's will grow and some will be absent.
    would i have to pull all the id's out of the db, then have php pick one of those somehow?
  • lightninlightnin
    edited May 2006
    to try and best recreate this scenario i went ahead and built a test table and tried out the rand() function.

    here's what i've got: 
    mysql> desc tips;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(3)      | YES  |     |         |       |
| tip   | varchar(10) | YES  |     |         |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

    with 9 entries

    the following query : 
    select id, tip from tips order by rand() limit 1;

    brings back random results, as i think you are looking for.
    here's my php test code:
    $link = mysql_connect('***.westga.edu', '***' '***')
         or die('Could not connect: '. mysql_error());
echo 'Connected!';

mysql_select_db('testProject') or die('Could not select database');

$query = 'select id, tip from tips order by rand() limit 1';

$result = mysql_query($query) or die('Could not query: ' . mysql_error());

while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   foreach ($line as $col_value) {
       echo "$col_value";
   }
   
}


mysql_free_result($result);

mysql_close($link);

    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.
  • youragainyouragain
    edited May 2006
    thanks for helping me get going in the right direction, but its been so long since i used code i dont remember too much. (not that i ever knew too much...) anyway, i am able to get this far:
    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...
  • lightninlightnin
    edited May 2006
    actually ignore my earlier advice about pulling out tips by id, that's just a little too complex i think. I'm actually getting very random results using both my code and yours, with selecting * or id, tip and also with a where clause. it's all pretty random. can I see the code to the whole page? i wonder if that first result is accidently being cached somehow. you're right, this should be very simple.
  • youragainyouragain
    edited May 2006
    here is a zip file of the page:
    http://www.gatewaycomgp.com/staging/displaytwotips.zip

    i think my lack of understanding some of the functions is whats blowing it...
  • lightninlightnin
    edited May 2006
    dude i have no idea what's goin on here. i took your displaytwotips.php file, put it on my server, put my db host and username/password in place of yours and here's my results:
     Here are tips from our database:

7 - test7

8 - test8

9 - test9

1 - test1

2 - test2

5 - test5

4 - test4

6 - test6

3 - test3

    refresh: 
     Here are tips from our database:

2 - test2

1 - test1

5 - test5

8 - test8

6 - test6

3 - test3

9 - test9

7 - test7

4 - test4

    refresh: 
     Here are tips from our database:

9 - test9

5 - test5

1 - test1

3 - test3

8 - test8

2 - test2

6 - test6

4 - test4

7 - test7

    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... :confused::confused::confused:
  • shwaipshwaip bluffin' with my muffin Icrontian
    edited May 2006
    you could try solution 3 from this page. It's supposed to be much more efficient too:

    http://akinas.com/pages/en/blog/mysql_random_row/
  • lightninlightnin
    edited May 2006
    solution 3 does work well, if you only pull back one result that is. and i get less dupes with it. i'd say definitely give it a shot.
  • youragainyouragain
    edited May 2006
    i tried to plug in 'solution 3' and get no results returned at all.
    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?
    mysql.jpg
  • lightninlightnin
    edited May 2006
    nah it's not your table. you get that resource bit because you are trying to echo a result set, which you can't do. this is how i implemented it in the code you gave us.
      29  echo 'Here are tips from our database: ';
    30
    31  $offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `tips` ");
    32  $offset_row = mysql_fetch_object( $offset_result );
    33  $offset = $offset_row->offset;
    34  $result = mysql_query( " SELECT * FROM `tips` LIMIT $offset, 1 " );
    35
    36    // Request the ID and text of all the tips
    37    //$result = @mysql_query('SELECT * FROM tips ORDER BY RAND()');
    38    if (!$result) {
    39      exit('Error performing query: ' .
    40          mysql_error() );
    41    }
    42

    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?
  • lightninlightnin
    edited May 2006
    this solution 
     $range_result = mysql_query( " SELECT MAX(`id`) AS max_id , MIN(`id`) AS min_id FROM `table` ");
$range_row = mysql_fetch_object( $range_result );
$random = mt_rand( $range_row->min_id , $range_row->max_id );
$result = mysql_query( " SELECT * FROM `table` WHERE `id` >= $random LIMIT 0,1 ");

    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.
  • youragainyouragain
    edited May 2006
    first off, thanks for all the help, sorry if im not getting something simple...

    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.

    echo ' Here are tips from our database: ';

	$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM tips ");
    $offset_row = mysql_fetch_object( $offset_result );
    $offset = $offset_row->offset;
    $result = mysql_query( " SELECT * FROM tips LIMIT $offset, 5 " );

  // Request the ID and text of all the tips
    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['id'];
    $tiptext = $row['tip'];
    echo $id .' - '. $tiptext ;
  }

    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; 
     // Connect to the database server
  $dbcnx = @mysql_connect($host, $user, $pass);
  if (!$dbcnx) {
    exit('Unable to connect to the ' .
        'database server at this time.');
  }

  // Select the tips database
  if (!@mysql_select_db('tips')) {
    exit('Unable to locate the tip ' .
        'database at this time.');
  }

   
    $product_id=$_GET['id'];
    $query="SELECT id FROM tips";
    $result=mysql_query($query);

 

     while ($row=mysql_fetch_row($result)) :
   echo $row[0] . "\n"; 
     endwhile;

    but im still at a complete loss right now.
  • lightninlightnin
    edited May 2006
    I attached it as a text file. it's your original code that i modified with solution 3's method for pulling a random. i tested it, it works. just rename it to something with a .php extension and fill in your database info and voila it should work.
  • youragainyouragain
    edited May 2006
    should...

    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?
  • youragainyouragain
    edited May 2006
    i determined it to be a server config issue by running the db and scripts on different servers perfectly. thanks for all the help yinz.
  • youragainyouragain
    edited May 2006
    without changing the server config, (which i cant do and im sure the paperwork would take forever anyway...) the solution was this:
    $result = @mysql_query("SELECT * FROM tips ORDER BY rand(".rand().") LIMIT 4");

    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
Sign In or Register to comment.