Tutorial Details
- Topic: Database Access
- Difficulty: Intermediate
- Estimated Completion Time: 14 Minute Video
Download Source Files
PHP’s prepared statements (for database access) are fantastic. Not only do they help secure your database queries, but they’re also particularly more efficient for larger products. However, there are a couple issues that appear to make these methods less flexible than we’d hope. For one, we must utilize the bind_result method, and pass in a specific number of variables. However, what happens when this code is within a class, and we won’t immediately know how many variables to pass? Luckily, there’s a solution! I’ll show you what it is in today’s video tutorial.
Premium Members: Download this Video ( Must be logged in)
Subscribe to our YouTube page to watch all of the video tutorials!
Final Code
<?php
function read()
{
$parameters = array();
$results = array();
$mysql = new mysqli('localhost', 'root', 'root', 'db') or die('There was a problem connecting to the database');
$stmt = $mysql->prepare('SELECT body FROM posts') or die('Problem preparing query');
$stmt->execute();
$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() ) {
$x = array();
foreach( $row as $key => $val ) {
$x[$key] = $val;
}
$results[] = $x;
}
return $results;
}
$results = read();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>untitled</title>
</head>
<body>
<?php foreach ($results as $row) : ?>
<p> <?php echo $row['body']; ?> </p>
<?php endforeach; ?>
</body>
</html>
Enjoyed this Post?
Subscribe to our RSS Feed, Follow us on Twitter or simply recommend us to friends and colleagues!
By Jeffrey Way
Join our newsletter!
Create a Tutorial, Get Paid!
Have something to teach the world? Want to earn money doing it? Tutorials, screencasts, and articles published here on Nettuts+ are largely contributed by readers just like you! We'll pay you great money for good content. Find Out More
Oh, this is a good trick. My applications already have this implemented, hamidhossain posted this on PHP’s Manual 2 years ago:
http://www.php.net/manual/en/mysqli-stmt.bind-result.php#85470
Thanks Jeff!
Thanks for the idea!
An elegant solution to the problem. Thank you so much.
Awesome solution.
great idea very very thanks
This is great and very useful. I wasn’t aware of the result_metadata() method of the statement object before.
This is a problem I have encountered in the past, so I’ve found this tutorial very useful.
The only thing I would ask is if you could clarify why you needed to pass the &$row[$field->name]; by reference.
I’m not an expert in passing things by reference and have never really understood the need for it if I’m honest. You did say at one point in the video that you’d explain further your reasons for doing this, but I didn’t notice a deeper explanation of this point.
This was a very useful tutorial and I appreciate it. Thanks :-)
Jeff, I am also curious to know about the &$row….
Thanks very much!!!
it might be something like using a pointer, basically the address in the memory for that specific entity… don’t know for sure though…
I too am a bit confused about how passing by reference works. I would love a quick tip screencast to clear things up =)
Anyways, a great tutorial a usual, thanks !
Yeah, please explain that. I’m curious too.
Thanks for a great quick tip!!
I’m a bit confused as well. I understand pointers and all. I think whats confusing me is your use of the $row array. I don’t see $row defined anywhere in your code before that point…
Yes!!! please explain it!! where is defined $row? and why you are using a pointer???
Sorry, if I missed something here. Why not just use fetch_assoc?
http://us3.php.net/manual/en/mysqli-result.fetch-assoc.php
If you have PHP 5.3, you could also use fetch_all without even looping:
http://us3.php.net/manual/en/mysqli-result.fetch-all.php
Like this:
$result = $stmt->fetch_all(MYSQLI_ASSOC);
Sorry, looks like I was thinking of PDO, not the mysqli prepared statements. In PDO you can do a prepared statement and get an associative array. But with mysqli, only regular queries let you use the fetch_assoc method, not prepared statements.
Hey Burak, why not do a PDO screencast for us Ol’ Buddy, Ol’ Pal. :)
Ha,
I was thinking the same thing Burak. Thanks for mis-stepping before me.
I’m with you. Why use mysqli when PDO does such nice abstraction already.
Great tutorial. Thanks for that!
Very nice tutorial. Thanks a lot.
Nice tutorial.
Thanks for sharing.
Great tutorial, I haven’t read lately about mysql, I almost forgot it :)
Why not use mysqli::fetch_assoc() ?
Great tutorial.
I think the reason why Jeff is using the & operator is to assign each row to the parameter.
It’s not easy to understand but it’s memory allocation. So when he calls $parameter he’s refering to the $row instead.
For example:
$me = “Joao”;
$reference = &$me;
$me = “Lopes”;
echo $reference; //Will print Lopes
I have to be honest to you, i can’t really get why he needs to reference the $row or where did it come from, but that’s the truth behind memory allocation. He is pointing to the position in memory instead of the value of $row.
Hope it helps.
Cheers.
So simply it is probably something like that:
class Foo {
private $query;
public function __construct() {
….
}
public function prepare($some_string_query) {
$this->query = $some_string_query;
}
public function execute() {
return some_mysql_operation($this->query);
}
}
in simple way of course…:)
Jeffrey, please stop using ‘or die()’! It’s a bad coding habit and it should be avoided at any cost. No error reporting, no additional information, just a line of pre-defined line of characthers. Use trigger_error instead or use exceptions if you are working with OOP.
I wouldn’t mind writing a quick tip about it…
Well even though it’s not good practice it’s very useful if, like in this case, you’re writing a tutorial about something else database related and don’t want to spend time on catching exceptions.
Please write a Quick Tip on it.
I contacted Jeffrey about writing a quick tip about it a while ago, but his answer was no back then. So yeah, I don’t know whether his mind has changed or not.
I would really like to see a tutorial about how to handle errors! It has always been a tricky subject I think.
Thanks in advance ;P
Hi,
That’s useful, for sure.
But I have 2 questions regarding this:
1. You never define the $row variable. Did you forget? (I assume so, because you really defines the other).
2. PHP Standard is heading to PDO, which doesn’t have this problem, because you can fetch directly the statement without binding the variables. Isn’t that even easier?
Thats a nice workaround, but i would just use PDO with the mysql driver!
That will give you all the power of prepared statements and you dont have to bind the results (but you can if you want…)
Your example would something like this:
$mysql = new PDO(mysql:host=localhost;dbname=db’, ‘root’, ‘root’) or die(‘There was a problem connecting to the database’);
$stmt = $mysql->prepare(‘SELECT body FROM posts’) or die(‘Problem preparing query’);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
Thats all you have to do :)
I don’t use prepared statements very much, but when I did try, I found that it was very difficult to use prepared statements with nested mysqli while loops. Not sure if I was doing something wrong, or that it isn’t possible. I looked for help, but couldn’t find much out there to help me on it.
very cool solution jeffrey way!
How about that with an INSERT?
This is for binding results. For INSERTS, you use the “bind_param” method. See here for an implementation: http://github.com/JeffreyWay/PHP-MySQL-Database-Class
your class is acting strange when you want to execute couple INSERTs, one by one.
i can’t figure out why… can you help? :)
I’m a bit confused by this, let me try to explain why.
Firstly, the original code you used $title and $body, due to the binding of the results into those variables.
However, afterward in your actual solution(?) you are just using $row['body'] so what’s the point of this whole exercise, as you aren’t actually binding the results to the variables.
Forgive me if I’m mistaken, but I just feel this is a long winded solution that doesn’t actually solve the problem of dynamic binding?
Yes – but that’s not being done in the class file. With this method, you never have to touch the class.
And why not use PDO for this problem. As far as I remember executing with named parameters (‘:id’ in the query, and array(‘id’ => 1) as params passed to execute) achieves the same effect.
Nice solution J, I had this problem in a project a few months back and implemented something pretty similar
It’s really simple to use the Exception class and grasp the concept;
$var = 5;
try {
if ( $var != 6 )
throw new Exception(‘Not Six there buddy.’);
} catch Exception ($e) {
echo ‘Error occured: ‘.$e->getMessage();
exit;
}
as you can see first you begin with try { } then do something, if that something fails throw the exception then once the try is complete catch the exception then provide some error message.
Whoops that was supposed to be a reply to the exception queries above, my bad.
Thanks Jeff!
As always, nice tut. Jeff. However, how much using a simple prepared select (without any WHERE conditions) better than a regular mysql query? I was hoping you’d go into detail as to how you could use this in an insertion or update instance.
Thanks.
I’ve had trouble on a couple servers with call_user_func_array. I could never figure out the issue. PHP version was 5+.
I have implemented this workaround too, based on what is already in the PHP manual. It feels a bit hackish, but in my opinion is necessary to give you some dynamic flexibility while using prepared statements.
Nice tutorial….! Thanks for sharing….!!!
Great tutorial! Can such thing be done sith bind_param too?
got a little confused when using $row the second time , but i got it eventually .
nice tut . keep them coming :)
You don’t have to use the BindParam type of functions. All you have to do is
prepare($query);
$stmt->execute(array($table_n, $user_id);
?>
So when you execute your query, put in your query parameters there.
The php.net manual method/funtion definitions for execute indicate it cannot be called like that
I see …
oo style
————
bool mysqli_stmt::execute ( void )
Procedural style
———————-
bool mysqli_stmt_execute ( mysqli_stmt $stmt )
How did you manage to get params in there and get it to work? Is this new in 5.3?
Unless you’re just using prepared statements for ‘security,’ you’re going to lose some of the performance gains from prepared statements by using that ‘call_user_func_array,’ which is notoriously slow.
Not that it matters, and this is a cool way to make prepared statements more usable. I just never see a huge advantage to prepared statements over careful string manipulation. You’ll probably lose what you gained in a stored query plan with that call_user_* function call.
Depending on whether it’s in a loop, it might entirely cancel out.
Oh well :) Bookmarked for future use.
I still find it much easier and more maintainable using a wrapper class to do all of the work: http://www.aplweb.co.uk/blog/php/mysqli-wrapper-class/
Is it possible to use this if you wanted to update or insert a table in the database?
I believe this is for the set of queries that actually returns results. That is SELECT, etc.
Since the DELETE, UPDATE, and INSERT are from the set of queries that do not return results, this tut should not matter. Unless you were doing something funky like combining a stored proc with prepared statements to somehow get back a result set from queries that normally would not return results.
Thank you! this is excellent, I needed to setup a database helper class and this helped solve my issue!
Thanks jeffrey!.. I was just wondering also if you are inserting into database how can you handle bind_param() in order to make it reusable (assuming the function was in a class)? Thanks a lot
e.g.
function insert($q){
$stmt->prepare($q)
$stmt->bind_param(…….); //right here jeff!!
$stmt->execute();
}
From the comments on the php.net manual….
$param = array(of, your, agruments);
proc style
————-
call_user_func_array(‘mysqli_stmt_bind_param’, array_merge (array($sql_stmt, $type), refValues($param)));
oo style
———
call_user_func_array(array($stmt, ‘bind_param’) , array_merge (array($type), refValues($param)));
function refValues($arr)
{
if (strnatcmp(phpversion(),’5.3′) >= 0) //Reference is required for PHP 5.3+
{
$refs = array();
foreach($arr as $key => $value)
$refs[$key] = &$arr[$key];
return $refs;
}
return $arr;
}
The refvals function is need to fudge the arguments as references for php 5.3+ since 5.3 corrected the way in which they should be passed to bind_params
Thank you so much for this!
I’ve been searching the net for a solution to this specific problem for about a week now, I was about to give up on it completely and just use regular mysql queries. Now with renewed energy I can go back to finishing this database class :D.
You are a hero amongst men.
Feels like I know this from somewhere :P
http://marketplace.tutsplus.com/item/-objectoriented-programming-in-php-videos-811/126882
thanxs for this article helped me a lot but for more detailed prepared statement please visit http://mukundtopiwala.blogspot.in/p/avoid-sql-injection-in-php.html