title: MySQLi: query VS prepare
body: <p>There is something I don't quite understand it at all which is <code>prepare</code> and <code>query</code> in mysqli.</p>
  
  <p>This one is using <code>mysqli::query</code> to process the query and it has been known of lacking security:</p>
  
  <pre><code>public function fetch_assoc($query)
      {
          $result = parent::query($query);
          //$result = self::preparedStatement($query);
          if($result) 
          {
              return $result-&gt;fetch_assoc();
          } 
          else
          {
              # call the get_error function
              return self::get_error();
              # or:
              # return $this-&gt;get_error();
          }
      }
  </code></pre>
  
  <p>this is the one with prepare-bind-execute which has a better security I assume,</p>
  
  <pre><code>public function fetch_assoc_stmt($sql,$types = null,$params = null)
      {
          # create a prepared statement
          $stmt = parent::prepare($sql);
  
          # bind parameters for markers
          # but this is not dynamic enough...
          //$stmt-&gt;bind_param("s", $parameter);
  
          if($types&amp;&amp;$params)
          {
              $bind_names[] = $types;
              for ($i=0; $i&lt;count($params);$i++) 
              {
                  $bind_name = 'bind' . $i;
                  $$bind_name = $params[$i];
                  $bind_names[] = &amp;$$bind_name;
              }
              $return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
          }
  
          # execute query 
          $stmt-&gt;execute();
  
          # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
          $meta = $stmt-&gt;result_metadata(); 
  
          while ($field = $meta-&gt;fetch_field()) { 
              $var = $field-&gt;name; 
              $$var = null; 
              $parameters[$field-&gt;name] = &amp;$$var; 
          }
  
          call_user_func_array(array($stmt, 'bind_result'), $parameters); 
  
          while($stmt-&gt;fetch()) 
          { 
              return $parameters;  
          }
  
          # close statement
          $stmt-&gt;close();
      }
  </code></pre>
  
  <p>However, both of these two methods return the same result, </p>
  
  <pre><code>$mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
  
  $sql = "
  SELECT *
  FROM root_contacts_cfm
  ORDER BY cnt_id DESC
  ";
  print_r($mysqli-&gt;fetch_assoc_stmt($sql));
  
  print_r($mysqli-&gt;fetch_assoc($sql));
  </code></pre>
  
  <p>they print this:</p>
  
  <pre><code>Array
  (
      [cnt_id] =&gt; 2
      [cnt_email1] =&gt; lau@xx.net
      [cnt_email2] =&gt; 
      [cnt_fullname] =&gt; Lau T
      [cnt_firstname] =&gt; Thiam
      [cnt_lastname] =&gt; Lau
      [cnt_organisation] =&gt; 
      [cnt_website] =&gt; 
      [cnt_biography] =&gt; 
      [cnt_gender] =&gt; 
      [cnt_birthday] =&gt; 
      [cnt_address] =&gt; 
      [cnt_postcode] =&gt; 
      [cnt_telephone] =&gt; 
      [cnt_note] =&gt; 
      [cnt_key] =&gt; 
      [cat_id] =&gt; 
      [tcc_id] =&gt; 
      [cnt_suspended] =&gt; 0
      [cnt_created] =&gt; 2011-02-04 00:00:00
      [cnt_updated] =&gt; 2011-02-04 13:54:36
  )
  Array
  (
      [cnt_id] =&gt; 2
      [cnt_email1] =&gt; lau@xx.net
      [cnt_email2] =&gt; 
      [cnt_fullname] =&gt; Lau T
      [cnt_firstname] =&gt; Thiam
      [cnt_lastname] =&gt; Lau
      [cnt_organisation] =&gt; 
      [cnt_website] =&gt; 
      [cnt_biography] =&gt; 
      [cnt_gender] =&gt; 
      [cnt_birthday] =&gt; 
      [cnt_address] =&gt; 
      [cnt_postcode] =&gt; 
      [cnt_telephone] =&gt; 
      [cnt_note] =&gt; 
      [cnt_key] =&gt; 
      [cat_id] =&gt; 
      [tcc_id] =&gt; 
      [cnt_suspended] =&gt; 0
      [cnt_created] =&gt; 2011-02-04 00:00:00
      [cnt_updated] =&gt; 2011-02-04 13:54:36
  )
  </code></pre>
  
  <p>You should have noticed that inside the method of <code>fetch_assoc_stmt</code> I don't use <code>fetch_assoc</code> at all. Probably no chance at all in using it as <code>prepare</code> uses a different way to return result.</p>
  
  <p>So, my question is since using <code>prepare</code> is better than <code>query</code>, why should <code>fetch_assoc</code> exist at all? Shouldn't we just forget about it or shouldn't php.net have it deprecated?? It is the same for <code>fetch_all</code> - why should we have it in the first place!??</p>
  
  <p>Thanks.</p>