Creating Users Table 0:00Now, in the previous episode, I gave you a little bit of homework that would require some research. So essentially, I said, when you type your name here, well, behind the scenes, you want to insert it into the database. Now at this point, though, we don't have a names table or a users table. So why don't we do that now? Let's create a new table. I'll call it users. And in real life, you'd have a username, a password, an email address, but we're keeping it really simple, and we're just going to stick with the name.And in real life, you'd have a username, a password, an email address, but we're keeping it really simple, and we're just going to stick with the name. That is a variable number of characters and maybe a max of 100. Now, right now, it's automatically set to allow null. No, you have to enter a name. But we also have the ID, which is pretty standard once again. It's an integer that will auto increment, as you can see here, and it is a primary key. Okay, so now we have our table. Let's go back and see how we can insert it. So if you remember, let's go to our index view. Wiring Form to Route 0:50Let's go back and see how we can insert it. So if you remember, let's go to our index view. Here's our little form that posts to slash names. And our routes file says that route or that endpoint is associated with this controller. In real life, you may have like a names controller. And within there, you'll have a method called store or add name. And we're going to review that. But for now, we're just loading a single file, and that's fine. Okay, next, within our bootstrap, we know that we have access to our query builder by doing app database. Designing Insert Method 1:17Okay, next, within our bootstrap, we know that we have access to our query builder by doing app database. But so far, if we take a look at the query builder, yeah, we only have a select method. So it sounds like we need a new method called insert that will accept the name of the table. We called it users, right? And then it should accept an array of parameters. So for example, in our case, we just have a name. Remember, the ID will auto increment, which means you don't have to explicitly set it. So we only need to set the name. So we could say, the name is going to be equal to whatever the user types into thatSo we only need to set the name. So we could say, the name is going to be equal to whatever the user types into that text box. So right here, input name equals name. We're fetching that, and we're going to pass it to our insert query. Okay, but right now, we know none of this is going to work. So let's go over to the query builder. And we know that we have a new method called insert that will accept the table name and our parameters. So we're going to have to dynamically populate this to make it as flexible as possible.our parameters. So we're going to have to dynamically populate this to make it as flexible as possible. And for a refresher, the shape it will take is something like this. Insert into a table name that can be users or tasks. We have to give it the keys. So in this case, it would be name. I'll just use a stub there. And then the values would be a set of placeholders. So we'd have things like name. So yeah, in our particular case, the query we probably want is something like insertSo we'd have things like name. So yeah, in our particular case, the query we probably want is something like insert into names, the name, and then the value would be a placeholder here. And we use placeholders here because, once again, we're going to use prepared statements, and that's going to give us a lot of security and protection against SQL injection. But otherwise, if we had another column, like an email column for the users table, you might do something like this. And notice how there is a colon before each one. That designates that it's a placeholder. And then ultimately, when you execute your query, there is where you would say, okay,That designates that it's a placeholder. And then ultimately, when you execute your query, there is where you would say, okay, the name placeholder, you can use a colon here or omit it. I always omit it because usually you already have an array of parameters. So you don't want to map over it and update the key. But anyways, you could say name equals Joe, and email is joe at example.com. So now each of those will be bound. And ultimately, this ends up being Joe, and this ends up being joe at example.com, and you have your query. So if we break this down into a template, once again, it's going to be something like Building Insert SQL 3:35you have your query. So if we break this down into a template, once again, it's going to be something like insert into some table name, some column names, and then some column values. That's our query. Okay, well, PHP offers a function called sprintf. And what this does is it allows you to declare a string with, once again, placeholders that you can attach variables or values to. So for example, if I were to say sprintf and paste that in, well, now we have two placeholders. And that's why I'm using the percentage here. A string, a string, a string.And that's why I'm using the percentage here. A string, a string, a string. So I have to provide values. I could say one, two, three. And let's just see what happens there. SQL. And then I'm going to die and var dump that SQL. And you'll see how this works. And we can get rid of that. Okay, so back to Chrome.And we can get rid of that. Okay, so back to Chrome. We'll just type Joe, submit. And there's what we end up with. So once again, we use the percentage here as a placeholder for sprintf. And then these down here will be bound to each one. So this one will be bound to the first. This will be bound to the second. This will be bound to the third. So that means we can just update these accordingly.This will be bound to the third. So that means we can just update these accordingly. For example, table. And if we give that a refresh, now it's using the users table. Okay, next, the column names. So how can we get that? Well, right now, we're passing through our parameters. And each key would represent a column name, right? Well, in PHP, I don't know if I've shown you this one, but we can use array keys, and then you give it an array.Well, in PHP, I don't know if I've shown you this one, but we can use array keys, and then you give it an array. And that will return to you an array of only the keys. So that will give you basically an array that says name here. It ignores the values, it just gives you an array of only the keys. So if you have age, well, then that will be name age is what you get. Make sense? So let's come back, die and var dump this so you can see it. Refresh. Sure enough, you can see that we have an array of only the keys.Refresh. Sure enough, you can see that we have an array of only the keys. And those will be our column names in this case. So let's do this. We'll put each on their own line. So I can't just say array keys parameters, because again, that's going to return an array. But we actually want to translate that to a comma separated list. So we can use the implode function. We can designate the separator, in this case, just a comma.So we can use the implode function. We can designate the separator, in this case, just a comma. And then we provide the array. So again, let me show you an example, since some of this is new. So if our array is 1, 2, 3, and we say implode that array. And the separator will be a comma. What that's going to do is turn this into a string. And then basically the glue that connects them will be a comma. So you'll get 1, 2, 3, exactly what we want here. So let's get rid of that, uncomment this.So you'll get 1, 2, 3, exactly what we want here. So let's get rid of that, uncomment this. And now I can say implode only the keys for the parameters and separate each one by a comma. All right, give that a refresh. And now it's failing, but only because we haven't passed a third value. We'll say stub for now, because we haven't gotten to that. Refresh. Okay, so we're getting there. Insert into users, the name column.Okay, so we're getting there. Insert into users, the name column. And the value for that name column will be, well, now we have to get the values. But remember, we don't actually want the value itself. So we don't want John Doe here, we want a placeholder. So remember, that would be something like, values would be name, or email, or age. Remember, we're using placeholders there. Okay, well we have the keys, but we do need that colon in front of them.Remember, we're using placeholders there. Okay, well we have the keys, but we do need that colon in front of them. So here's what we can do, we can use a little trickery. Let's get the keys once again, but then I'm going to implode them and separate them by a comma, and then a colon after it. This is kind of a little bit of trickery here to make it work. Another option, of course, is to use array map, and then implode that. But this will add a colon after each one, but you'll see that it ignores the first one. Refresh.you'll see that it ignores the first one. Refresh. Yeah, and in this case, we don't see anything, because we only have one column. But if we were to say age is 30, and give that a refresh, you'll see that yes, we get the colon for any additional column names, but not the very first. So there, yeah, this is where I say it's a little trickery, because we can just add a colon, and then implode these into a string, and this will give us exactly what we want. Yeah, it'll work.this will give us exactly what we want. Yeah, it'll work. You could even defer to a method to clean this up. And then the method, once again, could do an array map over the parameter, and then return a colon, and then the param, and then you pass the array keys. That would be an option as well. But I think this is going to do the job for now. So we have our SQL query. We now need to build up the query. So prepare a new SQL query. Executing and Handling Errors 8:26We now need to build up the query. So prepare a new SQL query. We'll save that to statement. And then finally, we want to execute the query. But you might be noticing, okay, we built up the SQL query. We prepared it, but at what point do we bind the values? And you can do this in a couple ways. One, you could say statement bind param. And basically, the way that would work is you would say something like, bind the name parameter to whatever was passed in, like Joe.And basically, the way that would work is you would say something like, bind the name parameter to whatever was passed in, like Joe. Alternatively, you can just pass an array to the execute method. And in this case, we have an array, so that's going to be really easy for us. But now, what if something goes wrong? Like, for example, we know that we don't have an age column, so this should fail, right? Let's go back to the homepage, Joe. And yes, it does fail, but it only displays it as a warning. And really, I want an exception.And yes, it does fail, but it only displays it as a warning. And really, I want an exception. This is an important thing. It couldn't perform the query, so let's make sure that an exception is thrown. And you'll remember, we set up our PDO options here. So I will update this to error mode exception, okay. So now if I give that a refresh, we get a full exception here, PDO exception, okay. So let's do this. Let's run this through a try catch. So we're going to try to perform this query based upon what you gave us.Let's run this through a try catch. So we're going to try to perform this query based upon what you gave us. But if we catch an exception, or a PDO exception, well, something went wrong. So why don't we, yeah, we could redirect, we could display a 404 page, or we could just die and say, whoops, something went wrong. Yeah, in real life, you'd have something a little more sophisticated, where you would let this bubble up. And then maybe for local development, you would spit out the message itself. But for production, you don't want to show that information to the user, so you just display a generic message, or a 404 page.But for production, you don't want to show that information to the user, so you just display a generic message, or a 404 page. Let's not get ahead of ourselves, though. So let's do this. Let's go back to Chrome. Once again, this should fail, and we should see a message. Sorry, something went wrong. Let's go back and fix this, though. There is no age column. So we get to this point. Redirecting and Listing Users 10:31There is no age column. So we get to this point. We insert it into the database, and I believe that's going to work now. Jeffrey, and we don't see anything. But if we come back, view the contents, there it is. So if we try it again, Sarah, it's all working like we would expect. However, we don't see anything here, and that's because we performed the query and then did nothing. In PHP, you can redirect by setting a header. And you could say the location I want to redirect to is the home page, orIn PHP, you can redirect by setting a header. And you could say the location I want to redirect to is the home page, or the about page, or anywhere you want. Now, you'll find that if you use a framework, they will make this a little more natural. For example, in Laravel, you might say, return a redirect to the home page, and it just makes a little more sense. But here, this will do the trick. So let's come back, run it again, Frank, submit. And it did redirect very, very quickly, butSo let's come back, run it again, Frank, submit. And it did redirect very, very quickly, but we didn't have much indication that anything took place. But nonetheless, it did work. So why don't we finish up by echoing out all of that information? Here's our index controller. We're no longer getting tasks, we're going to get names. And we're going to select star from the names or users table. Then we load our view. And within our view, yeah, we could say right up here, PHP, forThen we load our view. And within our view, yeah, we could say right up here, PHP, for each names as, or it's actually users, really. Let's go back and update this. Okay, for each user as user, and then we'll close that out. And for each, then, yeah, we'll just use a list item here and echo out the user's name. Okay, so now, we give that a refresh, and we have our names. But we could say Stuart, Brenda, or Jack. You get the idea.But we could say Stuart, Brenda, or Jack. You get the idea. Okay, good job, and that'll do it for this episode.
