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?

1
23
5/5/2009 12:03:59 PM

Accepted Answer

"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).

32
5/29/2014 6:22:46 AM

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/(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.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon