Codeigniter – Select Unique Random Row Every Day From MySQL

I am going to show how to select a row from MySQL database as randomly as possible, but not repeating it. So I am going to select unique row from the MySQL database using PHP based Codeigniter framework. The original tutorial could be found as selecting a random row every day from MySQL.

Prerequisites

Apache 2.4, PHP 7.4.3, Codeigniter 3.1.11, MySQL 8.0.17

Project Directory

It’s assumed that you have setup Apache, PHP and Codeigniter in Windows system.

Now I will create a project root directory called codeIgniter-mysql-unique-random-row-every-day the Apache server’s htdocs folder.

Now move all the directories and files from CodeIgniter framework into codeIgniter-mysql-unique-random-row-every-day directory.

I may not mention the project root directory in subsequent sections and I will assume that I am talking with respect to the project root directory.

MySQL table

I have kept my table with less columns than the original post. This table is about celebrities.

I have also stored some sample data to test the application right away once coding is done.

CREATE TABLE IF NOT EXISTS celebrity (
  id int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  full_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  created_date date COLLATE utf8mb4_unicode_ci NOT NULL,
  shown tinyint COLLATE utf8mb4_unicode_ci NOT NULL default 0,
  photo varchar(255) COLLATE utf8mb4_unicode_ci NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (full_name)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into celebrity(id,full_name,created_date,shown) values(1, 'Scarlett Johansson','2020-09-16',0);
insert into celebrity(id,full_name,created_date,shown) values(2, 'Madhuri Dixit','2020-09-19',0);
insert into celebrity(id,full_name,created_date,shown) values(3, 'Natalie Portman','2020-09-17',0);
insert into celebrity(id,full_name,created_date,shown) values(4, 'Aishwarya Rai','2020-09-19',0);
insert into celebrity(id,full_name,created_date,shown) values(5, 'Salma Hayek','2020-09-16',0);
insert into celebrity(id,full_name,created_date,shown) values(6, 'Divya Bharati','2020-09-19',0);

Autoload Configuration

You need some configurations, such as, auto-loading for helpers to avoid loading every time we need to use.

Modify application/config/autoload.php file for auto-loading libraries and helper functions.

This one time auto-loading gives flexibility to uniformly use the helpers and libraries anywhere throughout the application without loading repeatedly.

For my example I am auto-loading only database.

$autoload['libraries'] = array('database');

Database Configurations

Now we will configure database connection into application/config/database.php file. Please make sure to change according to your database settings.

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => 'root',
	'database' => 'roytuts',
        ...
	'char_set' => 'utf8mb4',
	'dbcollat' => 'utf8mb4_unicode_ci',
        ...
);

Model Class

You need to perform database operations to fetch celebrities data from database and display on the front-end or user interface (UI).

Create a file RandomRow_Model.php under applications/models folder to create the required model class.

<?php

defined('BASEPATH') OR exit('No direct script access allowed');
	
/**
* Description of RandomRow_Model
*
* @author https://roytuts.com
*/

class RandomRow_Model extends CI_Model {
	
	private $celebrity = 'celebrity';

	function pickTodayCeleb() {
		$tdate = date('Y-m-d');
		$this->db->select('id, full_name, photo')->where('created_date', $tdate)->from($this->celebrity, 1);
		$res = $this->db->get();
			
		if($res->num_rows < 1) {
			return $this->pickRandomCeleb();
		}
			
		$data['celeb'] = $res->row_array();
			
		return $data;
	}
	
	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('created_date', $ydate);
		$this->db->update($this->celebrity, $update);
			
		//Find a random entry that hasn't already been shown
		$this->db->select('id, full_name, photo');
		$this->db->where('shown !=', TRUE);//->from($this->celebrity);
		$res = $this->db->get($this->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('id, full_name, photo');
			$this->db->where('shown !=', TRUE);//->from($this->celebrity);
			$res = $this->db->get($this->celebrity);
				
			$num_rows = $res->num_rows > 1 ? $res->num_rows : 1;
			$rand = mt_rand(0, $num_rows);
			$res = $res->result_array();
				
			$data['celeb'] = $res[$rand];
				
			$update = array('created_date' => date('Y-m-d'));
			$this->db->where('id', $data['celeb']['id']);
			$this->db->update($this->celebrity, $update);

			return $data;
		}
		
		$num_rows = $res->num_rows > 1 ? $res->num_rows : 1;
		$rand = mt_rand(0, $num_rows);
		$res = $res->result_array();
			
		$data['celeb'] = $res[$rand];
			
		$update = array('created_date' => date('Y-m-d'));
		$this->db->where('id', $data['celeb']['id']);
		$this->db->update($this->celebrity, $update);
			
		return $data;
	}

}

In the above source code I have kept the function names unaltered from the original post.

pickTodayCeleb() function 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.

$tdate = date('Y-m-d'); – get today’s date and store it in a variable.

The below code:

$this->db->select('id, full_name, photo')->where('created_date', $tdate)->from($this->celebrity, 1);
		$res = $this->db->get();

Query the database for the information you 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 you don’t need to validate the input, since you are using PHP to provide the input though we can pretty much guarantee that the input will not be malicious.

If there is no result to the query it means that you are on a new day and you need to pick a new random celebrity. So you need to make a call to another function and return the result.

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

Finally in this function I assign the information to an array and return the array.

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

The next function picks the random celebrity. It’s called pickRandomCeleb().

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('created_date', $ydate);
	$this->db->update($this->celebrity, $update);
		
	//Find a random entry that hasn't already been shown
	$this->db->select('id, full_name, photo');
	$this->db->where('shown !=', TRUE);//->from($this->celebrity);
	$res = $this->db->get($this->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('id, full_name, photo');
		$this->db->where('shown !=', TRUE);//->from($this->celebrity);
		$res = $this->db->get($this->celebrity);
			
		$num_rows = $res->num_rows > 1 ? $res->num_rows : 1;
		$rand = mt_rand(0, $num_rows);
		$res = $res->result_array();
			
		$data['celeb'] = $res[$rand];
			
		$update = array('created_date' => date('Y-m-d'));
		$this->db->where('id', $data['celeb']['id']);
		$this->db->update($this->celebrity, $update);

		return $data;
	}
	
	$num_rows = $res->num_rows > 1 ? $res->num_rows : 1;
	$rand = mt_rand(0, $num_rows);
	$res = $res->result_array();
		
	$data['celeb'] = $res[$rand];
		
	$update = array('created_date' => date('Y-m-d'));
	$this->db->where('id', $data['celeb']['id']);
	$this->db->update($this->celebrity, $update);
		
	return $data;
}

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, I set a bool field to true if the celeb has been picked.

$ydate = strtotime('yesterday');
$ydate = date('Y-m-d', $ydate);
$update = array('shown' => 1); 
$this->db->where('created_date', $ydate);
$this->db->update($this->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.

$this->db->select('id, full_name, photo');
$this->db->where('shown !=', TRUE);
$res = $this->db->get($this->celebrity);

Again I use the active database connection to select a single random entry that doesn’t already have its 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('id, full_name, photo');
	$this->db->where('shown !=', TRUE);//->from($this->celebrity);
	$res = $this->db->get($this->celebrity);
		
	$num_rows = $res->num_rows > 1 ? $res->num_rows : 1;
	$rand = mt_rand(0, $num_rows);
	$res = $res->result_array();
		
	$data['celeb'] = $res[$rand];
		
	$update = array('created_date' => date('Y-m-d'));
	$this->db->where('id', $data['celeb']['id']);
	$this->db->update($this->celebrity, $update);

	return $data;
}

If there is no row found then all of them have been shown. So I reset them all back to 0 (false) and pick a random one to show, then return the result.

The last part is run if you 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 I 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 you picked so that it has today’s date in it.

$num_rows = $res->num_rows > 1 ? $res->num_rows : 1;
$rand = mt_rand(0, $num_rows);
$res = $res->result_array();
	
$data['celeb'] = $res[$rand];
	
$update = array('created_date' => date('Y-m-d'));
$this->db->where('id', $data['celeb']['id']);
$this->db->update($this->celebrity, $update);
	
return $data;

Controller Class

Create a controller file RandomRowController.php under application/controllers with the following source code.

The below controller class handles request and response for clients.

The controller class has a method called index() that displays random celebrity on the view or UI (User Interface).

In the below code snippets I am loading data from model class and passing to the view file random.php. I have used alias for model class as rm.

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class RandomRowController extends CI_Controller {

	function __construct() {
        parent::__construct();
        $this->load->model('RandomRow_Model' , 'rm');
    }
	
	public function index()	{
		$data['celeb'] = $this->rm->pickTodayCeleb();
		$this->load->view('random', $data);
	}
}

View File

The view file is responsible for displaying information on the web page.

So I will create a view files random.php under application/views folder.

I am using the following code snippets to display random celeb info on the view page:

<?php
	print_r($celeb);
?>

The whole code you can download from the Githut link given under source code section.

Configuring Route

We need to replace our default controller in application/config/routes.php file as shown below:

$route['default_controller'] = 'RandomRowController';

Testing the Application

Make sure your Apache HTTP server is up and running. Hitting the URL http://localhost/codeIgniter-multiple-thumbnails/ will display the following info on browser. On refreshing the browser you will see different celeb info randomly.

codeigniter select unique random row every day from mysql

Or

codeigniter select unique random row every day from mysql

Source Code

Download

Thanks for reading.

Leave a Comment