Jump to content
Larry Ullman's Book Forums

Using Mysqli Prepared Statements To Fetch Objects


Recommended Posts

I have been trying to research how to have a prepared mysqli statement give you a result set that you can fetch into an object (class). I know PDO can do this effortlessly, but it's hard to believe you can't do it with mysqli. Maybe someone can point me in the right direction.

 

Some example code that I have been trying:

 

class Page {

protected $id = null;

protected $title = null;

protected $content = null;

}

 

$dbc = new mysqli('host', 'user', 'password', 'db');

 

$q = 'SELECT id, title, content FROM pages WHERE id=?';

$stmt = $dbc->prepare($q);

$pid = (int) $_GET['p']; // I validate this extensively above... no worries.

$stmt->bind_param('i', $pid);

$stmt->execute();

$stmt->store_result();

if ($stmt->num_rows ==1) {

echo "<h1>Success!</h1>"; //This works, but the following line doesn't. $page is never created.

$page = $stmt->fetch_object('Page');

}

 

 

If I decide not to use:

stmt->store_result and $stmt->fetch object

 

and instead just use:

stmt->bind_result($id, $title, $content) and stmt->fetch

I can successfully retrieve the columns ($id, $title, $content) individually, but I would rather put them into an object.

 

 

What I also found interesting is that if I don't use a prepared statement, then fetching into an object isn't a big deal either. This works just fine:

 

 

$q = 'SELECT id, title, content FROM pages LIMIT 10';

$r = $dbc->query($q);

while($page = $r->fetch_object('Page')){

// This works

}

 

Thanks

Link to comment
Share on other sites

I just read that if you're only going to run a statement once, it probably isn't very efficient to use prepared statements. So for the scenario above I changed my code to this:

 

$q = sprintf('SELECT id, title, content FROM pages WHERE id=%d', (int)$_GET['id']);

$r = $dbc->query($q);

if ($r->num_rows ==1){

$page = $r->fetch_object('Page');

}

 

This works fine and creates a Page object. However, I will come across instances where I do want to use mysqli prepared statements. The only work around that I could think of is to bind_result like mentioned in my previous post and then create a new object doing this:

 

$stmt->bind_result($id, $title, $content);

while($stmt->fetch()){

$page = new Page($id, $title, $content); // I would change the Page class to include a __construct function.

}

Link to comment
Share on other sites

I didn't even think about extending the mysqli class to accomplish this. I don't think I'm brave enough to give it a try. Honestly I was just surprised that you couldn't do (with mysqli) what is so easily done using PDO, so I thought I would ask in case I was missing something.

 

Thanks for the replies!

Link to comment
Share on other sites

To be honest with you, I don't know if it's possible or probable myself. I was more asking for the sake of clarifying your question. With that said, I would imagine that you can extend the MySQLi class, as that's standard OOP.

 

Anyway, what you can do is write your own mini

API for combining MySQLi with prepared statements. In fact, I do this all the time.

 

All this really amounts to is writing one or more functions that can take the necessary arguments for a given query, and then using both a MySQLi DB connection with prepared statement functions to generate the necessary results and returning them as an array or whatever.

 

I would personally recommend this route if you want a simpler and more lightweight PDO.

Link to comment
Share on other sites

 Share

×
×
  • Create New...