Which should I be using?

[3raser]

I'm really starting to get use to PDO and some of its a basic methods and features. I made a small method in my database class that would run a query like so:

 

public function processQuery($query, array $binds, array $assign)
    {
        $query_handle = $this->dbc->prepare($query);
        $i = 0;
        
        foreach($binds as $bind)
        {
            $query_handle->bindParam($bind, $value);
            $value = $assign[$i];
            $i++;
        }
        
        $query_handle->execute();
    }

 

Which would then be ran on any of my webpages like so:

 

$database->processQuery("INSERT INTO test (id, message) VALUES (null, :message)", array(':message'), array('rofldslgkdfsklgj'));

 

It works. But my question is, should I be using this instead:

 

public function insert($query)
    {
        $this->dbc->exec($query);
    }

 

Which would then be called like so:

 

$database->insert("INSERT INTO test (id, message) VALUES ('lol', 'roflaaaaaaaaaaaa')");

 

 

=========

 

My question is: Which would should I use/which is more efficient? Or is it just preference?

[kicken]

Unless your query is static or does not depend on user input in any way, then you should be using prepare/execute and bound parameters as in your processQuery function.  It will protect you from any injection attempts and make query handling easier.

 

That said, your processQuery method is not going to work when you have multiple parameters/binds.  All your fields will end up with the same value (the last value) being bound to them since your binding them all to the same $value variable.  bindParam is assigns the variable as a reference so the value of the variable is not taken/used until your ->execute() call is made.

 

You can either use bindValue which will assign the value normally rather than by reference, or make sure you use separate variables for each bind you do.

 

 

[3raser]

Unless your query is static or does not depend on user input in any way, then you should be using prepare/execute and bound parameters as in your processQuery function.  It will protect you from any injection attempts and make query handling easier.

 

That said, your processQuery method is not going to work when you have multiple parameters/binds.  All your fields will end up with the same value (the last value) being bound to them since your binding them all to the same $value variable.  bindParam is assigns the variable as a reference so the value of the variable is not taken/used until your ->execute() call is made.

 

You can either use bindValue which will assign the value normally rather than by reference, or make sure you use separate variables for each bind you do.

 

Thanks for this reply.

 

A few questions, though. How come I shouldn't be using the prepare method when it comes to non-static queries? Isn't the point of the prepare statement to filter/clean it? How come that shouldn't be used with the prepare and bind methods?

 

=============================

 

Also, about my processQuery method. I don't what the problem is when trying to add multiple binds. Any help on this?

 

I tried modifying my code to this:

 

$database->processQuery("INSERT INTO test (id, message) VALUES (null, :message, :title)", array(':message', ':title'), array('rofldslgkdfsklgj', 'tis a title'));

 

public function processQuery($query, array $binds, array $assign)
    {
        $query_handle = $this->dbc->prepare($query);
        $i = 0;
        
        foreach($binds as $bind)
        {
            $query_handle->bindParam($bind, $value[$i]);
            $value[$i] = $assign[$i];
            $i++;
        }
        
        $query_handle->execute();
    }

 

No error is outputted, so... :/

[kicken]

A few questions, though. How come I shouldn't be using the prepare method when it comes to non-static queries? Isn't the point of the prepare statement to filter/clean it? How come that shouldn't be used with the prepare and bind methods?

 

Not sure what your asking there.  I said you should be using prepare and bind for your queries if they are not static.  Even if they are static queries you could still use prepare if you wanted.

 

 

[3raser]

A few questions, though. How come I shouldn't be using the prepare method when it comes to non-static queries? Isn't the point of the prepare statement to filter/clean it? How come that shouldn't be used with the prepare and bind methods?

 

Not sure what your asking there.  I said you should be using prepare and bind for your queries if they are not static.  Even if they are static queries you could still use prepare if you wanted.

 

Unless your query is static or does not depend on user input in any way, then you should be using prepare/execute and bound parameters as in your processQuery function.