Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
We have learned how to read data from the database. The process for creating updating and deleting records is going to be very similar with a few key differences. The most important difference is just that when we do a select, we're expecting to get back rows of data as a result. Either that or an empty set with no rows. But still we're expecting a resource result to come back to us that we then parse through. With insert, update, and delete, we're not expecting to get any data back. We're just affecting a change on the database either inserting a row, deleting a row or updating a record in place. So it just returns true or false to us for whether it succeeded or whether it failed.
So with this difference in mind, let's try and use our MySQLI interface to do an insert of a new subject. So to begin with let's open up databases.php. Now I don't want to change this file. First of all we can see all of four subjects with it which is going to be handy later on and also it's a good example for you of how to do all five steps that you need to do for reading. So let's leave it pristine for now and let's just do a Save As and we're going to call it Databases_insert... So the steps that we're going to go through here are going to be just every so slightly different. We're still going to connect to our database and we're still going to close the database at the end. We're going to preform our database query, we'll come back to that in a second.
But we're not going to need to use the returned data. There's not going to be any returned data and therefore we don't need to release the data. It means nothing to say MySQL Free Result, if result is equal to just true or false. So we won't need that either. So instead we just need to focus on steps 1, 2 and 5. We're going to perform the database query here. That query is just going to be a regular MySQL query. We know what those look like. Just paste one in. So here's a good example, insert into subject. Here's my columns, here's my values. Now we're going to of course need to have values for that. Those would normally come in from a web form probably. So often these are form values in post.
And let's go ahead and just hard-code them here for now, while we're testing. Menu name equals edit me and we'll call this position equals 4 and then visible is going to be equal to 1 for true. So now when it constructs this string, it will drop in these values. And then query now will be the SQL query that we want to run. That's handy because remember we can always do echo of this query if we want to see what we put together. We want to find out why there was a problem, we can just echo the query and take a look. We'll know what we were sending to MySQL.
Now this would work just fine but I just want to show you a different format. I want to paste in a version that I like to use where I build up the quere and I separate my parenthesis here. I've got my opening parenthesis there, my closing parenthesis here and same thing here after values. And that just allows me to have all of my columns on one line and all of my data on another line. So all I do is insert into the subjects values and then I can pick what I want my columns and my data to be. It's nice and easy to see them lined up. So I like this form a little better. Both of them work just fine. So once we have our query string constructed, then we just run MYSQL query on it.
Now don't think that query somehow means that we're only asking the database to return us data. We're asking the database to do something for us. So query is still what we use for it. We still provide the connection, we just send it whatever MYSQL string we want. The result however, that was a little different. Because now the result that we get back is not a result set, it's just true or false. And we can test that, we can say if it's not true that would be false then die database query failed. That still works. However, I'm going to change it a little bit. I'm just going to make it positive. If we have a result, well then we know that we have success. If we don't have a result, then we can die with database query fail. Now in truth for success, if you were in production on something, you probably would do something like redirect to some page.php.
And for failure, you would probably do something like message equals subject creation failed. Something a little more user friendly. But we're going to go ahead and die with database query failed. And let's go ahead and say what the error was. We can find what the error was with my SQLi_error. And then parenthesis followed by the connection and that's going to basically go out to that connection and find out what was the most recent error that you got.
That error sits there until we make another query. So that error's still there for access as soon as we make another query its gone. And its replace by either no error if the next quarry ran without error or whatever the new error was. So lets put a space here so it concatenates nicely together and let's try it out instead though for success lets just have something here success that it worked. Now I realize that I'm echoing this above even the html, don't worry about that we just want some indication that it did succeed when this runs. So let's save it, let's go try it out.
We're now going to open the connection, run our insert query and then close the connection at the end. We'll go back to firefox/databases/insert.php. Success. We can actually find out if it did succeed, by clicking the back arrow and just reloading this databases page. And there it is, we can see it now. So that will work for us. Now I just I want to show you if it doesn't succeed what it looks like. Let's just change some part of this. So it's going to be a catastrophic failure, right. This is no longer valid MySQL says "inert" into subjects that's not going to work. We'll save it, we'll come back over here and databases insert. Database query failed and then here's that error message, this is what it's telling me.
Now that's probably not something that we want to show to our end user but it can be useful for us to see that message. So I just wanted you to see how to get it. Alright, so lets change this back so we once again have a valid page and it does say INSERT INTO and then we'll save it. So as you can see the process is very similar to what we did with reading records back. We're going to open our database connection, construct our SQL query, run that query using MySQLi_query. The one difference is that we get back true or false instead of getting back a result set that we parse through. And then at the end, we close the database connection and we're all done until the next request from the user starts.
There's another useful function that you should know about and that's mysqli _insert_id. Now because we told MySQL to insert the record without providing an ID at the same time, then MySQL uses the next autoincrement value for ID. That's because we told the ID column that it should autoincrement back when we defined it. But what if you need to know that ID after the insert is completed? Maybe you need to immediately do another insert into another table and use the ID for it. Or perhaps you just want to redirect the user to another page that shows the record that was just inserted. You would need to know the ID that MySQL just set. Well mysqli_insert_id allows you to do that.
It returns the ID of the most recently inserted record on that connection. Now we won't have a reason to use it in our content management system but I do want you to know that it's available because it can be vital for other applications. Let's look at update and delete now because there is one difference there that I want you to pay attention to.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 99077 Viewers
56 Video lessons · 112363 Viewers
71 Video lessons · 81191 Viewers
131 Video lessons · 38996 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.