title: PDOStatement::execute(): SQLSTATE[42S22]: Column not found: 1054 Unknown column in 'field list' in
body: <p>5.6.17 - MySQL Community Server (GPL)</p>
  
  <p>Generated a simple rountine (stored procedure) in mysql to update a table, which is passed in as a variable. </p>
  
  <p>I have a ugly fix for it, but i want to understand why the first two techniques did not work?</p>
  
  <p>Stored Procedure one could not recognize the table name I passed in</p>
  
  <pre><code>DROP PROCEDURE IF EXISTS `setNewCategoryUrlTag`//
  
  CREATE PROCEDURE `setNewCategoryUrlTag`(
      IN _id INT,
      IN _urlid VARCHAR(150),
      IN _tablename VARCHAR(100)
  )
  BEGIN
      UPDATE _tablename SET urlid=_urlid WHERE ID=_id;
  END//
  </code></pre>
  
  <p>Error was:</p>
  
  <pre><code>PDOStatement::execute(): SQLSTATE[42S02]: Base table or view not found: 1146 Table '_tablename' doesn't exist in
  </code></pre>
  
  <p>So I used the concat function which produced the second error. </p>
  
  <pre><code>DROP PROCEDURE IF EXISTS `setNewCategoryUrlTag`//
  
  CREATE PROCEDURE `setNewCategoryUrlTag`(
      IN _id INT,
      IN _urlid VARCHAR(150),
      IN _tablename VARCHAR(100)
  )
  BEGIN
      set @query = CONCAT('UPDATE ',_tablename,' SET urlid=',_urlid,' WHERE ID=',_id,';');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
  END//
  </code></pre>
  
  <p>Error was:</p>
  
  <pre><code>PDOStatement::execute(): SQLSTATE[42S22]: Column not found: 1054 Unknown 'home' column in 'field list' in
  </code></pre>
  
  <p>where home was the actual value of the variable _urlid</p>
  
  <p>The Ugly fix is to add if else statements surrounding updates for specific table names. :( </p>
  
  <p>Thank you for anyone who can explain to me why this error is occuring in my stored procedures. I have used similar techniques before but this never occurred.</p>
  
  <p><strong>Additional Info</strong></p>
  
  <pre><code>public function dataInsert($category){
      $procedure = "Call setNewCategoryUrlTag(?,?,?)";
      $statement -&gt; bindParam(1, $category['id']);
      $statement -&gt; bindParam(2, $category['urlid']);
      $statement -&gt; bindParam(3, $category['tableName']);
      $statement -&gt; execute();
      echo $category['id']."\t".$category['urlid']."\t".$category['tableName'];
  }
  </code></pre>
  
  <p><strong>Drew's Suggestion?!?!?! (Unsure because his explanation was not clear)</strong></p>
  
  <pre><code>DROP PROCEDURE IF EXISTS `setNewCategoryUrlTag`//
  CREATE PROCEDURE `setNewCategoryUrlTag`(
      IN _id INT,
      IN _urlid VARCHAR(150),
      IN _tablename VARCHAR(100)
  )
  BEGIN
      set @t = _tablename;
      set @var1 = _urlid;
      set @var2 = _id;
      set @query = CONCAT('UPDATE ',@t,' SET urlid=',@var1,' WHERE ID=',@var2,';');
      PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
  END//
  </code></pre>
  
  <p>Anyhow this produces the same error as my copy.</p>