Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
oderza

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

Share this post


Link to post
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.

}

Share this post


Link to post
Share on other sites

I know what you mean about how this is very easy with PDO and you'd think you could do it with MySQLi, but I don't know how offhand myself.

  • Upvote 1

Share this post


Link to post
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!

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...