SELECT * FROM in MySQLi

My site is rather extensive, and I just recently made the switch to PHP5 (call me a late bloomer).

All of my MySQL query's before were built as such:

"SELECT * FROM tablename WHERE field1 = 'value' && field2 = 'value2'";

This made it very easy, simple and friendly.

I am now trying to make the switch to mysqli for obvious security reasons, and I am having a hard time figuring out how to implement the same SELECT * FROM queries when the bind_param requires specific arguments.

Is this statement a thing of the past?

If it is, how do I handle a query with tons of columns involved? Do I really need to type them all out every time?

From stackoverflow

• Is this statement a thing of the past?

  Yes. Don't use SELECT *; it's a maintenance nightmare. There are tons of other threads on SO about why this construct is bad, and how avoiding it will help you write better queries.

  See also:

  • http://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select
  • http://stackoverflow.com/questions/487578/performance-issue-in-using-select/487612#487612
  • http://stackoverflow.com/questions/262450/why-is-using-to-build-a-view-bad
  johnnietheblack : okay...i totally believe you, but im having trouble finding them somehow, and i could learn from them im sure. coudl you give me a link? thanks:)

  Thilo : I think the question was about bind variables. I see no big problem with "SELECT *" (if you need more than just primary key or otherwise indexed columns).

  kquinn : Yeah, search engines don't like querying for "SELECT *" much; I edited my post with a couple links (the first link is especially useful).

  johnnietheblack : kquinn - thanks for the resources:)

• You can still use it (mysqli is just another way of communicating with the server, the SQL language itself is expanded, not changed). Prepared statements are safer, though - since you don't need to go through the trouble of properly escaping your values each time. You can leave them as they were, if you want to but the risk of sql piggybacking is reduced if you switch.

  soulmerge : Sometimes you even *need* the old usage. Mysql can't do multiple inserts in a prepared statement for example.

  johnnietheblack : it cant? isn't that something that most people need to do?

  soulmerge : No, it cannot do something like INSERT INTO A (a,b) VALUES (1,2), (3,4), (5,6). And *if* (and only if) speed is an issue (which shouldn't be in 99% of cases - but we just had one in a recent project), consider this: prepared statements need 3 communications for 1 query (prepare, set var, execute)

  johnnietheblack : oh, i gotcha....makes sense now

• "SELECT * FROM tablename WHERE field1 = 'value' && field2 = 'value2'";
  

  becomes

  "SELECT * FROM tablename WHERE field1 = ? && field2 = ?";

  which is passed to the $mysqli::prepare:

  $stmt = $mysqli->prepare(
    "SELECT * FROM tablename WHERE field1 = ? && field2 = ?");
  $stmt->bind_param( "ss", $value, $value2); 
  // "ss' is a format string, each "s" means string
  $stmt->execute();
  
  $stmt->bind_result($col1, $col2);
  // then fetch and close the statement
  

  OP comments:

  so if i have 5 parameters, i could potentially have "sssis" or something (depending on the types of inputs?)

  Right, one type specifier per ? parameter in the prepared statement, all of them positional (first specifier applies to first ? which is replaced by first actual parameter (which is the second parameter to bind_param)).

  mysqli will take care of escaping and quoting (I think).

  johnnietheblack : so if i have 5 parameters, i could potentially have "sssis" or something (depending on the types of inputs?)

• While you are switching, switch to PDO instead of mysqli, It helps you write database agnositc code and have better features for prepared statements.

  http://www.php.net/pdo

  Bindparam for PDO: http://se.php.net/manual/en/pdostatement.bindparam.php

  $sth = $dbh->prepare("SELECT * FROM tablename WHERE field1 = :value1 && field2 = :value2");
  $sth->bindParam(':value1', 'foo');
  $sth->bindParam(':value2', 'bar');
  $sth->execute();
  

  or:

  $sth = $dbh->prepare("SELECT * FROM tablename WHERE field1 = ? && field2 = ?");
  $sth->bindParam(1, 'foo');
  $sth->bindParam(2, 'bar');
  $sth->execute();
  

  or execute with the parameters as an array:

  $sth = $dbh->prepare("SELECT * FROM tablename WHERE field1 = :value1 && field2 = :value2");
  $sth->execute(array(':value1' => 'foo' , ':value2' => 'bar'));
  

  It will be easier for you if you would like your application to be able to run on different databases in the future.

  I also think you should invest some time in using some of the classes from Zend Framwework whilst working with PDO. Check out their Zend_Db and more specifically [Zend_Db_Factory][2]. You do not have to use all of the framework or convert your application to the MVC pattern, but using the framework and reading up on it is time well spent.

  johnnietheblack : awesome, iv never heard of PDO, ill check it out

• This was already a month ago, but oh well.

  I could be wrong, but for your question I get the feeling that bind_param isn't really the problem here. You always need to define some conditions, be it directly in the query string itself, of using bind_param to set the ? placeholders. That's not really an issue.

  The problem I had using MySQLi SELECT * queries is the bind_result part. That's where it gets interesting. I came across this post from Jeffrey Way: http://jeff-way.com/2009/05/27/tricky-prepared-statements/. The script basically loops through the results and returns them as an array — no need to know how many columns there are, and you can still use prepared statements.

  In this case it would look something like this:

  $stmt = $mysqli->prepare(
    'SELECT * FROM tablename WHERE field1 = ? AND field2 = ?');
  $stmt->bind_param('ss', $value, $value2);
  $stmt->execute();

  Then use the snippet from the site:

  $meta = $stmt->result_metadata();
  
  while ($field = $meta->fetch_field()) {
    $parameters[] = &$row[$field->name];
  }
  
  call_user_func_array(array($stmt, 'bind_result'), $parameters);
  
  while ($stmt->fetch()) {
    foreach($row as $key => $val) {
      $x[$key] = $val;
    }
    $results[] = $x;
  }

  And $results now contains all the info from SELECT *. So far I found this to be an ideal solution.

  0plus1 : Thanks, this was the exact same problem I had, the accepted answer won't work with SELECT *