6.3 PHP and jQuery: Part 1

[NOISE] At this point we've learned that all of the Ajax helper methods like jQuery.get or .getJSON or .post, all of those simply point directly to the jQuery.ajax method. So if you use this method, you're actually saving a couple function calls which isn't much, you're not gonna be able to notice a difference, but if it makes you feel better definitely use jQuery.ajax. Plenty of developers use it exclusively, and ignore the helper methods. So in this lesson, I'm gonna give you a quick overview of jQuery.ajax and then we're going to build a little application with PHP so we could see how we could use it in a real-world project. So if we take a look at the jQuery API. We'll go to jQuery.get. And what we can here is, the parameters that we pass to the method are identical to calling jQuery.ajax directly and passing in those parameters. So in this case, .get, we pass in the URL. Well, if we did it with .ajax, we would specify a property name in the object literal and then pass the value. The same thing for a success callback. Now, there's actually a couple ways to do this with jQuery, now that we have access to deferreds, which you learn about in the next lesson. But, for now, you can still do it this way. You specify a success property, and that related function will be triggered when the Ajax request completes successfully. And we can also specify, among many things, a data type property. And this is where we can specify what type of data we expect in return. Are we expecting XML? Are we expecting a JSON response? This is where you can specify that. So as you might guess, when you use .getJSON, what that's doing is, it's setting the data type specifically to JSON. And that's really the only difference. Your still passing in the URL, your still passing in data a success callback but then, jQuery explicitly sets data type equal to Jason because it knows that's what you need in response. And the same thing its going to be true for jQuery.post, this is almost identical to dot get its just a slightly different request, you pass the exact same things but this time it sets type to post. But that's interesting if we come back to .get there is no type and that's because get will be the default for jQuery. So if you wanna use post it needs to set type to post, and then everything else is business as usual. Now, jQuery.ajax is extremely flexible, so we are not limited to only the one set outline you can specify content type. You can set a context for all your Ajax calls. You can set up filters where you'd run some calculation before the Ajax request occurs or even after it. And you can even use the .Ajax setup method if you wanna set up some defaults for all Ajax calls in your projects, and we'll take a look at that one as well today. All right, so now that we understand that really at the end of the day, we're just working with jQuery.ajax. Let's build a little application. We're going to build a simple way to query a database, display some information maybe go from page to page and, and filter that information down. So we're going to separate this into two chunks, the first step is we need some data to work with, so we're going to be working with a MySQL database. And the mysql.com website provides lots of sample database. So in this case, we're going to take advantage of this Sakila database, dev.mysql.com/docs/index-other. All right, so let's just download the ZIP file right there. That's gonna download to my desktop. Here we go. I'll open that up, and what we wanna do is import the schema and the data to our database. So I'm gonna come back and I have MAMP running, so I will go to php.MyAdmin. I'm gonna create a database called sakilla. Good, that's been created and now I'm going to import the schema. I'll choose that file, sakilla.db and get that schema in and click go. And that'll just take a few seconds. Now that the skin has been imported, we can import the data itself. So one more time, we're going to input the data.SQL file and click Go. And that is now completed, so I can go to any of these new tables, for example actor_info,. Click Browse, and now we have the massive amount of data that we can work with. Cool. So that was really easy. So I will close out the MySQL page. I'll also close out these Ajax references. Next, we need to set up an application. So, within my HT docs folder, you will want to create a folder in your HT docs folder as well, or wherever you have your server set up. And you can see I've created a folder called lesson 25, and it's empty. So I wanna go ahead and create the files that we need. For example, I'm going to need an index.php file, and I also need a file for our php function, so I'll go ahead and create those right now. All right, now we have two files set up. We also wanna make a couple directories, so I wanna create a partials directory. A CSS directory, a JavaScript directory, and a views directory. List the files, and now, those have been all set up. And next, our JavaScript folder should contain a scripts file. So let's touch js/scripts.js. And let's create some partials, as well. Now, partials will be, header.php, or footer.php. Those little bits of HTML that we include, so that we don't have to duplicate all of this code. So, let's go into the partials directory and touch footer.php and header.php. Next for a bit of styling, lets touch CSS/style.css, by the way, if your not familiar with this you can do this manually, I just find its a little easier especially when I'm not speaking over it, but touch will simply if the files not there, it will create the file for you. And lastly for now, we're going to touch within our views folder, I'm going to create an index.template file. And this will just be our view so that we can have our logic in index.php but the actual html and the view within its own file, so it has a little bit of separation. All right, and I think that's pretty good. So let's close this out and now, if I open the folder within sublime text, we're all set to go. So the first thing I want to do, is setup my database access. So I'm going to go into functions.PHP, and we're going to create a handful of functions. The first one is going to be called connect. Now this function will be responsible for setting up a new connection to our sakila database. And we are going to use PDO to take care of this. It is much more secure. It's a better solution. So, we will create a variable called PDO that's just fine. And we'll make it equal to a new N sense of the PDO class, and we need to pass in what database are we working with MySQL. What is the host right now? Its just local host for me. What is the name of the database? The database name is sakila or sakila and then it needs our user name and password. And on my local server, I have that set up to root root but you'll likely have password for in actual production. And the last thing for this function is, I do want it to be global, so that the other functions can access it. So, I'm gonna set that up as a global. Like so. Next, we need to have a couple functions that will grab information about an actor. So, let';s say we want to work with this actor table right here. Let's browse it. And this has just a massive list of actors. Well, let's see how we could query that. I'm gonna open up this SQL table. We'll pull that to the side, and let's try running some SQL. And let's say we want the first name and the last name and maybe the ID. So, we could say select actor ID first name and the last name and we're selecting it from the actor table, and let's try just like that for now. Okay, and now we've run that query, and we've selected all of those rows. If we wanna limit it, we could say LIMIT 10, and that will run. In this case, we don't wanna limit it, but what we do want to do, and we're not gonna do this just yet, but we want to be able to filter down this query by letter. So, where the actor last name's begins with A, or B, or C. And I'll show you how to do that, shortly. But, for now, we're gonna come back to Sublime text. Create a new function called, get actors. And I'm going to, within comments, simply paste in this query. And that'll be okay, for now. The next step is to go into our index.templateview. And if you want, right here at the top of index.php, we will include views/index.template.php. So now, when the regular index.php loads, it's going to include this index.templet file. So as we've done in the past, I'm going to import some HTML. And I wanna wrap our project within a div with a class of container. So the next step is, I'm gonna take the top of this, and I'm going to place that within a header.php file. And then I'm gonna take the bottom half of it, and place it within footer.php. There we go. Now, I get rid of these php include partials/footer, and then get rid of the top and replace it with partials/header, and we can get rid of this as well. So now, within here we can say, hi there. Let's test this out on the browser. Open this up, and we get Hi there. Let's make sure everything's working. And it is. Good. So we're making good progress. Now, the next step is, within our heading, it will be Search Actors By Last Name. And what we wanna do is have a drop down, where they can select a letter. And we're going to query the database and return all actors where their last name begins with whatever letter was selected. So with that in mind, we need to have a form its going to post to itself for now and the method will be post. Now within here, lets create a selects and we'll give a name of q and an ID of q12 for query. And then you might do something like this where you do a and then you duplicate this a couple dozen times for every single letter, that's really not good for php, we can do much better. So why don't we do this instead? I'm gonna open up a php block, and within here, we're going to set up the alphabet. And what I'll do, is within here I'm just going to put in every letter like so. Now, I could make this an array, but I'm a little lazy, so I'm going to use php string split function. And that's going to take every letter and turn it into an item within the array. So now alphabet is equal to an array of 26 items. Now we can say, for each alphabet as letter, let's echo out an option. So, we will echo out an option, the contents will be equal to that letter, and we're going to set the value attribute equal to that as well. All right, let's try that one out. Come back, reload the page, and now we have a dropdown for every single letter. All right, that was easy enough. Next, we want to listen for when the user clicks on the Submit button. So right after the select here, I'm gonna have a button, and we're gonna set it's type to submit, and we will say go. One more time. There we go. So at this point, when the user makes a selection, they will choose for example, k they click on go, and that's going to post to index.php and it's going to serialize those key value pairs. So now, for example, right at the top, we could say, if isset POST, and what are we looking for? We are looking for the element with a name of q. So, if that's available, why don't we just echo that out for now, echo POST q. All right, let's try this again. Reload the page. Make a selection, g. Go, and now we get that in response. So now we know that when the page posts back, if there is a q, key within $_POST superglobal, then the page has been posted back, and we can query the database for all actors where the last name begins with that letter that the user selected. So I'm going to remove this, and the first step is we want to make sure that we have access to the functions.php file. So I'm gonna require that. Next, if the page has been posted back, let's go ahead and connect. Now we could store this within a class to make things easier, but let's keep it as simple as possible for now. So if the page posts back, we're going to connect to our database. And the next thing I wanna do is grab the actors where the last name begins with the letter that was selected. So I'm gonna change this function from get actors, to get actors by last name. Now this function should accept a letter, and then it's going to execute a query. So once again, we want to bring in the PDO instance that we created right up here, and then let's prepare our SQL query. Statement is going to be equal to PDO. And we're going to prepare and I will copy and paste all of this in. So select actor ID, first name last name from the actor table. However, as we discussed how can we specify that it needs to begin with the letter? Well, lets come back to our database and what we have here is lets bring up the sequel again. And we're going to select, for now just to save time, select star from actor. Let's run that. That's going to get everything. But now watch this. Select star from actor where, and we can say first name equals Penelope. Execute that, and that's working. That's bringing that back. But what I really wanna do is say, where the first letter begins with p. And we can't do anything like that, because that's going to be specifically P. So, I'm going to replace this, and we're going to change it to where the last name is, like. And we're going to type in p. And then a percentage, which will reference any number of characters. So I click, go, and now, check it out. We're bringing in all records where the last name begins with p. Or, for example, if you wanna make it where the second letter is p. You would use underscore and that can represent any letter, I'll click go and in this case there aren't any lets do something a little more common. Any letter and now we've returned all rows where the second letter is equal to A. So that's a good technique to know percentage refers to any number of characters after the A, and underscore can refer to any character. So now we have the basic system in place. The last thing I'm gonna do just to be safe is limit it to around 50. We don't wanna deal with any more than 50 records. So let's come back, and I'm gonna replace all of this one more time. Like so. And then I will replace the star with actor id first name and the last name. Now the final thing is we wanna replace the letter A and we wanna make it dynamic so it can be whatever letter is passed in, but we can't really do it like this with prepared statements, so what we will do instead, is I'm going to replace this with letter like so, and then we will bind the value to it. So now that we've prepared a sequel query, let's execute it, and as the first parameter to the execute method, I'm gonna pass in any values that need to be bound. So we will do it within an array, and we will replace letter with the value that was passed in. But remember, the letter variable is just the letter. So in addition to that, we want to tack on the percentage sign. There we go. And the last thing is, let's just return. We're going to statement, and we're going to fetch everything. And I wanna fetch it as an object. So I'm gonna pass in PDO, and we want an object in response. Good. So now, we have this new function call. We can come back to index.php. We can reference it. We can pass in the letter that the user selected. And that will be returned in results. Lets print r, and see what we get in response. I'm going to reload the page, make a selection, D, click go, and if I view the source, now you can see we get results of all actors whose last name starts with D. That was pretty easy not too rough at all. So I'll come back and I'm gonna replace this with a variable called actors. And now we can come back to our template. Below the form, we will create a new unordered list. I'll give it a name or a class of actors list. And then within here, we will filter through the results or the actors object. For each actors as a, we will echo out a list item. In assets text we will do a first_name. They have split up into first name and last name. Then a space and a last_name. Now we need to remember though that we're filtering through actors. But it's possible when the page initially loads that actors won't be available. So, at the top, for now, why don't we do this, php if actors. And we'll make sure that it's set. Only on that condition, will we create an unordered list, and filter through it. And then I'll endif right here. All right, let's try this out. Load the page, and make a selection, choose, go. And now we get a list of all actors whose last names begin with A. And there you go, all of them are right there. Let's do one more. Let's choose h. Okay, well, this is turning out to be really easy. Now, we are done yet, though. I want each of these actors' names to be clickable. So within here, let's an anchor tag. And we'll wrap it like so. And we want the href to go to actor.php. And we'll create that file right now. And this will be a page that simple displays information about the actor, but I know that that page is going to need to have access to the actor's ID. And if we come back to our table, we can see that we pulled the actor ID for each actor. So I'm simply going to take that value and send it through to actor.php. Actor ID equals a actor_id like so. And the last thing we'll do here just for convenience more than anything, is we're going to do the same thing as a custom attribute. So a list item and we'll add data actor_id equals a actor_id, and that way we can easily access that value if we need it. It just makes things a little more convenient than having to parse the href attribute. Finally, I'm gonna make sure it looks like I have double quotes repeated, so I'm gonna replace that, like so. That looks good. And let's try it one more time. Reload the page, choose a letter. Now we have each of these links. Each of them, if you look in the lower left-hand corner, is going to actor_id. It's 194, in this case. I'll click on it. And now it takes us to that page. So let's work on the actor page. I'm gonna open that up right now. And first thing we'll do within here is require the functions file. Once again, we're going to connect. And then we're going to create a variable called info. And that's going to be equal to a new function, and we'll call this one get_actor_info. Now, this function, which we have not created yet, is going to get specific information about the actor. So clearly, that function is going to need the id of the actor to query the database. So we will pass in $_GET. And we're going to get the actor_id super global. Because remember, when we link to it right here, for ref we're sending it through right here. So we can access that within the get or request super global using actor id as the key as we've done right there. So, let's come back to our database, and take a look at actor info. And if I click browse, we can see that we have our list of actors. But, for each one, we also have some information about them. So we could say something like, select star from actor info, where actor id equals 3. And if I click Go. We get those results. Okay, so that should be fairly easy. We'll come back to functions.php. Create this new function. This function will accept the id. Once again, we'll bring in the global pdo. And we will prepare statements. So this time, I'm going to copy all of this in. Like so, and we're going to adjust this how we need. So in this case, we wanna select film_info, because if we come back, remember we want to grab this right here. And then we will also grab the first_name and the last_name from the actor_info table, and the only clause is that WHERE the actor_id is equal to actor_id. And then we will bind this variable to this within the execute method. Scroll down we're going to execute it, we're going to replace actor id with the value that was passed in. And then we're done, so we will simply return statement and because we're only returning one row, I will limit that to one and run fetch. Let's make sure we get a object in response as well. So now actor.php is going to call this function. This function is going to accept that ID. It's going to query the database for the information for an actor with the id that was passed in, and it's going to return that data. So once again, let's print our info and now we get this object. Let's view the source to see it a little more styled. And we get information within the object about the information and the actors name that is related to this id. So for example if we change it to id 193, we're going to get information about a different actor Burt Temple. So at this point, we have our object set up, lets include views .and well call this actor template.php, I'll create that file now. And lets come into its counter part template just so that we can steal some of this and we'll get rid of all of that. So now we have our header and our footer, now within php and we will echo an h1 tag, and this will have the actors first name and the last name. And we know we can access that through the info object. So lets do info->first_name than info->last_name. And then we will echo a paragraph tag that contains that information, and that was stored within the film_info column. Info->film_info, and close out our paragraph tag. So let's try this out. Come back, I will reload this page. Now we get the actor's name and we get some information. I'm not sure how they divided this up, it looks like they may be want you to split it up by the key and the colon. We're not gonna worry about that. We're just going to assume that's information about the actor, and then you could do whatever you want with that to divide it up. But what if we use an id that doesn't exist? So I will do 1940. We're not gonna get anything. Or if we don't have the actor id key at all, we're not going to get anything else. So, let's do this. At the top, we will say, if info exists, if it's not null, then echo this stuff out. Otherwise, we'll echo out a paragraph tag and say, No results available. Load it again, and now we get, no results available. And this would of course be your styled website and you would style that however you want. Now, the last thing is I want to have a link back to the homepage, so we'll do that right here. The href will be index.php and the value will be Back. Now we get a link back and now we're back to searching actors by name. I'll choose J, Jane Jackman, and now we are viewing information about Jane Jackman. So at this point the brunt of our php work is done. We're still going to do a little bt shortly so that we can work with our JavaScript, but that's going to conclude the php part of this lesson.