Codeigniter Tutorial: Selecting A Random Row Every Day From MySQL & Not Repeating It

by Paul Robinson on 09.03.2008 | 2 comments

So I am doing a little project that I am thinking of implementing over at Celeb O Rama. Basically it’s a poll, but one of [...]

So I am doing a little project that I am thinking of implementing over at Celeb O Rama. Basically it’s a poll, but one of the features the poll has is that it will change randomly every day. I have found lots, and lots of people asking how to do daily polls or pictures so here is how. You will need a MySQL database & Codeigniter already set-up. I am only going to show you how to do the main functions, the layout and stuff is up to you.

The MySQL Table

So my table will obviously be about celebrities, but the basic structure is the same. Here is the code to make it if you want to use mine:

CREATE TABLE IF NOT EXISTS `celebrity` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `hot` int(11) NOT NULL default '0',
  `not` int(11) NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `shown` tinyint(1) NOT NULL default '0',
  `photo` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL default 'Image Copyright To Respective Owner',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM;

That should give you a table identical to the one I will be using in this tutorial. Fill it with a few random celebrities or some data. The photo field holds just the filename of a photo you want to be shown. If you don’t want a photo remove that field from the code.

The Model

So I prefer to use Codeigniters model system to hold my database info. You don’t have to, but it is sensible to do so. This tutorial is not meant to be in depth about how to use models so if you don’t know yet check this out.

First we make a function to pick today’s celebrity. I called this pickTodayCeleb(). Not very original, but it serves it’s purpose. Here is the code:

function pickTodayCeleb()
{
	$tdate = date('Y-m-d');
	$this->db->select('celebrity.id, celebrity.name, celebrity.hot, celebrity.not, celebrity.photo, celebrity.title')->where('celebrity.date', $tdate)->from('celebrity', '1');
	$res = $this->db->get();

	if($res->num_rows < 1)
	{
		return $this->pickRandomCeleb();
	}

	$data['celeb'] = $res->row_array();

	return $data;
}

So this function does exactly what it says. It picks a celebrity marked with today’s date. If there is more than one, which shouldn’t happen but we need to be prepared just in case, we limit the results to one.

Here is a quick run down of the code.

$tdate = date('Y-m-d');
</pre>
Get today's date and store it in a variable.
<pre lang="php">
$this->db->select('celebrity.id, celebrity.name, celebrity.hot, celebrity.not, celebrity.photo, celebrity.title')->where('celebrity.date', $tdate)->from('celebrity', '1');
$res = $this->db->get();

Query the database for the info we want, and only get results where the date matches today’s date. Store the results in a variable. Using the active database class that Codeigniter includes means we don’t need to validate the input, since we are using PHP to provide the input though we can pretty much guarantee that the input will not be malicious.

if($res->num_rows < 1)
{
	return $this->pickRandomCeleb();
}

If there are no results to the query it means that we are on a new day and we need to pick a new random celebrity. So we make a call to another function I will get to in a minute and return the result.

$data['celeb'] = $res->row_array();
return $data;

Finally in this function we assign the information to an array and return the array. This part of the code will only happen if the return in the above piece doesn’t run, since return ends the function.

Ok, so the next function picks the random celebrity. It’s called pickRandomCeleb(). Here’s the full code first:

function pickRandomCeleb()
{
	//Find yesterdays entry and change to true
	$ydate = strtotime('yesterday');
	$ydate = date('Y-m-d', $ydate);
	$update = array('shown' => 1);
	$this->db->where('celebrity.date', $ydate);
	$this->db->update('celebrity', $update);

	//Find a random entry that hasn't already been shown
	$this->db->select('celebrity.id, celebrity.name, celebrity.hot, celebrity.not, celebrity.photo, celebrity.title');
	$this->db->where('shown !=', TRUE)->from('celebrity');
	$res = $this->db->get('celebrity');

	if($res->num_rows < 1)
	{
		//all rows have been shown. Reset all shown values to false and run the randomizer again.
		$update = array('shown' => 0);
		$this->db->update('celebrity', $update);

		//Find a random entry that hasn't already been shown
		$this->db->select('celebrity.id, celebrity.name, celebrity.hot, celebrity.not, celebrity.photo, celebrity.title');
		$this->db->where('shown !=', TRUE)->from('celebrity');
		$res = $this->db->get('celebrity');

		$rand = mt_rand(0, $res->num_rows-1);
		$res = $res->result_array();

		$data['celeb'] = $res[$rand];

		$update = array('date' => date('Y-m-d'));
		$this->db->where('celebrity.id', $data['celeb']['id']);
		$this->db->update('celebrity', $update);

		return $data;

	}

	$rand = mt_rand(0, $res->num_rows-1);
	$res = $res->result_array();

	$data['celeb'] = $res[$rand];

	$update = array('date' => date('Y-m-d'));
	$this->db->where('celebrity.id', $data['celeb']['id']);
	$this->db->update('celebrity', $update);

	return $data;
}

Now in the start of the code I have a little custom bit. I don’t want the same celebrity to be picked again until all have been picked at least once. So The first part of code does exactly that, we set a bool field to true if the celeb has been picked. I’ll explain the code and hopefully it’ll make more sense.

$ydate = strtotime('yesterday');
$ydate = date('Y-m-d', $ydate);
$update = array('shown' => 1);
$this->db->where('celebrity.date', $ydate);
$this->db->update('celebrity', $update);

Get yesterday’s date using strtotime() which can accept human perceived dates such as ‘yesterday’ and ‘today’. Get the MySQL formatted date using the date() function and then update shown to 1(true) where the date matches.

//Find a random entry that hasn't already been shown
$this->db->select('celebrity.id, celebrity.name, celebrity.hot, celebrity.not, celebrity.photo, celebrity.title');
$this->db->where('shown !=', TRUE)->from('celebrity');
$res = $this->db->get('celebrity');

Again we use the active database connection to select a single random entry that doesn’t already have it’s shown value set to true(1).

if($res->num_rows < 1)
{
	//all rows have been shown. Reset all shown values to false and run the randomizer again.
	$update = array('shown' => 0);
	$this->db->update('celebrity', $update);

	//Find a random entry that hasn't already been shown
	$this->db->select('celebrity.id, celebrity.name, celebrity.hot, celebrity.not, celebrity.photo, celebrity.title');
	$this->db->where('shown !=', TRUE)->from('celebrity');
	$res = $this->db->get('celebrity');

	$rand = mt_rand(0, $res->num_rows-1);
	$res = $res->result_array();

	$data['celeb'] = $res[$rand];

	$update = array('date' => date('Y-m-d'));
	$this->db->where('celebrity.id', $data['celeb']['id']);
	$this->db->update('celebrity', $update);

	return $data;

}

If there are no rows then all of them have been shown. So we reset them all back to 0(false) and pick a random one to show, then return the info.

$rand = mt_rand(0, $res->num_rows-1);
$res = $res->result_array();

$data['celeb'] = $res[$rand];

$update = array('date' => date('Y-m-d'));
$this->db->where('celebrity.id', $data['celeb']['id']);
$this->db->update('celebrity', $update);

return $data;

This last bit runs if we do get a row from the original query. All it really does is pick a number between 0 and the max number of rows returned minus one. Then we assign the result array to a variable. Then use the random number to pick a number in the array and assign it to the variable $data['celeb']. The last bit just updates the row we picked so that it has today’s date in it.

That’s It

You should be able to use the basis of this Codeigniter code to make a daily randomizer for your project. If you have any questions about the code or anything to add please drop me a comment.

Also I am currently looking for paid work, if anyone has any coding jobs they would like doing then contact me via the contact form. Please remember I don’t do designing, just coding.