queries in database with example
Standard Query With Multiple Results (Object Version)
$query = $this->db->query('SELECT name, title, email FROM my_table'); foreach ($query->result as $row) { echo $row->title; echo $row->name; } echo 'Total Results: ' . $query->num_rows;
The above result function returns an array of objects. Example: $row->title
Standard Query With Multiple Results (Array Version)
$query = $this->db->query('SELECT name, title, email FROM my_table'); foreach ($query->result_array as $row) { echo $row['title']; echo $row['name']; echo $row['email']; }
The above result_array function returns an array of standard array indexes. Example: $row[‘title’]
Standard Query With Single Result
$query = $this->db->query('SELECT name FROM my_table LIMIT 1'); $row = $query->row; echo $row->name;
The above row function returns an object. Example: $row->name
Standard Query With Single Result (Array version)
$query = $this->db->query('SELECT name FROM my_table LIMIT 1'); $row = $query->row_array; echo $row['name'];
The above row_array function returns an array. Example: $row[‘name’]
Standard Insert
$sql = "INSERT INTO mytable (title, name) VALUES (".$this->db->escape($title).", ".$this->db->escape($name).")"; $this->db->query($sql); echo $this->db->affected_rows;
Query Builder Query
The Query Builder Pattern gives you a simplified means of retrieving data:
$query = $this->db->get('table_name'); foreach ($query->result as $row) { echo $row->title; }
The above get function retrieves all the results from the supplied table. The Query Builder class contains a full compliment of functions for working with data.
Query Builder Insert
$data = array( 'title' => $title, 'name' => $name, 'date' => $date ); $this->db->insert('mytable', $data);