TECHiE TALKS

Code Igniter is one of the best PHP framework available today.  It is free to download and user guide can be found together with the package.

In Code Igniter the class that has the database queries or the methods that deals with getting the data on the database is placed inside the MODEL folder.  Just remember the “D” which tells us that Database methods are placed in this folder as what Jeffrey Way said in his tutorials.

Here are different Methods to query the database which is based in Nettuts+ tutorials.

Inside my Model folder I created a file Mydb.php, so the name of my class should be the same as the filename I created.

< ?php
class Mydb extends Model {

	function getData() {
		$this->db->select('title,content'); #where title and content are fields
		$sql = $this->db->get('data');  #data is a database table
		if($sql->num_rows() > 0) {
			foreach($sql->result() as $row) {
				$data[] = $row;
			}
			return $data;
		}
        }

}

?>

In this method the line $this->db->select(‘title,content’); is using code igniter “active records” where I stated the fields I need but if you want to get all the data just remove this line. The line $sql = $this->db->get(‘data’); tells the name of the table where I want to get the fields title and content. To make sure I have something to work on the line $sql->num_rows() > 0 is placed and the data is placed inside the array $data[].

To be more specific in using code igniter active records,

< ?php
class Mydb extends Model {

	function getData() {
		$this->db->select('title,content');
		$this->db->from('data');
		$this->db->where('id',1);
		$sql = $this->db->get();
		if($sql->num_rows() > 0) {
			foreach($sql->result() as $row) {
				$data[] = $row;
			}
			return $data;
		}
        }
}

?>

Another way of querying is:

< ?php

class Mydb extends Model {

	function getData() {
		$sql = $this->db->query('SELECT * FROM data');
		if($sql->num_rows() > 0) {
			foreach($sql->result() as $row) {
				$data[] = $row;
			}
			return $data;
		}
	}

}

?>

The line $sql = $this->db->query(‘SELECT * FROM data’); is simply putting the query inside the predefined method in Code Igniter $this->db->query(‘[query]‘). Still the data returned is an array.

To query with a user input,

< ?php

class Mydb extends Model {

	function getData() {
		$sql = "select * from data where id=?";
		$val = $this->db->query($sql, 2);
		if($val->num_rows() > 0) {
			foreach($val->result() as $row) {
				$data[] = $row;
			}
			return $data;
		}
	}

}

?>

The line $sql = “select * from data where id=?”; is our database query where ? is any value passed to it. The line $val = $this->db->query($sql, 2); is where we execute our query and the value passed to “?” is 2. But what if we have more than 1 condition in our query,

< ?php

class Mydb extends Model {

	function getData() {
		$sql = "select * from data where id=? and author = ?";
		$val = $this->db->query($sql, array(1, 'Allan'));
		if($val->num_rows() > 0) {
			foreach($val->result() as $row) {
				$data[] = $row;
			}
			return $data;
		}
	}

}

?>

If we have more than one condition in our query we just use an array stating the value for our conditions.