Using a mySQL Prepared Statement in Multiple Class Instances

Question

I'm trying to understand how to best take advantage of mysqli prepared statements and I'm also pretty new to object oriented PHP.

I understand that preparing a statement and then executing it multiple times is much more efficient than simply repeating the same query.

The class below fetches a sound object by primary key. If I needed to have several instances of this class in the same script, would I be getting the benefits of a prepared statement?

Would this be a good candidate for a stored procedure?

class sound {
    public $id;
    public $name;
    public $musickey;

    public function __construct($id) {
        $this->id = $id;
        $sqli = new mysqli();
        if($stmt = $con->prepare("SELECT name, musickey FROM sounds WHERE id = ?")) {
        $stmt->bind_param("i", $this->id);
        $stmt->execute();
        $stmt->bind_result($name, $musickey);
}

I hope my question isn't a duplicate. I've been trying to find something on this topic, but sometimes forming the right question can be just as hard as finding the answer.

score 2 · Accepted Answer · edited May 23 '17 at 11:44

I understand that preparing a statement and then executing it multiple times is much more efficient than simply repeating the same query.

Correct. Because a server-side prepared statement (what mysqli uses) only sends the bound arguments to the database on subsequent executions instead of the whole query. Also the database server will not have to reparse the query, but can reuse the prepared one. So, this can make a difference in retrieving data. So in theory, this is more efficient.

See http://php.net/manual/en/mysqli.quickstart.prepared-statements.php.

In practice, don't expect that much difference. It certainly depends on the amount and the complexity of queries you intend to call for this to make a significant difference. When in doubt, benchmark.

It also has the added benefit of protecting you from SQL Injection.

The class below fetches a sound object by primary key. If I needed to have several instances of this class in the same script, would I be getting the benefits of a prepared statement?

No. Because mysqli will fire a query to the server each time you create a new instance to prepare that statement then. In addition to the execute call. So you win nothing. It would be more straightforward then to just use something like

$query = sprintf('SELECT name, musickey FROM sounds WHERE id = %d', $id);

and then execute that as a whole query if you really want one instance per ID.

If you want to do this with prepared statements, you want only one instance that prepares once but that you can pass the IDs to multiple times.

Would this be a good candidate for a stored procedure?

If you put application logic into stored procedures, you are making your application less portable. If you want to change your database system at one point, you'd need to recreate the stored procedures. It's up to you to make an informed choice about this. If you don't need to change the database system, then it might be viable. I prefer to keep the logic inside the application.

I'm also pretty new to object oriented PHP.

One aspect of OOP is about encapsulating and hiding information (and complexity) from a client. So having public properties there isn't the best idea. Also, doing work in the constructor is not what the constructor is for. The constructor should only put your object into a valid state. And since preparing does a query you want to defer that until it's needed.

With all that said, consider

interface FindById
{
    public function findById($id);
}

class SoundFinder implements FindById
{
    private $mysqli;
    private $statement;
    private $query = "SELECT name, musickey FROM sounds WHERE id = ?";

    public function __construct(mysqli $mysqli) 
    {
        $this->mysqli = $mysqli;
    }

    private function prepare()
    {
        $this->statement = $mysqli->prepare($this->query)
    }

    public function findById($id)
    {
        if (!$this->statement) {
            $this->prepare();
        }
        $this->statement->bind_param("i", $this->id);
        $this->statement->execute();
        $this->statement->bind_result($name, $musickey);
        $this->statement->free_result();
        return array($name, $musickey);
    }
}

Then you can use it like this:

$connection = new mysqli(/* your connection data */);
$soundFnder = new SoundFinder($connection);
$sound42 = $soundFinder->findById(42);
$sound314 = $soundFinder->findById(314);

I knew I was missing something pretty fundamental. I hadn't thought about creating and then reusing just one instance of the class. Great explanation. Thank you. — Brian, Jul 29 '13 at 12:52
I dont mean to bring up dead threads but in the prepare function, shouldnt it be $this->mysqli->prepare() ?? If I was certain I would edit the post — Adsy2010, Jul 02 '14 at 18:16

score -1 · Answer 2 · answered Jul 29 '13 at 13:11

is much more efficient than simply repeating the same query.

You understand it wrong.
Dunno who told you that but there are no "much more" at all.
The difference is hardly noticeable and often doesn't worth the mess. Especially in case of such a straightforward primary key lookup.

would I be getting the benefits of a prepared statement?

I doubt so.

If such a place ever be a bottleneck (the only reason to bother with whatever optimizations, mind you), it wouild be better to select all the data in one query.

Using a mySQL Prepared Statement in Multiple Class Instances

2 Answers2