user_guide_src/source/database/examples.rst


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

##################################
Database Quick Start: Example Code
##################################

The following page contains example code showing how the database class
is used. For complete details please read the individual pages
describing each function.

Initializing the Database Class
===============================

The following code loads and initializes the database class based on
your :doc:`configuration <configuration>` settings::

	$this->load->database();

Once loaded the class is ready to be used as described below.

Note: If all your pages require database access you can connect
automatically. See the :doc:`connecting <connecting>` page for details.

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 $row->email; }  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']

Testing for Results
===================

If you run queries that might **not** produce a result, you are
encouraged to test for a result first using the num_rows() function::

	 $query = $this->db->query("YOUR QUERY");  if ($query->num_rows() > 0) { Т Т Т foreach ($query->result() as $row) Т Т Т { Т Т Т Т Т Т echo $row->title; Т Т Т Т Т Т echo $row->name; Т Т Т Т Т Т echo $row->body; Т Т Т } }

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();

Active Record Query
===================

The :doc:`Active Record Pattern <active_record>` 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 :doc:`Active Record <active_record>` class contains a full
compliment of functions for working with data.

Active Record Insert
====================

::

	 $data = array( Т Т Т Т Т Т Т Т Т Т Т Т Т Т Т 'title' => $title, Т Т Т Т Т Т Т Т Т Т Т Т Т Т Т 'name' => $name, Т Т Т Т Т Т Т Т Т Т Т Т Т Т Т 'date' => $date Т Т Т Т Т Т Т Т Т Т Т Т );  $this->db->insert('mytable', $data);  // Produces: INSERT INTO mytable (title, name, date) VALUES ('{$title}', '{$name}', '{$date}')