1Prepared statements
2===================
3
4`Prepared statements <http://en.wikipedia.org/wiki/Prepared_statement>`_ help
5you in many cases to avoid avoid mysql injections and helps increasing security
6of your queries by separating the SQL logic from the data being supplied.
7
8`DALMP\\Database </en/latest/database.html>`_ by default tries to determine the
9type of the data supplied, so you can just focus on your query without needing
10to specify the type of data, If you preffer you can manually specify the type of
11the data. The following table, show the characters which specify the types for
12the corresponding bind variables:
13
14
15+-----------+--------------------------------------------------------------+
16| Character | Description                                                  |
17+===========+==============================================================+
18| i         | corresponding variable has type integer                      |
19+-----------+--------------------------------------------------------------+
20| d         | corresponding variable has type double                       |
21+-----------+--------------------------------------------------------------+
22| s         | corresponding variable has type string                       |
23+-----------+--------------------------------------------------------------+
24| b         | corresponding variable is a blob and will be sent in packets |
25+-----------+--------------------------------------------------------------+
26
27
28.. seealso::
29
30   Method `prepare </en/latest/database/Prepare.html>`_, & `mysqli_stmt_bind_param <http://www.php.net/manual/en/mysqli-stmt.bind-param.php>`_.
31
32To use "Prepared statements" on your SQL statements for retrieving data, the
33following methods can be used:
34
35+------+----------+--------------------------------------------------+---------------+---------------------------+
36| Name | Normal   | Prepared statements                              | Cache Normal  | Cache Prepared statements |
37+======+==========+==================================================+===============+===========================+
38| all  | GetAll   | `PGetAll </en/latest/database/getAll.html>`_     | CacheGetAll   | CachePGetAll              |
39+------+----------+--------------------------------------------------+---------------+---------------------------+
40| assoc| GetAssoc | `PGetAssoc </en/latest/database/getASSOC.html>`_ | CacheGetAssoc | CachePGetAssoc            |
41+------+----------+--------------------------------------------------+---------------+---------------------------+
42| col  | GetCol   | `PGetCol </en/latest/database/getCol.html>`_     | CacheGetCol   | CachePGetCol              |
43+------+----------+--------------------------------------------------+---------------+---------------------------+
44| one  | GetOne   | `PGetOne </en/latest/database/getOne.html>`_     | PGetOne       | CacheGetOne               |
45+------+----------+--------------------------------------------------+---------------+---------------------------+
46| row  | GetRow   | `PGetRow </en/latest/database/getRow.html>`_     | PGetRow       | CacheGetRow               |
47+------+----------+--------------------------------------------------+---------------+---------------------------+
48
49
50Any query or either for Inserting or Updating:
51
52======= ======= ===================
53Name    Normal  Prepared statements
54======= ======= ===================
55Execute Execute `PExecute </en/latest/database/PExecute.html>`_
56======= ======= ===================
57
58.. note::
59
60   Notice that when using "Prepared statements" the methods are
61   prefixed with a **P**.
62
63.. seealso::
64
65   Method `Cache </en/latest/database/Cache.html>`_.
66
67
68Examples
69........
70
71.. code-block:: php
72   :linenos:
73   :emphasize-lines: 12
74
75   <?php
76
77   $user = getenv('MYSQL_USER') ?: 'root';
78   $password = getenv('MYSQL_PASS') ?: '';
79
80   require_once 'dalmp.php';
81
82   $DSN = "utf8://$user:$password@127.0.0.1/test";
83
84   $db = new DALMP\Database($DSN);
85
86   $db->PExecute('SET time_zone=?', 'UTC');
87
88
89Example using the `LIKE <http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html>`_ statement:
90
91.. code-block:: php
92   :linenos:
93
94   <?php
95
96   $sql = 'SELECT Name, Continent FROM Country WHERE Population > ? AND Code LIKE ?';
97
98   $rs = $db->FetchMode('ASSOC')->PGetAll($sql, 1000000, '%P%');
99
100
101If you want to define the types, you must pass an array specifying each type.
102Example:
103
104.. code-block:: php
105   :linenos:
106
107   <?php
108
109   $sql = 'SELECT * FROM mytable WHERE name=? AND id=?';
110
111   $rs = $db->FetchMode('ASSOC')->PGetAll($sql, array('s' => '99.3', 7));
112
113An Insert example:
114
115.. code-block:: php
116   :linenos:
117
118   <?php
119
120   $db->PExecute('INSERT INTO mytable (colA, colB) VALUES(?, ?)', rand(), rand());
121
122.. seealso::
123
124   Method `PExecute </en/latest/database/PExecute.html>`_
125
126An Update example:
127
128.. code-block:: php
129   :linenos:
130
131   <?php
132
133   $db->PExecute('UPDATE Country SET code=? WHERE Code=?', 'PRT', 'PRT');
134
135.. warning::
136
137   When updating the return value **0**, Zero indicates that no records where
138   updated.