Selecting random mysql row

[worldcom]

Hey all,

I'm making this little script to rotate banners.

I'm having a brainurisim right now trying to think how to grab one random row from the database.

Here's what I have:

<?php
require('members/connect.php');
$query = "SELECT * FROM banners WHERE banner_size=7";
$result = mysqli_query($dbc, $query);
$banner = mysqli_fetch_array($result);
$num_rows = mysqli_num_rows($result);
echo "$num_rows - ". rand(1, $num_rows) ."<br>"; // This is just to check the numbers
?>

So for example, if the random number is 2, how can I select the 2nd row from the query database.

I have various sizes of banners in the database, so I'm only selecting one particular size ( banner_size= 7 ).

 

I know if I just use the $banner[array], it will just return the first one found.

I have a feeling this is going to be too simple and I will have a red face

 

[timothyarden]

Create an ID column in the table and auto increment the go SELECT WHERE ID == RANDOM NUMBER

[worldcom]

I do have a column id. But I can't let it select a random number because the auto_increment numbers may not be in order.

eg 3 certain size banners can be id's 2,10, 15

 

What I need is, the random number to select the id 2 or 10 or 15 but the random number is between 1 and 3.

 

Hope I'm explaining it right.

Edited February 28, 2013 by worldcom

[DavidAM]

Create an ID column in the table and auto increment the go SELECT WHERE ID == RANDOM NUMBER

That is not going to work, since there is a WHERE clause in the query, the ID's will not likely be consecutive in the results.

 

@worldcom You can use the LIMIT clause to get a specific row: LIMIT $offset, 1. But first a couple of things about your code.

 

1) Do not use SELECT * unless you need every column in the table. Specify the columns you want, instead. This documents your code and saves resources.

 

2) Do not SELECT data to find out how many rows are in the table. Again it wastes resources. Use the COUNT() function instead

 

require('members/connect.php');

// Get a count of the banners available
$query = "SELECT COUNT(*) AS RowCount FROM banners WHERE banner_size=7";
$result = mysqli_query($dbc, $query);
$banner = mysqli_fetch_array($result);
$num_rows = $banner['RowCount'];
mysqli_free_result($result);

// Pick a random number and subtract 1 (we want the OFFSET)
$randomOffset = rand(1, $num_rows) - 1;

// Select the banner
$query = "SELECT * FROM banners WHERE banner_size = 7 LIMIT $randomOffset, 1";
$result = mysqli_query($dbc, $query);
$banner = mysqli_fetch_array($result);
mysqli_free_resullt($result);

[worldcom]

Thanks DavidAM,

 

I got it working but I'm gonna use your code.

I'll just post how I got my code working.

<?php
require('members/connect.php');
$query = "SELECT * FROM banners WHERE banner_size=7";
$result = mysqli_query($dbc, $query);
$random_banner = rand(1, mysqli_num_rows($result));
$i = 0;
while( $banner = mysqli_fetch_array($result) ){
	$i++;
	if( $i == $random_banner ){
?>
<a href="http://<?php echo $banner[2]; ?>" all my banner stuff here </a>
<?php
	}
}
?>

This works, but I am taking your advice.

My new code here is very clunky.

Edited February 28, 2013 by worldcom

[worldcom]

OK my new and improved code.

 

<?php
require('members/connect.php');

// Get a count of the banners available
$query = "SELECT COUNT(*) AS RowCount FROM banners WHERE banner_size=7";
$result = mysqli_query($dbc, $query);
$banner = mysqli_fetch_array($result);
$num_rows = $banner['RowCount'];
mysqli_free_result($result);

// Pick a random number and subtract 1 (we want the OFFSET)
$randomOffset = rand(1, $num_rows) - 1;

// Select the banner
$query = "SELECT http_url, onMouseDown_site, title, banner_url FROM banners WHERE banner_size = 7 LIMIT $randomOffset, 1";
$result = mysqli_query($dbc, $query);
$banner = mysqli_fetch_array($result);
mysqli_free_result($result);
?>
<a href="http://<?php echo $banner[0]; ?>">. . . My banner stuff here . . .</a>

Works great. Thanks for your help.

[Psycho]

No need to use PHP to get the random value. That can be done with MySQL in the first query

 

 

<?php
require('members/connect.php');

// Get a count of the banners available
$query = "SELECT FLOOR(COUNT(*) * RAND()) AS RandOffset FROM banners WHERE banner_size=7";
$result = mysqli_query($dbc, $query);
$banner = mysqli_fetch_array($result);
mysqli_free_result($result);

// Select the banner
$query = "SELECT http_url, onMouseDown_site, title, banner_url FROM banners WHERE banner_size = 7 LIMIT {$banner['RandOffset']}, 1";
$result = mysqli_query($dbc, $query);
$banner = mysqli_fetch_array($result);
mysqli_free_result($result);

?>
<a href="http://<?php echo $banner[0]; ?>">. . . My banner stuff here . . .</a>

[timothyarden]

From before DavidAM:

Create an ID column in the table and auto increment the go SELECT WHERE ID == RANDOM NUMBER

That is not going to work, since there is a WHERE clause in the query, the ID's will not likely be consecutive in the results.

 

If its autoincrementing that value they will be consecutive - wont it?
 

[Psycho]

If its autoincrementing that value they will be consecutive - wont it

 

I don't think you understand. The OP wants a random record from the list of records "WHERE banner_size = 7". So, let's say you have these records in your DB

 

id | name | banner_size
 1   A      6
 2   B      7
 3   C      6
 4   D      7
 5   E      6
 6   F      7
 7   G      6

 

The result should be a random record from item B, D, or F. Using the logic of "SELECT WHERE ID == RANDOM NUMBER" would grab ANY record from the table and not just one where banner_size = 7.

[worldcom]

The result should be a random record from item B, D, or F. Using the logic of "SELECT WHERE ID == RANDOM NUMBER" would grab ANY record from the table and not just one where banner_size = 7.

 

Also, what if I deleted a banner or 2 and there was NO id for that random selection.

 

Thanks for the mySql solution Psycho.

All the help has been great.

[Psycho]

Also, what if I deleted a banner or 2 and there was NO id for that random selection.

 

Not sure exactly what you mean. The query is not selecting by ID. It is selecting a record randomly using LIMIT (i.e. the position in the result set). So deleting records does not mess up the logic for selecting a random record. As long as there are ANY records that match the set banner_size you will get a result. If there are no records matching the banner_size then you will get an empty result set.

 

The only real problem I see is a race condition issue. If a banner was deleted between the first query and the second query AND the random position selected was for the last record, then the second query would be trying to get a record in a position that is 1 higher than the ones that exist (resulting in an empty result set). But, unless the site is going to have a very high concurrent usage and records will be deleted regularly I think this is a very, very low probability of happening. Plus, the more records that exist the lower probability it would ever occur.

Edited March 1, 2013 by Psycho

[worldcom]

Sorry wasn't talking about your code. It works great.

It was this quote I was referring too:

 

If its autoincrementing that value they will be consecutive - wont it?

[Psycho]

 

Sorry wasn't talking about your code. It works great.

It was this quote I was referring too:

 

If its autoincrementing that value they will be consecutive - wont it?

 

Right, but your requirement to only pick a random value based upon a specific condition completely negates the method he was suggesting. But, yes, even if the intent was to get any random record, just using the max ID (or count of records) and selecting a random value out of that as an ID is not useful since IDs can be skipped - either intentionally when created or by records being deleted.

 

But, that's not to say that approach is completely without merit. You could, for example, get the highest ID value and get a random value between 0 and 1 less than that highest ID value.and do a query similar to what we did above

 

SELECT *
FROM table
WHERE id > $randID
LIMIT 1