Documentation/database/queries.html

   1 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
   2 <head>
   3         <title>CarbonPHP Documentation</title>
   4         <style type="text/css">
   5                 * {
   6                         margin: 0;
   7                         padding: 0;
   8                 }
   9
  10                 html {
  11                         font-family: "Lucida Sans Unicode", "Lucida Grande";
  12                         font-size: 14px;
  13                 }
  14
  15                 body {
  16                         background: #fff;
  17                         color: #666;
  18                 }
  19
  20                 h1 {
  21                         font-size: 15px;
  22                         font-weight: bold;
  23                         margin: 0 0 5px 0;
  24                 }
  25
  26                 h2 {
  27                         font-size: 13px;
  28                         font-weight: bold;
  29                         margin: 5px 0 5px 0;
  30                 }
  31
  32                 ol, ul {
  33                         margin: 10px 0 10px 0;
  34                         list-style-position: inside;
  35                 }
  36
  37                 p {
  38                         margin: 5px 0 5px 0;
  39                 }
  40
  41                 .content {
  42                         border: 1px dotted #444;
  43                         margin: 10px;
  44                         padding: 10px;
  45                         text-align: justify;
  46                         width: 700px;
  47                 }
  48
  49                 .example {
  50                         border: 1px solid #ccc;
  51                         background: #eee;
  52                         margin: 10px;
  53                         padding: 10px;
  54                         text-align: left;
  55                 }
  56         </style>
  57 </head>
  58
  59 <body>
  60         <div class="content">
  61                 <h1>Database Queries</h1>
  62
  63                 <p>These outline the ways in which you can submit queries to your database.</p>
  64         </div>
  65
  66         <div class="content">
  67                 <h1>$this->db->query()</h1>
  68
  69                 <p>To submit a query you can use the following method.</p>
  70
  71                 <div class="example">
  72                         $this->db->query('SQL QUERY HERE');
  73                 </div>
  74
  75                 <p>The <b>query()</b> method returns a database result object when 'read' type queries run, which you can
  76                 then use to show database results.  When a 'write' type of query is used it will simply return a true or false
  77                 depending on success or failure.  When retrieving data you will typically assign the return value to a
  78                 variable</p>
  79
  80                 <div class="example">
  81                         $query = $this->db->query('SQL QUERY HERE');
  82                 </div>
  83         </div>
  84
  85         <div class="content">
  86                 <h1>$this->db->simple_query()</h1>
  87
  88                 <p>This is a simplified version of the <b>query()</b> method.  It will only return true or false.  It will
  89                 not return a database result set, nor does it set the query timer, or compile bind data, or store your query
  90                 for debugging.  It simply submits a query.</p>
  91         </div>
  92
  93         <div class="content">
  94                 <h1>Adding Database Prefixes Manually</h1>
  95
  96                 <p>If you have configured a database prefix and would like to add it in manually, you can use the following.</p>
  97
  98                 <div class="example">
  99                         $this->db->dbprefix('tablename');
 100                         // outputs prefix_tablename
 101                 </div>
 102         </div>
 103
 104         <div class="content">
 105                 <h1>Protecting Identifiers</h1>
 106
 107                 <p>In many databases it is adviable for you to protect table and field names, for example with backticks in MySQL.  The Active Record class
 108                 automatically protects its queries.  If you need to manually protect an identifier you can use the following.</p>
 109
 110                 <div class="example">
 111                         $this->db->protect_identifiers('table_name');
 112                 </div>
 113         </div>
 114
 115         <div class="content">
 116                 <h1>Escaping SQL Queries</h1>
 117
 118                 <p>It is very good practice to escape your data that is being inserted into a database.  CarbonPHP provides
 119                 two methods to help you do this.</p>
 120
 121                 <ol>
 122                         <li><b>$this->db->escape()</b> - this method determines the data type so that it can escape only string
 123                         data.  It also automatically adds single quotes around the data.</li>
 124                         <li><b>$this->db->escape_str()</b> - this method escapes data regardless of the type.  Most of the time
 125                         the above method will be used instead of this one.</li>
 126                 </ol>
 127         </div>
 128
 129         <div class="content">
 130                 <h1>Query Binding</h1>
 131
 132                 <p>Binding enables you to simplify your SQL query syntax by letting CarbonPHP put the queries together for you.
 133                 Consider the following example.</p>
 134
 135                 <div class="example">
 136                         $sql = 'SELECT * FROM `some_table` WHERE `id` = ? AND `status` = ? AND `author` = ?';<br />
 137                         <br />
 138                         $this->db->query($sql, array(3, 'inprint', 'Charlie'));
 139                 </div>
 140
 141                 <p>The question marks in the query are replaced by the values found in the array passed to the method in the
 142                 second parameter.  Another benefit of using binds are that they automatically escape values producing
 143                 safer queries.</p>
 144         </div>
 145 </body>
 146
 147 </html>