Get Started With CRUD Operations in PHP MySQL Databases

In this article, we're going to explore how you could use a MySQL database to perform CRUD (create, read, update, and delete) operations with PHP. If you want to get your hands dirty with database connectivity in PHP, this article is a great starting point.

If you are just getting started with PHP, you probably realize that database connectivity is an essential feature that you'll need to get familiar with sooner or later. In most cases, a database is the backbone of any web application and holds the data of the application. So, as a PHP developer, you'll need to know how to deal with database operations.

In this article, we'll keep things simple and explore how to use the core mysqli functions. In upcoming articles of this series, we'll explore a couple of other ways to handle database connectivity.

Today, we'll go through the basics of database connectivity in PHP, and we'll use the MySQL database as our database back-end. Let's have a quick look at what we'll cover in this article:

  • how to set up a database connection
  • how to select a database
  • how to insert and update records
  • how to fetch records
  • how to delete records

I assume that you've a working installation of PHP and MySQL, and that you're aware of the basics of MySQL. Also, you'll need to make sure that the mysqli extension is enabled in your PHP installation, since we'll use it to do database operations with the MySQL database.

If you are not sure about the mysqli extension, you can check it using the phpinfo() function. In the phpinfo() output, you can check if there's a section titled mysqli. You should also see the MysqlI Support | enabled header if the mysqli extension is enabled.

There's also an easy way to check it using the CLI interface. You can run the following command to list all extensions that are enabled with your PHP set up.

$php -m

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

Now that we're set up, let's start creating a database connection.

How to Set Up a Database Connection

In this section, we'll discuss how you can establish a database connection using the mysqli extension.

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

Firstly, let's go through the syntax of the mysqli_connect function which is used to set up a connection with the MySQL back-end.

<?php
$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}", "{MYSQL_DATABASE}");
?>

The mysqli_connect function takes four arguments and returns the connection object upon successful connection. Let's go through the each argument:

  • MYSQL_HOSTNAME: This should be replaced with the MySQL server's host-name or IP address. If you're working with the MySQL server in your local system, you can either use localhost or 127.0.0.1.
  • MYSQL_USERNAME: The username of your MySQL user.
  • MYSQL_PASSWORD: The password of your MySQL user.
  • MYSQL_DATABASE: The database that you want to connect to.

Upon successful connection, the $connection_obj contains the connection object. With this, you're ready to run queries against the database which was provided to the MYSQL_DATABASE argument.

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

<?php
$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;
}
?>

In the next section, we'll see how you can select a specific database using the connection object.

How to Select a Database

In the previous section, we discussed how to set up a database connection using the mysqli_connect function. In this section, we'll look at how to select a database once the MySQL connection is successful.

Of course, as we've already seen, you can always pass a database in the fourth argument of the mysqli_connect function itself. But, there's also another way you could do this in case you want to change a database after you've connected to MySQL. You can use the mysqli_select_db function to select a database to work with.

Let's revise the example discussed in the previous section to see how it can work.

<?php
$connection_obj = mysqli_connect("{MYSQL_HOSTNAME}", "{MYSQL_USERNAME}", "{MYSQL_PASSWORD}");

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've passed only three arguments in the mysqli_connect function, and that should give us a successful database connection. Instead of passing a database in the fourth argument, we select the database using the mysqli_select_db function.

The mysqli_select_db function takes two arguments: the connection object and the  database you want to connect to.

How to Create (Insert) and Update Records

In the previous sections, we discussed how to set up a MySQL connection and select a database to work with. Now, we'll look at how to execute different types of queries against the selected database. First, let's look at how to insert and update records.

If you want to follow along with 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 form 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;

Executing the above command in your MySQL database should create the employee table.

How to Create Records

To keep things simple, we'll initialize values that need to be inserted into the employee table in the beginning of the script. However, in most of the cases, these would come from user input in the $_POST variables submitted using a form.

<?php
$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 = 'John Smith';
$email = 'john.smith@yahoo.com';
$phone = '541-754-1234';

// 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've first initialized variables that we’re going to use in the insert query. Next, we've prepared the insert query and assigned it into the $query variable.

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. You must use this function when you’re dealing with string values submitted via $_POST variables. This ensures that your users don't inject malicious code into to your database queries. 

Finally, we ran the insert query using the mysqli_query function. The mysqli_query function is used to run a query against the active database. To wrap things up, we used the mysqli_close function to close the active connection.

How to Update Records

In the previous section, we discussed how you can insert new records in the MySQL table. In this section, we'll see how you can update a record which already exists in the table.

I assume that there's already a record exists in the employee table, and the id of that record is 1. In the following example, we're going to update a record which has a value of 1 in the id column.

<?php
$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 = '333-555-4444';

// 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 you can see in the above example, we want to update the phone column of the employee table where id is 1. This is very similar to the insert query, except that we're using UPDATE instead of INSERT.

How to Retrieve Records

So far, we've discussed how to insert and update records in the MySQL table. In this section, we'll see how to retrieve (fetch) records from the MySQL table.

Take a look at the following example.

<?php
$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);
?>

The first thing that you need to do in order to fetch records from a database is to execute the select query using the mysqli_query function. Upon successful execution of the select query, the mysqli_query function returns the mysqli result object, and that's what we've stored in the $result variable above.

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

The mysqli_fetch_array function takes two arguments—the result object and result type. The result type indicates what type of array will be returned from the mysqli_fetch_array function. It could be numeric, associative or both. It's an optional argument and you can pass one of these three values—MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH. MYSQLI_ASSOC indicates that you want to access columns by their name, and MYSQLI_NUM indicates that you want to access columns by their column number.

If you pass the MYSQLI_NUM value in the second argument of the mysqli_fetch_array function, you can access columns as $row[0], $row[1] and so on. In our case, we've passed the MYSQLI_BOTH value, so we can access columns in both ways. If you want to access only associative values, you can use the mysqli_fetch_assoc function instead.

In the next and last section, we'll see how to delete records from a database.

How to Delete Records

In this section, we'll see how to delete records from a database.

Take a look at the following example.

<?php
$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 pretty much the same as the insert and update queries. The mysqli_query function executes the DELETE query, and that eventually deletes the record from the employee table.

Conclusion

In this article, we explored how you can use the MySQL database with PHP. This article was intended to provide you with a basic knowledge of MySQL database connectivity in PHP to beginners. We saw how to go from setting up a database connection to executing different types of queries.

Feel free to post your feedback and queries using the feed below.



from Envato Tuts+ Tutorials

Comments

Post a Comment

Popular posts from this blog

How to Build Self-Confidence: 10+ Steps to Improve Your Self-Esteem

Image
Do you have friends who can walk up to someone at a party and they’d instantly hit it off? These are the people who often get free drinks, invites to cool events, and make new friends whenever they go out. You can gain confidence in yourself and improve different aspects of your life. (Image source:  Envato Elements ) Meanwhile, there are people who feel anxious in almost every social encounter. Their first day on a job, a big company meeting, and even a housewarming party all make them feel vulnerable and unsure of themselves. Questions like the following often spring to mind during these events:  “ What will I say? ”  “Who do I talk to?” “ What will I do if there’s an awkward silence? ”  While social awkwardness is common, it’s not a lifetime thing. You can learn how to build self-confidence that doesn’t feel forced or unnatural. You can still be yourself and have the self-confidence to try new things, meet new people, and all the benefits that come with...
Read more

Best of 2020: Great Landing Pages for Design Inspiration (Showcase Examples)

Image
A great landing page design is crucial when it comes to announcing new products or services as well as the launch of your company. Great landing pages help visitors focus on a singular action. That makes them great for driving conversions and signups. Zimed is one of our best landing page design examples on Envato Elements. Nowadays, you don’t have to be a web design expert to create a landing page. There's no shortage of  quality landing page samples on various marketplaces so all you need to do is find the one you like, buy it, and set it up. That's why we've set up this landing page showcase. In today's post, we'll share our best landing page examples from Envato Elements and ThemeForest. Find Great Landing Page Design Examples on Envato Elements for 2020 Envato Elements is a great starting point when it comes to the best  landing page design . Envato Elements is a subscription-based marketplace with dozens of landing page templates. When you start bro...
Read more

18+ Best Free PowerPoint Puzzle Pieces Templates (Infographic PPT Slides)

Image
Many people are visual learners. The population of visual learners is why it helps to use visual infographics to explain an idea in your PowerPoint presentation.  One of the best examples is using a PowerPoint puzzle pieces template.  Each piece of the puzzle can fit together into a singular idea. Templates like the premium  PANDAWA  template have all the PowerPoint puzzle pieces you need to explain a complex idea. Using a PowerPoint puzzle pieces will help you explain concepts more clearly. You can quickly and easily change professionally designed PowerPoint puzzles to fit your needs. This round-up will show you some of the best free PowerPoint puzzle templates. You'll also see top PowerPoint puzzle pieces templates from Envato Elements, and more puzzle pieces templates from GraphicRiver. Download the Best PowerPoint Puzzle Templates From Envato Elements Later in this tutorial, you'll see free template options. But for now, let's focus on the bes...
Read more