PHP MySQL CRUD Tutorial

4 Components Of CRUD With Native PHP and MySQL

In the last lesson we learned more about working with results from select queries in MySQL. In other words, when we used PHP to build up a query and send it to MySQL, MySQL then sent back a resource which could be massaged by any of the four techniques we discussed to get access to the data contained in the resource. So of all the MySQL commands such as SELECT, INSERT, UPDATE, and DELETE, SELECT is the only one to return a resource on success which requires further processing. The other commands simply return either true or false on a successful query or failed query respectively. In this episode, we’ll continue working with the database and build on the prior lessons concepts. We’ll now start completing inserts, updates, and deletes as well. Let’s get to it!


What Does A Query Return?

First off, let’s take a look at the different scenarios for what we can expect when actually executing the query against MySQL. A query is either going to succeed or fail, and the table here shows each of the possible 8 scenarios you may have.

  Query Success Query Failure
SELECT resource false
INSERT true false
UPDATE true false
DELETE true false

What this tells us is that the SELECT is going to strictly follow the 5 step process which is to:

  • 1. Create The Database Connection.
  • 2. Perform A Query.
  • 3. Make Use of Data Retrieved.
  • 4. Release Memory.
  • 5. Close The Connection.

All other cases for INSERT, UPDATE, and DELETE, are only going to require 3 steps. Those would be 1, 2, and 5 like so:

  • 1. Create The Database Connection.
  • 2. Perform A Query.
  • 5. Close The Connection.

How Do You MySQL Insert With PHP?

With this knowledge, lets create an insert statement into a database table. We’ll use a links table, here is the sql to create it:

Slick! This is a good example of being able to open one database connection at the top of the page, run multiple queries as needed in the body of the page, then close the database connection at the very end. In this example, ran this page two times. Once with the variable values of $url = 'http://yahoo.com'; and $name = 'Yahoo'; while the second query contained $url = 'http://vegibit.com'; and $name = 'VegiBit';. Once the connection was opened, we first did a query using the INSERT statement to put bookmark into our links table. Further down the page, but before the database connection was closed, we ran another query using the SELECT statement to immediately read back the data we just inserted. Once we did all of this, we simply closed the database connection.

mysqli_insert_id

Typically when doing inserts into a MySQL database, you’ll have an ID field that auto increments on each new insert. You never specify the value of this field, MySQL does it all automatically for you. The problem however, is that this field is also typically the unique identifier for a specific row or record of data in the table. How often do you hear something like, find by ID? If you are entering new records into the database, and you’re not specifying what their ID is, then how in the world will you find out this information? You’ll find this out with the mysqli_insert_id() function, and it is a critical function to be aware of. Maybe you need to do another insert into another table right after the first insert, and you need to use the prior ID for this purpose. This happens all the time in relational databases. The function signature is like so:

$id = mysqli_insert_id( $connection );

This function returns the id of the most recently inserted record on the given connection.


How Do You MySQL Update With PHP?

Now that we completed some inserts using PHP and MySQL, we should take a look at how to update records in the database. There are a few things to be aware of when doing updates. Usually, you’ll want to complete updates by the record’s unique ID. When doing an insert, we don’t need to worry about that since MySQL generates an ID automatically with an auto increment function. In this example, we’re just going to look in the database and choose the ID that we would like to update. In the real world, this ID will have been generated on a different page. The other page will have used a SELECT to pull the record out of the database, then it would be displayed on that page along with the ID of that record. Now that ID may or may not be visible to the user on the page. Most likely, it is transparent to the user, they wouldn’t even see it. Now there might be an ‘Edit This Entry’ type of link that would essentially pass along the ID via $_GET when you click that link. There may also be a form which has a hidden field pre populated with the ID and get passed via $_POST. The main idea however, is that one way or another, you are going to need to provide that ID in order to update it! Let’s do it.

Note! The query worked perfect and we can see that Google is now The Google and 1 row was affected.

$count = mysqli_affected_rows( $connection );

That mysqli_affected_rows function is going to come in handy, and is often used with update and delete statements in MySQL. The reason is, you may run an update query with success, but no rows were affected in the database. What this means is that, yes you sent a query, but there was nothing to change for that particular ID, so it did not affect the row. To test this, we can run the query above multiple times and it is only the first time that 1 row gets affected. All subsequent queries that use the same query string are successful, but no rows get affected. This is important to keep in mind.


How Do You MySQL Delete With PHP?

We now know how to Createinsert into table (column1, column2, column3…) values (value1, value2, value3…)” records in MySQL with PHP, Readselect from table where expression” records from MySQL with PHP, Updateupdate table set column1=value1, column2=value2 where expression” records in MySQL with PHP, and now we’ll learn how to Deletedelete from table where expression” records in MySQL using PHP. Create Read Update Delete. These are the four components of CRUD, and with them, you are now free to rule the world. Let’s complete our training with the delete.

Awesome! Note that the query worked perfectly and the entry associated with ID 11 is gone, the bookmark link for Google.

Conclusion

We’ve come full circle covering the basics of PHP and MySQL manipulation. We can see it’s a pretty straight forward process to implement CRUD with native PHP and MySQL. Now, using any of the awesome PHP frameworks to handle these chores for you is certainly an option. In cases where you might have a client that has hosting that might not support the latest and greatest technologies however, you’ll need to know how to do these things by hand in PHP. For those scenarios, this PHP Tutorial Series is the answer to your woes.