Web Development: How to Connect PHP to MySQL

How to Connect PHP to MySQL

Do you want ot learn how to connect php to MySQL and perform different types of database operations? In this article, we'll do just that—we'll discuss MySQL database connectivity in PHP.

PHP provides different ways to connect PHP to a MySQL database server. Until PHP 5.5, one of the most popular ways was with the MySQL extension—it provided a "procedural" way to connect to the MySQL server. However, this extension is deprecated as of PHP 5.5, so we’re not going to discuss that.

The current two ways you can choose from are PDO and MySQLi. The PDO (PHP Data Objects) extension supports different types of underlying database servers along with the MySQL server. And thus, it’s portable in case you decide to swap the underlying database server at any point in the future. On the other hand, the MySQLi extension is specific to the MySQL server and provides better speed and performance. The MySQLi extension might also be a little simpler to understand at first, since it lets you write SQL queries directly. I've you've worked with SQL databases before, this be very familiar. On the other hand, the PDO extension creates a powerful mapping from the SQL database to your PHP code and lets you do a lot of database operations without needing to know the details of SQL or the database. In the long run and for larger projects, this can save a lot of coding and debugging effort.

In this post, I'll use the MySQLi extension. But you can learn more about the PDO extension here on Envato Tuts+.

PHP

Quickly Build a PHP CRUD Interface With the PDO Advanced CRUD Generator Tool

Sajal Soni

We’ll cover the following topics in this article:

creating a MySQL connection
inserting, updating and deleting Records
retrieving records

Best PHP Database Scripts on CodeCanyon

In this post, I'll be showing you how to make a bare-metal connection to a MySQL database from PHP. This is an important skill to have, but if you want to save time on your next project, you might want to upgrade to a professional database script. This will save time and make your work easier.

Explore the best and most useful PHP database scripts ever created on CodeCanyon. With a low-cost one time payment, you can purchase these high-quality WordPress themes and improve your website experience for you and your visitors.

Here are some of the best PHP database scripts available on CodeCanyon for 2020.

PHP

Comparing PHP Database Abstraction Layers and CRUD Plugins

Sajal Soni

Create a MySQL Connection

In this section, I'll show you how you can connect to the MySQL server from your PHP script and create a connection object. Later on, this connection object will be used to run queries, fetch output and manipulate the database records. As we discussed earlier, we’re going to use the PHP MySQLi extension for this article.

The MySQLi extension provides two different ways to perform database operations. You can use it either the object-oriented way or the procedural way, but as this tutorial is aimed at beginners, I'll stick with the procedural way. If you're interested in seeing some object oriented snippets, you can ping me in the comment section below and I’ll be happy to provide them.

To start, go ahead and create the db_connect.php file under your document root with the following contents.

<?php
$mysqli_link = mysqli_connect("{HOST_NAME}", "{DATABASE_USERNAME}", "{DATABASE_PASSWORD}", "{DATABASE_NAME}");

if (mysqli_connect_errno()) 
{
    printf("MySQL connection failed with the error: %s", mysqli_connect_error());
    exit;
}

Note that I've used placeholders in the mysqli_connect function—you have to replace these with actual values.

Let’s go through the each placeholder to see what it stands for.

{HOST_NAME}: This represents your MySQL server’s host-name or IP address. If you have installed the MySQL server on the same system along with PHP, you should use localhost or 127.0.0.1. On the other hand, if you’re using a MySQL server which is hosted externally, you can use the corresponding host-name or IP address.
{DATABASE_USERNAME}: This represents the username of your MySQL user. Basically, this is the username which you use to connect to your MySQL server.
{DATABASE_PASSWORD}: This represents the password of your MySQL user. Again, this is the password which you use to connect to your MySQL server along with the MySQL username.
{DATABASE_NAME}: This is a name of the MySQL database which you want to connect to. Once the connection is created, you'll query this database for further operations.

Go ahead and replace these placeholders with actual values. In my case, I’ve installed the MySQL server locally, and I’ve a MySQL user with tutsplus-demo-user as the MySQL username and tutsplus-demo-password as the MySQL password. And with that, the above example looks like this:

<?php
$mysqli_link = mysqli_connect("localhost", "tutsplus-demo-user", "tutsplus-demo-password", "tutsplus-demo-database");

if (mysqli_connect_errno()) 
{
    printf("MySQL connection failed with the error: %s", mysqli_connect_error());
    exit;
}

If you run this example, it should create a database connection link and assign it to the $mysqli_link variable, which we’ll use later on to perform different types of database operations.

On the other hand, if there’s any problem setting up the connection, the mysqli_connect_errno function will return an error code and the mysqli_connect_error function will display the actual error. In the above example, we’ve used it for debugging purposes.

Now, we’ve a successful connection to the MySQL server and we’ll see how to use it to perform different types of queries next section on wards.

How to Insert Records

In the previous section, we discussed how to use the mysqli_connect function to setup a database connection with the MySQL server. In this section, we’ll go ahead and discuss how to use the connection object to perform the INSERT queries.

If you want to follow along with the examples discussed in this article, you'll need to create the following MySQL table in your database. It's the table which we're going to use in all the examples from now on.

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Go ahead and create the above MySQL table by using the phpMyAdmin software or command line tool.

Next, let’s create the db_insert.php file with the following contents. Please don’t forget to replace the connection parameters with your own.

<?php
$mysqli_link = mysqli_connect("localhost", "tutsplus-demo-user", "tutsplus-demo-password", "tutsplus-demo-database");

if (mysqli_connect_errno())
{
    printf("MySQL connection failed with the error: %s", mysqli_connect_error());
    exit;
}

$insert_query = "INSERT INTO students(`first_name`,`last_name`,`email`)
VALUES ('". mysqli_real_escape_string($mysqli_link, 'John') ."','". mysqli_real_escape_string($mysqli_link, 'Wood') ."','". mysqli_real_escape_string($mysqli_link, 'john.wood@tutsplus.com') ."')";
 
// run the insert query
If (mysqli_query($mysqli_link, $insert_query)) {
    echo 'Record inserted successfully.';
}
 
// close the db connection
mysqli_close($mysqli_link);
?>

First, we set up a database connection by using the mysqli_connect function as we discussed earlier. After that, we’ve prepared the insert query which we’re going to execute later on. It's important to note that we've used the mysqli_real_escape_string function to escape string values that we’re going to use in the insert query. Specifically, you must use this function when you’re dealing with values submitted via $_POST variables to avoid SQL injection.

Finally, we’ve used the mysqli_query function, which takes two arguments. The first argument is the active connection link where the query will be executed. And the second argument is the MySQL query which we want to execute. The mysqli_query function returns TRUE if the query was executed successfully.

Finally, we’ve used the mysqli_close function to close the active database connection. It’s a good practice to close the database connection once you’re done with database operations.

Go ahead and run the script, and that should insert a record in the students table!

How to Update Records

Updating the records in a database from the PHP script is very similar to the insert operation with the only difference is that the query is going to be the update query instead of the insert query.

Let’s revise the above example and update the first_name field as shown in the following example.

<?php
$mysqli_link = mysqli_connect("localhost", "tutsplus-demo-user", "tutsplus-demo-password", "tutsplus-demo-database");

if (mysqli_connect_errno())
{
    printf("MySQL connection failed with the error: %s", mysqli_connect_error());
    exit;
}

$update_query = "UPDATE students SET `first_name` = '". mysqli_real_escape_string($mysqli_link,'Johnny') ."' WHERE `email` = 'john.wood@tutsplus.com'";
 
// run the update query
If (mysqli_query($mysqli_link, $update_query)) {
    echo 'Record updated successfully.';
}
 
// close the db connection
mysqli_close($mysqli_link);
?>

Go ahead and run the script, and that should update the record in the students table.

How to Select Records

In the earlier sections, we discussed how you can insert and update records in a database from the PHP script. In this section, we’ll explore how you can fetch records from a database by using the different types of MySQLi functions.

Firstly, you need to use the mysqli_query function to execute the select query. Upon successful execution of the select query, the mysqli_query function returns the mysqli result object which we could use to iterate over the records returned by the select query. When it comes to fetching and iterating over the records from the MySQLi result object, there are different functions available.

mysqli_fetch_all: It allows you to fetch all result rows at once. You can also specify whether you want results as an associative array, a numeric array or both.
mysqli_fetch_array: It allows you to retrieve one row at a time. And thus, you’ll have to use the while loop to iterate over all the records. Again, you can specify whether you want a result row as an associative array, a numeric array or both.
mysqli_fetch_assoc: It fetches a result row one at a time as an associate array.
mysqli_fetch_object: It fetches a result row one at a time as an object.

Let’s have a look at the following example to understand how it works:

<?php
$mysqli_link = mysqli_connect("localhost", "tutsplus-demo-user", "tutsplus-demo-password", "tutsplus-demo-database");

if (mysqli_connect_errno())
{
    printf("MySQL connection failed with the error: %s", mysqli_connect_error());
    exit;
}

$select_query = "SELECT * FROM students LIMIT 10";
$result = mysqli_query($mysqli_link, $select_query);

while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    echo "First Name:" . $row['first_name'] . "<br/>";
    echo "Last Name:" . $row['last_name'] . "<br/>";
    echo "Email:" . $row['email'] . "<br/>";
    echo "<br/>";
}

// close the db connection
mysqli_close($mysqli_link);
?>

As you can see, we’ve used the mysqli_fetch_array function with the MYSQLI_ASSOC option as a second argument. And thus, it returns the result row as an associate array. Had you would have used the MYSQLI_NUM option, you would have accessed it like $row[0], $row[1] and $row[2] in the above example.

On the other hand, if you would use the mysqli_fetch_object function in the above example, you can access the values as shown in the following snippet. For brevity, I’ll only include the while loop snippet.

…
…
while ($row = mysqli_fetch_object($result)) {
    echo "First Name:" . $row->first_name . "<br/>";
    echo "Last Name:" . $row->last_name . "<br/>";
    echo "Email:" . $row->email . "<br/>";
    echo "<br/>";
}
...
...

So in this way, you can fetch and display records from the MySQLi database.

How to Delete Records

In this section, we’ll see how to run delete queries from the PHP script. Deleting records from a database is a pretty straightforward operation since you just need to prepare the delete query and run it with the mysqli_query function.

Let’s go through the following example to see how it works.

<?php
$mysqli_link = mysqli_connect("localhost", "tutsplus-demo-user", "tutsplus-demo-password", "tutsplus-demo-database");

if (mysqli_connect_errno())
{
    printf("MySQL connection failed with the error: %s", mysqli_connect_error());
    exit;
}

$delete_query = "DELETE FROM students WHERE `email` = 'john.wood@tutsplus.com'";
 
// run the update query
If (mysqli_query($mysqli_link, $delete_query)) {
    echo 'Record deleted successfully.';
}
 
// close the db connection
mysqli_close($mysqli_link);
?>

As you can see, everything is the same as we’ve seen it already with insert and update examples except that the query is a delete query in this case.

So that's how you can perform different types of database operations from the PHP script.

And with that, we’ve reached the end of this tutorial. What we’ve discussed so far today should help you to strengthen database connectivity concepts, specifically how to connect PHP to MySQL and perform different types of operations.

Conclusion

In this article, we discussed how to connect PHP to MySQL database by using the MySQLi extension. From setting up a database connection to executing different types of queries, we discussed almost every aspect of the database connectivity.

Although I’ve tried to keep things as simple as possible, if you still find anything confusing or want to know any specific thing in detail, feel free to post your queries using the feed below.

The Best PHP Scripts on CodeCanyon

Explore thousands of the best and most useful PHP scripts ever created on CodeCanyon. With a low-cost one time payment, you can purchase these high-quality WordPress themes and improve your website experience for you and your visitors.