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.