This is the next article about PDO in PHP and I am going to cover query execution in PDO with the example. If you are not sure what was covered in the previous article yet then you can have a look at the previous article about PDO Introduction and connection.
How to execute query in PDO?
There are two ways to perform query using PDO:
1. Query() function
PDO allows you to query directly.If the request does not use any variable, you can use the query() function. It will perform the query and return the PDO object.PDO object is kind of mysql resource.
When you use query() function to insert query parameters, query evaluation must be carried out manually, query() method is not much used by developers, They prefer to use prepare() and execute () statement as it is more convenient and query implementation is executed optimally.
Example:
1 2 3 | $result = $db->query($sql_query); |
2. Prepare statement
PDO provides a convenient mechanism for building queries using prepared statements. Many database programming interfaces provide ways of using prepared statements. A prepared statement is used to execute the same statement repeatedly with high efficiency. Also, Prepared statements can improve query performance. If the query request is using at least one variable, then the request must be carried out only in prepared statements.
Prepared statements comes with two major advantages:
1. If you are performing the same statement repeatedly such as to insert many rows into a table so there is a significant and unnecessary overhead associated with repeatedly performing the query. By using the prepared statement, The query statement does not have to be prepared each time, only once but execute multiple times with same or different values so prepared statements will use fewer resources and runs faster.
2. You don’t need to use any escape quote the parameters to prepared statements, PDO automatically handles this.It will automatically escape values within the server and it is considered as a security feature to prevent SQL injection.
The prepared statement execution consists of two stages: prepare and execute. prepare () and execute () can be used to repeatedly perform once prepared the query with different sets of data.
You can use the placeholders in the query to replace different values in the prepare statement. Prepare statement have two types of placeholders:
- Named Placeholder
- Positional Placeholder
1.Named Placeholder
Prepare statement is followed by execute() method. During execute, it binds parameter values and sends them to the server. The server creates a statement from the template and the bind the values to execute it using the previously created internal resources.
Let us proceed with an example to understand prepared statement.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <?php $query = $dbh->prepare('insert into tbl_name (name, address) values (:name, :address)'); //Following lines will bind the php variable to the prepared statements. $query->bindParam(':name',$name); $query->bindParam(':address',$address); // Insert First Record $name = 'Bhumi'; $address = 'India'; $query->execute(); // Insert second record $name ='Creativedev'; $address = 'Canada'; $query->execute(); ?> |
bindParam binds a query parameter to a variable and runs execute() method.bindParam allows you to set the parameters.You can use this query with any number of different parameters and times and to carry it out, just call the method execute. This example was named parameters.
2. Position Placeholder
PDO support positional placeholder with ? (question mark).
Now, I will show you an example using the placeholder(?) in PDO.Prepared statements use a question mark character (?) instead of variable for your SQL query as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?PHP $query = $dbh->prepare('insert into tbl_name (name, address) values (?, ?)'); // Following lines will bind the variable with the prepared statements. $query->bindParam(1,$name); $query->bindParam(2,$address); // Insert First Record $name ='Bhumi'; $address ='India'; $query->execute(); // Insert Second Record $name = 'creativedev'; $address = 'Canada'; $query->execute(); ?> |
As you have seen above, the named placeholders in the execute () should be given as an array in which the keys have the same name as placeholders.Whereas, in another method position placeholder method, the variables are first tied to a request by position using bindParam() method.
Here I am ending this article about PDO. Are you using PDO in your application? Let hear your thought in the comment box.
Comments (1)