Random unique row from MySQL

[phpnewbieee]

Hi,

 

First off:

I got a table with two columns and 100.000 rows.

Some of the rows are duplicates (allowing me to "weight" the chance of some rows being hit the first time the script runs)

Allright. This i what I want to do:

 

 

 

When the user clicks a button:

1. Generate a random selected row from the table (this I allready accomplished using this code:

 

<?php

  //CODE FROM WWW.GREGGDEV.COM

  function random_row($table, $column) {

      $max_sql = "SELECT max(" . $column . ") 

                  AS max_id

                  FROM " . $table;

      $max_row = mysql_fetch_array(mysql_query($max_sql));

      $random_number = mt_rand(1, $max_row['max_id']);

      $random_sql = "SELECT * FROM " . $table . "

                     WHERE " . $column . " >= " . $random_number . " 

                     ORDER BY " . $column . " ASC

                     LIMIT 1";

      $random_row = mysql_fetch_row(mysql_query($random_sql));

      if (!is_array($random_row)) {

          $random_sql = "SELECT * FROM " . $table . "

                         WHERE " . $column . " < " . $random_number . " 

                         ORDER BY " . $column . " DESC

                         LIMIT 1";

          $random_row = mysql_fetch_row(mysql_query($random_sql));

      }

      return $random_row;

  }

  

  //USAGE

$randomdata = random_row('MYTABLE', 'MYCOLUMN');

echo $randomdata[2]; // Where [2] is the data I want to extract.


?>

 

This script works fine. The problem is that I want to exclude the results that have allready been shown. With me?

Therefore, since I have duplicate rows of certain data, I need to exclude all the rows that are the same.

 

I have a couple of solutions, but I can't seem to fit it into this script. (I will not use the ORDER_BY_RAND() query; it'll be too slow for a table with 100.000 rows)

 

 

SO:

Here's ideas I've come up with that might work, but because of my limited experience with PHP and MySQL, haven't been able to accomplish.

 

1) Insert a "temporary" column in the table which is filled with 1 and 0's where 1 indicates that the row has allready been taken? (If possible)

2) Use PHP sessions to store the previously generated rows?

3) Create a temporary table, which fills up with the allready generated rows by the random script?

4) Create a unique txt-file (e.g: _USERSIPADRESS__.txt) which can be used to save and extract data from the random script? (fopen, fwrite and so fourth)

 

 

 

I hope you understand what I'm trying to do

Any suggestions would be highly appreciated.

 

Thank you.

[Zane]

1) Insert a "temporary" column in the table which is filled with 1 and 0's where 1 indicates that the row has allready been taken? (If possible)

This seems like your best bet.  Though the column wouldn't be temporary and I would do more than fill it with just 1s and 0s.  I would perhaps call this column 'threshold' and increment it everytime you select it.  Then you could simply check the threshold within your query.

[phpnewbieee]

1) Insert a "temporary" column in the table which is filled with 1 and 0's where 1 indicates that the row has allready been taken? (If possible)

This seems like your best bet.  Though the column wouldn't be temporary and I would do more than fill it with just 1s and 0s.  I would perhaps call this column 'threshold' and increment it everytime you select it.  Then you could simply check the threshold within your query.

 

Thanks for your reply.

 

The problem is that I need to be sure that the column or whatever is unique for every person that runs the script. The reason I say temporary is because I want each user to have its own unique column created when the script runs, and then delete it when the visitor wishes to stay at the current random data.

 

Simple interface sketch:

 

[button 1 - Generate new random row]              [button 2 - Choose this data]

 

When button 2 is pressed, the column should be removed. There should be no "old information" lying around the next time the script is executed from that current visitor.

 

Any solutions?

 

 

[Zane]

The problem is that I want to exclude the results that have allready been shown. With me?

Therefore, since I have duplicate rows of certain data, I need to exclude all the rows that are the same.

Ok, so you've got thousands of rows already.. you've got the random part finished.  You just don't want duplicates to appear.... or rather, you only want the row to appear once in it's lifetime.  Am I on the right tract?

[button 1 - Generate new random row]              [button 2 - Choose this data]

 

When button 2 is pressed, the column should be removed. There should be no "old information" lying around the next time the script is executed from that current visitor.

Creating a column and deleting it over and over again could be cumbersome and it's probably the wrong way to go.  You could just as easily change that columns value to 0 and be done with it.

 

I don't quite understand what you mean by "old information floating around".... do you want to delete this data of yours? I'm confused.  If it's duplicates that is bothering you right now, then you'll have to figure out a way to get rid of them entirely, through a mass SQL statement or just manually.

[phpnewbieee]

The problem is that I want to exclude the results that have allready been shown. With me?

Therefore, since I have duplicate rows of certain data, I need to exclude all the rows that are the same.

Ok, so you've got thousands of rows already.. you've got the random part finished.  You just don't want duplicates to appear.... or rather, you only want the row to appear once in it's lifetime.  Am I on the right tract?

[button 1 - Generate new random row]              [button 2 - Choose this data]

 

When button 2 is pressed, the column should be removed. There should be no "old information" lying around the next time the script is executed from that current visitor.

Creating a column and deleting it over and over again could be cumbersome and it's probably the wrong way to go.  You could just as easily change that columns value to 0 and be done with it.

 

I don't quite understand what you mean by "old information floating around".... do you want to delete this data of yours? I'm confused.  If it's duplicates that is bothering you right now, then you'll have to figure out a way to get rid of them entirely, through a mass SQL statement or just manually.

 

Sorry, I think I was a bit unclear. Say that a viewer navigates from the page and comes back later. If thats the case, the script should run from fresh without any old information about which previous data has been viewed from the database.

 

Example: Viewer1 runs the script. Output:

 

1st. time result: "Banana"

2nd time result: "Apple"

3rd. time result: "Pear".

 

I need to find a way to make sure that this viewer gets a new result each time the script runs. But I also need to make sure that I don't make a MySQL-table to store previous results for "viewer 1" that can interfere with the results of the other viewers. Say that viewer 1 gets "banana" randomly. This information needs to be stored somewhere to make sure it won't pop up again, when a new random output is generatated, right? If I  were to store this result in new table (let's call it taken) I would have to run the script towards table "taken" and exclude the rows of it with every new hit on the "generate new random"- button.

 

However, this table "taken" has to be unique for each user so that it won't interfere with the results of others

 

English is not my first language, so its kind of difficult to explain, but I hope you understand what I'm trying to do.

 

Run a script to pick a random row from the database, and exclude the previous results each time the script is executed again

[phpnewbieee]

I got the random row code from http://www.greggdev.com/web/articles.php?id=6

 

He suggests the following:

With this, it is useful to have an tinyint(1) column in the table that is updated as the row is selected so that it will not be chosen again. Since the function uses greater than and less than, it would not return an empty result unless all rows had been updated at which point the script could be ended. For such a script this also allows many updaters to be running at once selecting random rows that will not be selected again because the tinyint will be changed upon selection.

 

I don't understand how to do this.

[Insecure]

How about making another table that has a couple columns like:

 

ID

IP

Data Shown

 

Then you insert the ip of the user and which data is shown, then as you pick a "new/random" one, check to see if it is in this table, if it is, pick a new one...?

[tsm1248]

There is a sql command to select a random row(s) ORDER BY RAND() and you could limit with u guessed it LIMIT but keep in mind the idea of grabbing a random row in php rapes performance; not in a good way.

[phpnewbieee]

How about making another table that has a couple columns like:

 

ID

IP

Data Shown

 

Then you insert the ip of the user and which data is shown, then as you pick a "new/random" one, check to see if it is in this table, if it is, pick a new one...?

 

I'll try to do something like this, I'll post a the solutions when I'm done

[phpnewbieee]

There is a sql command to select a random row(s) ORDER BY RAND() and you could limit with u guessed it LIMIT but keep in mind the idea of grabbing a random row in php rapes performance; not in a good way.

 

I know of the order by rand() command, but as you say; it is too slow..

[phpnewbieee]

Ok. Unique data now works, but sometimes I get a blank result and have to refresh a couple of times to actually get a result. Can someone please take a quick look at this code for me?

If all the ID's have been taken I want the script to truncate table "visited" and start to pick random rows over again...

<?php

include 'connect.php';

      $max_sql = "SELECT max(ID) 

                  AS max_id

                  FROM pending";

      $max_row = mysql_fetch_array(mysql_query($max_sql));

      $random_number = mt_rand(1, $max_row['max_id']);  

$res = mysql_query('select count(*) from visited where id = ' . $random_number) or die();
$row = mysql_fetch_row($res);
if ($row[0] > 0) // If random row exist in table visited
{
	while ($row> 0) 
	{
	mt_srand ((double)microtime() * 100000);
	$unique = mt_srand(1,$max_row['max_id']);
	return $unique;
	$random_number = generateagain($unique);		
	}

$random_sql = "SELECT * FROM pending

WHERE ID >= " . $random_number . "

ORDER BY ID ASC

LIMIT 1";

      $random_row = mysql_fetch_row(mysql_query($random_sql));

      if (!is_array($random_row)) {

          $random_sql = "SELECT * FROM pending

                         WHERE ID < " . $random_number . "

                         ORDER BY ID DESC

                         LIMIT 1";

          $random_row = mysql_fetch_row(mysql_query($random_sql));

      }
}
else // random ID doesn't exist in table visited
{
     $random_sql = "SELECT * FROM pending

                     WHERE ID >= " . $random_number . "

                     ORDER BY ID ASC

                     LIMIT 1";

      $random_row = mysql_fetch_row(mysql_query($random_sql));

      if (!is_array($random_row)) {

          $random_sql = "SELECT * FROM pending

                         WHERE ID < " . $random_number . "

                         ORDER BY ID DESC

                         LIMIT 1";

          $random_row = mysql_fetch_row(mysql_query($random_sql));

      }
}	  
  


  
 $randomadress = $random_row[2];
 $randomid = $random_row[5];



$query = "INSERT INTO visited (ID, sites) VALUES ('$randomid', '$randomadress')";             // Insert taken into visited table
$result = mysql_query($query) or die (mysql_error());

echo $randomadress;



?>