From the course: PHP for Web Designers

Querying the database - PHP Tutorial

From the course: PHP for Web Designers

Start my 1-month free trial

Querying the database

“

After successfully making the connection to MySQL, you can query the database and display the results. Database queries are written in Structured Query Language, SQL, which is often also pronounced, sequel. SQL is a vast subject, but in this course, we use just a couple of very simple queries. If you want to know more about SQL, there are several courses on lynda.com, including SQL Essential Training with Bill Weinman. Now we're going to put our query here in this else block. But before doing that, let's take a look at the structure of the database that we're working with. So I'm going to switch to my browser and phpMyAdmin. I'm going to select the Hansel and Petal site, and then I'm going to browse the Arrangements table. Now, at the top here is actually something very useful. This is the SQL that has been used to get all the details from the table. It's convention in SQL to put keywords in uppercase, so SELECT * is all columns, FROM, and then the name of the table. We don't need to worry about this LIMIT 0, 30 at the bottom. We won't be using that because we want everything in the table. And if we look at the structure of the table, what we've got in there in each row is an ID for each element, the image name, the alternate text, the title, the price, and the description. And the description has been stored as HTML inside the database, so that's what we're going to have to put into our mix.php page. So let's go back to that page. And we no longer need this message here. We're going to put our query here, and store it in a variable, called the variable sql. And then if you remember what we saw in phpMyAdmin, it's SELECT * FROM, and then the name of the table. Then we need to pass that to MySQL using the query method and store the result. We'll store the result in a variable called result, and then we use our DB connector, then arrow, and query as the argument to query that SQL variable. And that's all you need to do to query the database. But there might be an error, so let's check if there's an error. If, and then we use db->error. And that will give us an error message. And we need to store that in our message variable. So that's the creation of the database query. That basically is fairly simple. But the original HTML structure of this page means we need to work out very carefully how to display the results. So let's go down and have a look at the page itself. And all of the small images are inside this div with the class page open. And inside that page div, there are six further divs, and they've all got the class section. We need only one of them because we're going to be using a loop. So let's just scroll down past that first section and find the next one. What I'm going to do is I'm going to use Dreamweaver's select parent tag. Little function there to select the whole div. Delete that, do the same until I've got rid of all the excess divs. Being left with only one section div. And I'm going to leave those blank lines here for a moment because that tells me where the closing tag of the page div is. It's down here. What we want to do is if there's an error, we'll display it in this if block, but if there is no error, we want to go ahead and display the page. So we need to put the page div in an else block. There's the opening curly brace of the else block, and that's why I left those lines down there, so it makes it easier to find. There is the closing curly brace of our else block, and lets remind ourselves of what it is with a comment. So we can now get rid of those. Now there is the section. One little problem with that is that it's got four list items inside an unordered list. What we're going to do is to put the section into a loop. And each time you go through the results from the database, you get only one row. So if I use all those four list items, we'll end up with the same item being repeated four times. So let's get rid of three of the list items, leaving just one. So the next stage is going to be working out how to display everything. But let's just check to see whether we've got any errors in our SQL. So save the page. And if we go to the browser, and we need to load the Mixed Arrangements page, and refresh that. We've now got one item left in our page. The fact that that item is being displayed tells us that there was no error with our database collection. If there had been an error, we would have seen that error message. Let's just artificially put an error in there. Let's go up there and change the name of the table, resave that and refresh, and now it says, Table hanselandpetal_arrangement doesn't exist, and the section is not being shown. So that is working absolutely brilliantly. Let's go back, make sure that we correct the name, and save the page, and then the next stage will be to display the results from the database.

Contents