PHP and MySQL

MySQL is great, but it's not useful for web applications without a way to integrate it with your application logic. This article discusses how to query MySQL from within PHP.

This guide assumes that you already know how SQL queries are structured. If you don't, read MySQL Schema and State first. This guide also assumes that you have a working copy of MySQL. If you don't, read Introduction to MySQL also.

PHP Libraries for Database Manipulation

PHP comes with many different ways of querying databases. This guide instructs you on how to use MySQL Improved Prepared Statements, which is a cleaner, more elegant way to perform queries on your database.

Connecting to a MySQL Database in PHP

To connect to a database using the MySQL Improved interface, use the mysqli command:

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'databasename');

if(mysqli_connect_errno()) {
	echo "Connection Failed: " . mysqli_connect_errno();
	exit;
}
?>

You need to run this code before you can perform any other query on your database. Many PHP web developers put this code in a separate file and require it into other files that require database access.

You should create a new MySQL user with minimal privileges just for PHP to use.

Performing INSERT Queries

The first type of prepared query we will discuss are INSERT queries.

Suppose you had a form that submitted the following information:

• first, the user's first name
• last, the user's last name
• dept, the user's department

You could insert data received from the form into the employees database using the following PHP code:

<?php
require 'database.php';

$first = $_POST['first'];
$last = $_POST['last'];
$dept = $_POST['dept'];

$stmt = $myqli->prepare("insert into employees (first_name, last_name, department) values (?, ?, ?)");
if(!$stmt){
	echo "Query Prep Failed: ".$mysqli->error;
	exit;
}

$stmt->bind_param('sss', $first, $last, $dept);

$stmt->execute();

$stmt->close();

?>

The query that will end up being executed by the above code is:

insert into employees (first_name, last_name, department) values ('John', 'Deere', 'EECE')

Notice how the MySQLi interface uses object-oriented PHP. Refer to the PHP guide if you need a refresher on object-oriented PHP.

bind_param()

The most tricky line in the above example is the bind_param line. Here's how it works.

The first parameter defines the data types of data to be inserted into the query. It is a string with one character per data type. The possible data types are:

• i - Integer
• d - Decimal
• s - String
• b - Blob

Note that data types in queries are not the same as data types in schemas. For instance, when saving a date into MySQL, you need to use a string representation of the data (except for timestamp, which uses an integer), so you would use the s option.

The remaining parameters define values associated with each of the question marks in your query.

In the example above, there are three question marks in the query, all of which are strings, and so the first argument to bind_params is 'sss'. The remaining three parameters is the information itself to be "injected" into the query.