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