Victor Walch Michnowicz

is a web application developer

Automating Database Results Array in CodeIgniter January 24th, 2011

The Setup

Let’s assume you have a table, users, with the following rows: id, first_name, middle_name, last_name, email_1, email_2, phone_home, phone_mobile, phone_work, address_1, address_2, zip, city, state, password, last_login, and salt.

The Problem

The problem comes when you query the database and try to get all this data into an array:

$query = $this->db->get('users');
		
$users = array();

if ($query->num_rows() > 0)
{
	// Loop through all rows
	foreach ($query->result() as $row)
	{
		$users[] = array(
			'id' => $row->id,
			'first_name' => $row->first_name,
			'last_name' => $row->last_name,
			// OK, how many times am I going to have to do this..?
		);
	}
}

Typing all that by hand is a real pain as you constantly have to look back to your database schema to see what you called an individual row. On top of that, the code can get real loooong. Do not worry, there is an alternative:

The Dumb Alternative

The dumb alternative approach uses another foreach loop to loop through each column of each result. It then pulls out the column name and uses that as the array key.

$query = $this->db->get('users');
		
$users = array();

if ($query->num_rows() > 0)
{
	// Counter
	$counter = 0;
	
	// Loop through all rows
	foreach ($query->result() as $row)
	{
		// Loop through all columns
		foreach ($row as $key=>$value)
		{
			$users[$counter][$key] = $value;
		}
		$counter++;
	}
}

The Better Alternative

The better alternative approach just uses CodeIgniter’s result_array() method. So… painfully… obvious…

$query = $this->db->get('users');

$users = array();

if ($query->num_rows() > 0)
{
	$users = $query->result_array();
}

blog comments powered by Disqus