title: PHP PDO Invalid parameter number: parameter was not defined using Q-mark parameters
body: <p><strong>EDIT</strong>: You're not allowed to have the word "question" in the title. Q-mark means "question-mark"</p>
  
  <p><strong>EDIT 2</strong>: Realized I should remove my personal contact info from the dummy customer record</p>
  
  <p>I'm trying to making some basic functions to reduce maintenance of a recent project. Among these is the following simple function so I can add columns to the database at a later date without needing to change my update queries:</p>
  
  <pre><code>function update( $table, $fields ) {
      global $db; // A slightly modified PDO object
      if( count( $fields ) &gt; 0 ) {
          $query = "UPDATE `" . $table . "` SET ";
          foreach( $customer as $key =&gt; $value ) {
              $query .= "`" . $key . "` = ?, ";
          }
          $query = substr( $query, 0, -2 );
          $query .= " WHERE `id` = " . $_REQUEST['id'];
          $db-&gt;prepare( $query );
          $db-&gt;execute( $fields );
      } else { return true; }
  }
  </code></pre>
  
  <p>This function was working when I passed one or two fields, but the moment I tried passing more it failed. Here's the query, value of <code>$fields</code>, and error I'm receiving:</p>
  
  <p>Query:</p>
  
  <pre><code>string 'UPDATE `customers` SET `5v` = ?, `contactaddress` = ?, `contactaddress2` = ?, `contactcity` = ?, `contactemail` = ?, `contactname` = ?, `contactphone` = ?, `contactstate` = ?, `contactzip` = ?, `corrugated` = ?, `name` = ?, `permalok` = ?, `residential` = ?, `secureseam` = ?, `tax` = ? WHERE `id` = 1' (length=301)
  </code></pre>
  
  <p>$fields:</p>
  
  <pre><code>array
    '5v' =&gt; string '120' (length=3)
    'contactaddress' =&gt; string '999 Street Dr' (length=13)
    'contactaddress2' =&gt; string 'Apt 2' (length=7)
    'contactcity' =&gt; string 'City' (length=9)
    'contactemail' =&gt; string 'steven.abarnett@gmail.com' (length=25)
    'contactname' =&gt; string 'Steven Barnett' (length=14)
    'contactphone' =&gt; string '(555) 555-5555' (length=14)
    'contactstate' =&gt; string 'KY' (length=2)
    'contactzip' =&gt; string '55555' (length=5)
    'corrugated' =&gt; string '90' (length=2)
    'name' =&gt; string 'Steven's Metals' (length=15)
    'permalok' =&gt; string '130' (length=3)
    'residential' =&gt; string '100' (length=3)
    'secureseam' =&gt; string '130' (length=3)
    'tax' =&gt; string '6' (length=1)
  </code></pre>
  
  <p>Error:</p>
  
  <pre><code>string 'Statement execution failed: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' (length=96)
  </code></pre>
  
  <p>Googling this error, it sounds like an issue with <em>named</em> parameters, not <em>question-mark</em> parameters. Why am I getting this error?</p>
  
  <p><strong>UPDATE</strong>:</p>
  
  <p>I've been continuing to debug and experiment and tracing the error I found it's not the statement execution that fails, but attempting to fetch the result afterwards.</p>
  
  <p>In my modified PDO object I have the following code within my <code>execute()</code> method:</p>
  
  <pre><code>    // Load from database
      try {
          $this-&gt;stmt-&gt;execute( $args );
          $this-&gt;lastid = $this-&gt;dbh-&gt;lastInsertId();
          $this-&gt;data = $this-&gt;stmt-&gt;fetchAll();
      } catch (PDOException $e) {
          $this-&gt;error = 'Statement execution failed: ' . $e-&gt;getMessage();
          return false;
      }
  </code></pre>
  
  <p>Placing <code>echo</code> statements between each line in the <code>try{}</code> section shows that the first two lines run without a hitch, but the third line is throwing the exception.</p>
  
  <p>Is there are reason my query would fail with a general error when I call <code>PDOStatement::fetchAll()</code>?</p>
  
  <p><strong>FINAL UPDATE:</strong></p>
  
  <p>As it turns out <a href="https://bugs.php.net/bug.php?id=35793" rel="nofollow">PDO and Mysql haven't been playing nicely for 6 years</a>. The general solution seems to be to unset the PDOStatement object after each call to <code>PDOStatement::execute()</code>, however this didn't work for me. The issue stems from the fact that MySQL can only have one cursor open at a time pointing to records in a record set, so another proposed solution is to use <code>PDOStatement::fetchAll()</code> instead of <code>PDOStatement::fetch()</code> OR to call <code>PDOStatement::closeCusor()</code>. Again, this didn't work for me. My issue comes from certain installations of MySQL which will create a cursor during <code>INSERT</code>, <code>UPDATE</code>, and <code>DELETE</code> queries (all of which return no data) which can not be closed since it's pointing to null data.</p>
  
  <p>My solution was to only call <code>PDOStatement::fetchAll()</code> when I'm running a query that return data. I have a <code>queryhash</code> variable which was used in caching (the big modification I made to the PDO class) which was set to <code>false</code> for these three query types (since you need to send the query to the database rather than deferring to cache). I simply modified my <code>execute()</code> function to check this variable and only call <code>fetchAll()</code> if it was not set to false.</p>