Writing mysql prepared statements

Database connection

First of all we have to connect to the database so we can perform our queries.
We are going to use a mysqli object to do so.

$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');
if($mysqli->connect_errno != 0){
	echo $mysqli->connect_error;
	exit();
}

• In line 1 we creating a new mysqli object, and we pass in the constructor our server and database credentials.
• In line 2-5 we perform a check. We are checking if the connection error is NOT zero, then we echo out the connection error message and we exit (stopping the execution) the script.

Select data

Let's see how to prepare a statement so we can select and compare data from a database table.
In this example let's say we have a login form and the user sends us the username and password.

So in line 8 we have the user's username, and in line 9 we have the user's password.

// Incoming data from a login form.
$username = "Dennis";
$password = 1234;

$stmt = $mysqli->prepare("SELECT username, password FROM users WHERE username = ? and password = ?");
$stmt->bind_param("si", $username, $password);
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_assoc();

• Let's begin with the username and password and see the data type's. We see that the $username is a string, and the $password is an integer. We have to know the input data types so we can bind them. You will see that when we get in line 12.

• In line 11 we use the prepare() method to send the query to the database.

  Notice carefully the query structure and you see that instead of the $username and $password variables, we are using question marks (?) ... WHERE username = ? and password = ?.

  Those question marks (?) are placeholders which we are going to bind with the $username and $password variables.

• In line 12 we use the bind_param() method to bind the $username and $password with the question marks (?)

  Lets take a look at the arguments that we are passing in the method. ("si", $username, $password).

  In the first argument "si" we are declaring the $username's and $password's data types. The letters must be inside quotes and there is no space between them. So here we are saying that the first binding parameter is a string (username), and the second binding parameter is an integer (password).

• For a string data type we use the letter "s".
• For an integer data type we use the letter "i".
• For a double (float) data type we use the letter "d".
• For a blob data type we use the letter "b".
• The next two arguments are the $username and $password. The order here matters. The first argument will be bound to the first question mark, and the second to the second question mark.
• And in line 13 we use the execute() method to run the query.

• If we use prepared statements with a SELECT query, we have to use the get_result() method to fetch the data as we see in line 14. The $result variable holds the returned result set.

  $result = $stmt->get_result();

• Now we have to extract the rows and columns from the result set. To do so we are going to use the fetch_assoc() method and fetch the data as an associative array.

  $data = $result->fetch_assoc();

  This is what the $data variable holds.

  array (
    'username' => 'Dennis',
    'password' => '1234',
  );
  			

Insert data

Now we are gonna see how to execute a prepare statement in an INSERT query.
In line 8 and 9 we have a new user that we want to insert in the database. The username is "Bob" and the password is "4567".

$username = "Bob";
$password = 4567;

$stmt = $mysqli->prepare("INSERT INTO users(username, password) VALUES(?,?)");
$stmt->bind_param("si", $username, $password);
$stmt->execute();

• In line 11 we use again the prepare() method, and we pass in the insert query. And as we did in the previous example with the select query, we use the question marks (?) instead of the $username and the $password variables VALUES(?,?)
• In line 12 we bind the variables to the question marks, and in line 13 we execute the query.
• That's all it is to prepare and execute an insert query.

We can use the execute() method repeatedly to insert as many records we like. Of course we have to assign to our variables new values. But we can't change the name of the variables.

We can do that because as i said in the introduction, the MySQL database stores the prepare statement and waits for the execute() method. And because of that we can call as many times we want the execute() method and the MySQL server will execute it.

$username = "Toni";
$password = 3434;
$stmt->execute();

$username = "Peter";
$password = 9999;
$stmt->execute();

We can also loop through an array in insert a large amount of users very easily. I have here only three users to insert, but you get the point.

$users = array(
	"Bob" => 4567,
	"Toni" => 8888,
	"Jennifer" => 4701
);

foreach ($users as $key => $value) {
	$username = $key;
	$password = $value;
	$stmt->execute();
}

• Every time we iterate through the users array, we call the execute() method and we insert the user in the database.

Update data

The only thing that changes in an update prepared query (from an insert query), is the query string inside the prepare() method. Here we are going to update Bob's password.

$username = "Bob";
$password = 9999;

$stmt = $mysqli->prepare("UPDATE users SET password = ? WHERE username = ?");
$stmt->bind_param("is", $password, $username);
$stmt->execute();

• In line 11 we use again the prepare() method, with the update query string, and as we did in the previous examples we use question marks (?) instead of the variables.
• In line 12 we bind the variables to the question marks, but we know that the order matters, so here the data type argument is set to "is", that means that we have first the integer (password) and then the string (username).
• And in line 13 we execute the statement.

Summary

We saw how to use MySQL prepared statements to SELECT, INSERT, and UPDATE data in a database table. We saw how to use question marks (placeholders) instead of variables and send the query to the database with the prepare() method. And we bound the parameters (variables) with the question marks in the right order with the bind_param() method.

We saw also how to fetch data from a select prepared statement with the get_result() method

Last Words

I hope you liked the article, and you find it helpful.
Thanks for reading.

Buy me a coffee