php - SELECT * FROM in MySQLi

Question

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?

Answer 1

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/(This link is no longer active). 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.