Introduction
The Mysqli class is a great help to working towards proper security when sending and pulling information from the database. It can stop injection attacks in their tracks and will allow you to specify data types allowing you to not let anything bad slip into the system.
In this article I will be going through the step by step approach to creating a query using prepared statements through MySQL. We are going to follow best practices and use the object oriented style to achieve a connection and gather our results.
Connecting to the database
The first thing that we will be doing is connecting to the database. Traditionally this should be done using an outside class to prevent an access to the connection information. The connection string will attempt to connect to the database and open up a persistent connection.
1 | $this->connection = new mysqli('localhost','username','password','database'); |
Creating the prepared statement
The next thing that is required is for us to store the prepared statement. This should be attempted inside of an if statement. This way we can handle any errors appropriately.
1 2 3 4 5 6 7 8 9 10 | $sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?"; //store the prepared statement into the $stmt variable. //note that the $stmt is a common naming for prepared statements. if($stmt = $this->connection->prepare($sql)) { }else{ die(trigger_error($this->connection->error)); } |
Wild cards
You probably noticed that the sql code has something new inside of it. This is the wild card for the sql statement and a ‘?’ is used to display it. This will allow us to bind variables in place of these questions marks and specify their data type.
Bind parameters
Bind parameters is the next step that takes place when there are wild cards inside of the sql statement. These allow us to force a data type into the format of our choosing. There are 4 data types that we can use.
‘I’ Integer
‘s’ String
‘d’ Decimal
‘b’ Blob
1 2 3 4 5 6 7 8 9 10 11 | $sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?"; //store the prepared statement into the $stmt variable. //note that the $stmt is a common naming for prepared statements. if($stmt = $this->connection->prepare($sql)) { //Now bind the data to the types. $stmt->bind_param('sii', $bookName, $bookYear, $limit); }else{ die(trigger_error($this->connection->error)); } |
The above function that we just used will take a minimum of two arguments, the first to define the variable types and everything after that is a variable. Notice that all of the type declarations are in the same string. A common error for this piece of code is that you have an invalid amount of arguments being passed. Another error commonly given is that you cannot have data placed directly in the string. You need to reference variables.
1 2 | //This is not allowed: $stmt->bind_param('sii',"the php book", 4, 20); |
Execution
Once we have properly set up our bindings we can execute the prepared statement and try to get a result from the database. As you can see in the code below; this can fail and an error message will be stored in case it does. This is why we are going to have it placed inside of an if statement.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | $sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?"; //store the prepared statement into the $stmt variable. //note that the $stmt is a common naming for prepared statements. if($stmt = $this->connection->prepare($sql)) { //Now bind the data to the types. $stmt->bind_param('sii', $bookName, $bookYear, $limit); //We will now execute the statement. This will throw an error if unsuccessful. if($stmt->execute()) { }else{ die(trigger_error($stmt->error)); } }else{ die(trigger_error($this->connection->error)); } |
Number of Rows
The next step in our quest for world domination is to store the results inside of the statement variable. This is going to let us make a call for the number of rows so we can handle the case of no rows being returned.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | $sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?"; //store the prepared statement into the $stmt variable. //note that the $stmt is a common naming for prepared statements. if($stmt = $this->connection->prepare($sql)) { //Now bind the data to the types. $stmt->bind_param('sii', $bookName, $bookYear, $limit); //We will now execute the statement. This will throw an error if unsuccessful. if($stmt->execute()) { //check the number of rows. $stmt->store_result(); if($stmt->num_rows >= 1) { }else{ echo 'Nothing was returned from the database.'; } }else{ die(trigger_error($stmt->error)); } }else{ die(trigger_error($this->connection->error)); } <pre> <h2>Binding Results to Variables</h2> The last thing that is required is binding the results and looping through the results. As you can see we are actually creating variables with this function and storing the information into them. The variables created are gathered from the select part of the statement. This means that we need to define the variables in the same order as the sql statement. <pre lang="PHP" line="1"> $sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?"; //store the prepared statement into the $stmt variable. //note that the $stmt is a common naming for prepared statements. if($stmt = $this->connection->prepare($sql)) { //Now bind the data to the types. $stmt->bind_param('sii', $bookName, $bookYear, $limit); //We will now execute the statement. This will throw an error if unsuccessful. if($stmt->execute()) { //check the number of rows. $stmt->store_result(); if($stmt->num_rows >= 1) { //bind the results $stmt->bind_result($id, $name, $year); //loop through the results. while($stmt->fetch()) { echo "$name: $year<br>"; } }else{ echo 'Nothing was returned from the database.'; } }else{ die(trigger_error($stmt->error)); } }else{ die(trigger_error($this->connection->error)); } |
It is now time to run your code and gather the results using the fetch function. If you only have to return one row from the database then you can just use the fetch function outside of the while loop and it will allow you to pull the results and use the variables in the same way you would in the while loop.