PHP Tutorial (& MySQL) #26 - Getting Data From a Database

Key Takeaways

alright then so we've made our connection to the database now how about we try to actually retrieve some data from it so we do that by issuing SQL or sequel commands or otherwise called queries ok now there's three steps to making these queries sending them and getting the data and it basically just sent them first of all we construct the query ok then we make the query and then we fetch the results from that query so there the three steps that we need to perform so first of all let's actually write the query to get all the pizzas so let's do a little comment right here and I'm gonna say write query for all pizzas okay so I'm gonna store this query that we construct in a variable and I'm going to call it sequel or SQL and we're gonna set that equal to a string and this is where we're going to write our SQL now you may have seen this before it's a lot of capital letters with different commands so what we're going to do is use the Select command right there and that is select data that's what it means go and get data now I'm going to use this start in fact what I'm gonna do is write this out and then I'll explain it so select star from pizzas like so okay now what does this all mean well select we've already said means go and get select the data okay so that's how we go and get data this star means I want all of the columns from the table we're about to get data from so say for example each of our records has five columns right it has an ID it has a title it has the ingredients and email and it created out all five columns and I want all of those five columns of data when we go to get the data all right that's what this means the star this from means where we're getting it from the data and this pizzas is the name of our database we called that pizzas remember not the database sorry the table the database name is ninja pizza the table inside that database is pizzas so that's where we're selecting the data from so this would go ahead and select it all I don't want it all I just want certain columns so the way we do that is right out the columns that we actually want I want the title the ingredients and the ID because we're going to use that in the future they're the only three columns that I need right now okay right so the next step is to actually make the query and get the results so we'll say make query and get result okay so the way we do this is by creating a variable called results and set that equal to my sequel I underscore query because we're making the query right now we use the connection variable this is our connection to the database so this is the information or the reference it's going to use to go out and get the data let's spell this correctly it's my and then the second parameter or the second argument we need to pass through is the actual stuff that we want to issue the command the sequel so let's toss that in right there like so so that there my friends is going to actually make the query to the database and get us some kind of result right there all right now that result isn't just an array of pizzas or anything like that an array of records we actually have to get from that result the array that we want and this is the third step so we need to fetch the resulting rows remember each row is basically a record in a table as an array so that's the next step okay because they're not automatically in a reformat here or anything like that we need to actually get these pizzas as an array of different records or different rows so I'm going to store this in a pizzas variable and set that equal to my sequel I and then underscore fetch underscore all because we want all of them and we're going to fetch them from the result that we just got from making this query right here that's where we're fetching them from and then we want to return it as a my sequel i underscore Assoc like that and that returns it as an associative array for us okay so we'll see that in a minute because I'm going to print it out in fact what I'll do is I'll print this out first so say print underscore R and then we're going to print out the pizzas okay so just quickly again what have we done one second well first of all we've connected to the database then we've constructed this query string using select and from to say where it's from as well then we've got this variable called result where we store this query that we're making via this connection so we know where we connect into what database and what issue in this command this sequel command right here that we constructed that should get us this data right here from this table okay now this result it's not in a format that we can use what we need to do is fetch the data from that result in a format that we're going to use which is what we're doing here using this function and passing in that result that we got from the query and we say we want it back as an associative array then we're printing that array to the browser so let me see if this works save that and refresh over here and now we can see we have this array okay so we can see now a position 0 we have an array and in there we have a title which is ninja supreme ingredients the tomato so these are associative arrays right these things right here for each pizza we have an associative array and that's good because now we can cycle through those at some point and output them to the Dom to the browser in the template down here we're not going to do that just yet we'll do that in another video probably the next one to be honest but for now what I'd like to do is one more thing so typically after we've done our query we should first of all free the results for a memory this is good practice we don't have to do this but it's good practice we don't need this anymore so we could free it from memory ok so we can say my sequel i underscore free underscore results and then in brackets pass in the result okay so that's the first step the next step is to close the connection to the database so we're going to do that next and I'm just going to say my sequel i underscore close to do this and we pass in the connection so that's the connection that we're closing right there so we're freeing the result from memory so let me just put a comment above that free results from memory so we don't need to hang on to that anymore and then we're closing the connection okay so that's all there is to it we're writing the SQL issue in the command getting the data in a format that we can use then we're freeing this resort from memory because we don't need that anymore then we're closing the connection and at the end of it all we have these pizzas array now we have the data we need and we can go ahead in the next video and stats out put that in the HTML template