JIYIK CN >

In this article, we will explore how to perform CRUD (Create, Read, Update, and Delete) operations using a MySQL database with PHP. If you want to get hands-on experience with database connections in PHP, this article is a good starting point.

If you are just starting to use PHP, you might realize that database connection is a basic feature that you need to get familiar with sooner or later. In most cases, the database is the backbone of any web application and holds the data of the application. Therefore, as a PHP developer, you need to know how to handle database operations.

In this article, we'll keep it simple and explore how to use the core mysqlifunctions. In subsequent articles in this series, we'll explore several other ways to handle database connections.

Today, we will learn the basics of database connectivity in PHP, and we will use MySQL database as our database backend. Let's take a quick look at what we will cover in this article:

• How to establish a database connection
• How to choose a database
• How to insert and update records
• How to get records
• How to delete records

I assume that you have already installed PHP and MySQL and understand the basics of MySQL. In addition, we need to make sure that the mysqli extension is enabled in your PHP installation because we will use it to perform database operations on the MySQL database.

If you are unsure about the mysqli extension, you can phpinfo()check it using the mysqli_support function. In phpinfo()the output, we can check if there is a section titled mysqli. If the mysqli extension is enabled, you should also see Mysqli Support | enabled .

There is also an easy way to check it using the CLI interface. We can run the following command to list all the extensions that PHP is set to enable.

$ php -m

It should print a list of extensions, and if it contains mysqlithe keyword, the mysqli extension is enabled.

Now that we have that set up, let's start creating the database connection.

How to establish a database connection

In this section, we will discuss how to establish a database connection using the mysqli extension.

There are two ways to use the mysqli extension, procedural and object-oriented, but we will use the procedural way in this article to keep things simple. If you are curious about the object-oriented syntax, let me know your questions in the comments section and I will be happy to answer them.

First, let's look at mysqli_connectthe syntax of the function, which is used to establish a connection to a MySQL backend.

$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}");
var_dump($connection_obj);

mysqli_connectThe function takes four parameters and returns a connection object if the connection is successful. Let's take a look at each parameter:

• {MYSQL_HOSTNAME}: This should be replaced with the hostname or IP address of the MySQL server. If you are using the MySQL server in your local system, you can use localhost or 127.0.0.1.
• {MYSQL_USERNAME}: The username of your MySQL user. The username defaults to root.
• {MYSQL_PASSWORD}: The password for your MySQL user. By default, the password is blank (i.e. no password).
• {MYSQL_DATABASE}: The database you want to connect to.

After the connection is successful, $connection_objthe connection object is included. The following figure shows $connection_objthe various properties of the object.

With this in place, we can {MYSQL_DATABASE}run queries against the database supplied to the parameter.

On the other hand, if the connection is unsuccessful, we can debug it as shown in the following code snippet:

$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}");
var_dump($connection_obj);
// 如果连接不成功：
if (!$connection_obj) {
    echo "Error No: " . mysqli_connect_errno();
    echo "Error Description: " . mysqli_connect_error();
    exit;
}

In the next section, we will see how to use the connection object to select a specific database.

How to choose a database

In the previous section, we discussed how to use mysqli_connectthe function to establish a database connection. In this section, we will see how to select a database after a successful MySQL connection.

Of course, as we've seen, we can always mysqli_connectpass a database in the fourth argument of the connect function itself. However, if we want to change the database after connecting to MySQL, there is another way to do it. We can use mysqli_select_dbthe connect function to select the database to use.

Let's modify the example discussed in the previous section to see how it works.

$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}");
var_dump($connection_obj);
// 如果连接不成功：
if (!$connection_obj) {
    echo "Error No: " . mysqli_connect_errno();
    echo "Error Description: " . mysqli_connect_error();
    exit;
}
mysqli_select_db($connection_obj, "{MYSQL_DATABASE}");

As you can see, we only mysqli_connectpassed three parameters in the function, which should provide us with a successful database connection. We did not pass the database in the fourth parameter, but instead mysqli_select_dbselected the database using the function.

mysqli_select_dbThe function takes two parameters: the connection object and the database to connect to.

How to create (insert) and update records

In the previous section, we discussed how to set up a MySQL connection and choose a database to use. Now, we will look at how to perform different types of queries against the selected database. First, let's look at how to insert and update records.

If we want to follow the examples discussed in this article, we need to create the following MySQL table in our database. We will use this table in all the examples from now on.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

How to create a record

For simplicity we will initialize the values ​​that need to be inserted into the employee table at the beginning of the script. However, in most cases these will come from $_POSTuser input in variables submitted using a form.

$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}");
if (!$connection_obj) {
    echo "Error No: " . mysqli_connect_errno();
    echo "Error Description: " . mysqli_connect_error();
    exit;
}
// initialize variables for the insert query 
$name = 'jiyik';
$email = 'jiyik.onmpw@163.com';
$phone = '12345678900';
// prepare the insert query 
$query = "INSERT INTO employee(`name`,`email`, `phone`) 
VALUES ('". mysqli_real_escape_string($connection_obj, $name) ."','". mysqli_real_escape_string($connection_obj, $email) ."','". mysqli_real_escape_string($connection_obj, $phone) ."')";
// run the insert query 
mysqli_query($connection_obj, $query);
// close the db connection 
mysqli_close($connection_obj);

In the above script, we first initialized the variables that we will use in our insert query. Next, we prepared the insert query and assigned it to $querythe variables.

It is important to note that we have used mysqli_real_escape_stringthe function to escape the string value that we will use in our insert query. $_POSTYou must use this function when you are dealing with string values ​​submitted via the variable. This ensures that users cannot inject malicious code into our database queries.

Finally, we mysqli_queryrun the insert query using the function. mysqli_queryThe function is used to run queries against the active database. Finally, we mysqli_closeclose the active connection using the function.

How to Update a Record

In the previous section, we discussed how to insert new records in a MySQL table. In this section, we will see how to update the existing records in a table.

I assume that a record already exists in the employee table and the id of that record is 1. In the following example, we will update the record whose value in the id column is 1.

$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}");
if (!$connection_obj) {
    echo "Error No: " . mysqli_connect_errno();
    echo "Error Description: " . mysqli_connect_error();
    exit;
}
// initialize variables for the insert query 
$id = 1;
$phone = '12345678901';
// prepare the insert query 
$query = "UPDATE employee SET `phone` = '". mysqli_real_escape_string($connection_obj, $phone) ."' WHERE `id` = '". (int) $id ."'";
// run the insert query 
mysqli_query($connection_obj, $query);
// close the db connection 
mysqli_close($connection_obj);

As we can see in the above example, we want to update the phone column in the employee table for the id 1. This is very similar to the insert query, except that we are using instead UPDATEof INSERT.

How to retrieve records

So far, we have discussed how to insert and update records in a MySQL table. In this section, we will see how to retrieve (get) records from a MySQL table.

Take a look at the following example.

$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}");
if (!$connection_obj) {
    echo "Error No: " . mysqli_connect_errno();
    echo "Error Description: " . mysqli_connect_error();
    exit;
}
// prepare the select query 
$query = "SELECT * FROM employee";
// execute the select query 
$result = mysqli_query($connection_obj, $query) or die(mysqli_error($connection_obj));
// run the select query 
while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
    echo "ID:" . $row['id'] . "<br/>";
    echo "Name:" . $row['name'] . "<br/>";
    echo "Phone:" . $row['phone'] . "<br/>";
    echo "Email:" . $row['email'] . "<br/>";
    echo "<br/>";
}
// close the db connection 
mysqli_close($connection_obj);

In order to fetch records from the database, the first thing we need to do is mysqli_queryto perform a select query using the function. Upon successful execution of the select query, mysqli_querythe function returns a mysqli result object, which is what we stored in the $result variable above.

Next, we iterate over the result set using the function in a while loop mysqli_fetch_array. mysqli_fetch_arrayThe function fetches one row at a time from the mysqli result set.

mysqli_fetch_arrayThe function takes two parameters: result object and result type. Result type indicates mysqli_fetch_arraythe type of array that will be returned from the function. It can be numeric, associative, or both. This is an optional parameter and we can pass one of three values: MYSQLI_ASSOC , MYSQLI_NUM , or MYSQLI_BOTH . MYSQLI_ASSOC means you want to access the column by name and MYSQLI_NUM means you want to access the column by column number.

If we pass the MYSQLI_NUMmysqli_fetch_array value in the second argument of the _SYS_CONTENT function , we can access the column in _SYS_CONTENT , _SYS_CONTENT , etc. In our case, we passed the MYSQLI_BOTH value, so we can access the column in both ways. If you want to access only the associated value, you can use the _SYS_CONTENT function instead.$row[0]$row[1]mysqli_fetch_assoc

In the next and final section, we will see how to delete records from the database.

How to delete records

In this section, we will see how to delete records from the database.

Take a look at the following example.

$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}");
if (!$connection_obj) {
    echo "Error No: " . mysqli_connect_errno();
    echo "Error Description: " . mysqli_connect_error();
    exit;
}
// initialize variables for the delete query 
$id = 1;
// prepare the insert query 
$query = "DELETE FROM employee WHERE `id` = '". (int) $id ."'";
// run the delete query 
mysqli_query($connection_obj, $query);
// close the db connection 
mysqli_close($connection_obj);

As you can see, it works almost the same as the insert and update queries. mysqli_queryThe function executes a DELETE query, which ultimately deletes the records from the employee table.

Summarize

In this article, we discussed how to use MySQL database with PHP. This article aims to provide beginners with the basics of MySQL database connectivity in PHP. We saw how to start from establishing a database connection to executing different types of queries.