Connecting PHP to MySQL
Introducing Databases
Most sites store data in a database, like MySQL, then use a server-side language to access it and display it on the site. In this project we’re going to move the shirt data from an array to a db. Commands to access things are called queries.
Installing MAMP
If you have MAMP, you have MySQL. If you go to the start page, you’ll see the info you’ll need later under the MySQL section.
You can use the web based interface phpMyAdmin for browsing your databases and entering queries.It is also known as a MySQL client. You can view your dbs on the left.
Creating the Products Table
He gave us a file to download. You can see it starts by creating the db, then using it. Then, it creates a table named products, setting the columns for it and their data types and some other parameters, then inserts the needed rows.
Now, go to phpMyAdmin > SQL (or the SQL button under phpMyAdmin in the top left to open a new window) > paste in everything from the file > Go to execute. You’ll now see the shirt4mike db on the left.
You can click on it to see the table it contains, and the info within it.
Introducing Classes
php comes with a native method for connecting with databases called php data objects (PDO). Quick review of objects: Objects are a complex variable type that bundles other variables (properties) and functions (methods) together. -> is used with objects to access those.
In php, a class is a type of object, an object definition. It’s like a blueprint for an object, defining what properties and methods will exist when new objects are created from it. They instantiate their class, maintaining its properties and methods. phpMailer was a class, as is PDO.
Getting Started with PDO
In our project files for shirts4mike, we’ll make a temporary file so we can look at the db. First, we’ll specify a variable name for the object we’re creating from our PDO class. Creating a new object from a class calls a function within that class, so you use parentheses. Like a function you can pass in arguments, and here, you pass in the info needed to connect to the db.
The first is a string to id the db. You specify the type of db then a colon, then host=, then the host you want to connect to, in this case localhost. Then a semicolon, then dbname=, then the name of the database. Next, you specify the port number. If you’re using the default MySQL post or 3306 you won’t need to do this though. Note that MAMP’s default mysql port is 8889.
The second argument is the username, in this case root. The third is the password, also root.
For troubleshooting, we’ll display some info about our new variable to the screen. When loading the page, we can see that it’s an object of the PDO class.
Handling Exceptions
Any code that connects to an external system should be placed in a block of code that can handle exceptions. In php, that’s a try catch block. You surround the code in a try, and php will try to execute all the code in there. After that you put a catch command, and if there’s an exception php will instead try executing the code in the catch block. When an exception occurs, it’s passed to the catch block as an object. The variable $e will contain details about the exception. So, if there’s an issue we’ll now echo out a message and stop all other code from running.
We’ll also add a message if the connection was successful.
To test this, we can mess with the arguments we’re passing to PDO.
Querying the Database with PHP
Querying the Database
Two common lines of code after creating a PDO object are as follows. exec is a method that receives one argument, the SQL command you want to execute as a string. SET NAMES ‘utf8’ defines the character set to be used when sending to and from the db.
The setAttribute method is for when there are errors with PDO. When it can’t connect, we use the catch block, but for things like invalid queries we use setAttribute. It takes two arguments, first being the attribute you want to change, in this case the error mode. The second is the value you want to give that attribute.
Now for another try/catch block. Remember, these are for when you interact with an external system, and you should have a separate one for each interaction. This one is to run the query that retrieves the products, and the previous one was for connecting to the db. We’ll use the PDO objects query method, which takes one argument, the query you want to run as a string. We’ll put it’s return value into a variable named results, so we can use it later. We’ll use the ORDER BY clause in our statement to set how to sort the results.
We’ll add some echo’s to let us test if it’s successful or not for now.
Retrieving the Results Set
$results isn’t an array, but a new kind of php object, a PDO statement one. We can see this by var_dumping it. It has one property, the query string used to retrieve it, but many methods which you can read about in the documentation.
If we read the documentation for the PDOStatement::fetchall method, we can see that it’s a public method, meaning that it can be called from code outside of the object. array means it returns an array. Thee :: between the class and the method means they are related. The square brackets once again mean those things are optional, in this case its arguments.
We’ll call fetchall on our $results object, and set a pre tag before it so it displays nicely.
Working With Query Results
Note that in our array each element shows up twice. Once with the associative key, and once with an integer key.
Looking at the documentation again, we can see the parameters fetchAll takes. fetch_style is the first, and we can see the default is to fetch both the column name and the 0 indexed column number, which explains what we’re seeing. We’ll change it to fetch_assoc to get just the column name.
We’ll need more info from our db for the site though, so we’ll add the other columns to our query string.
Note that the indices aren’t the same as the sku like the were in our array, but that’s okay because don’t need them to be here.
Modifying Model Code
Since we did a good job with our separation of concerns in the original product, we only need to modify our model code to make it use this db instead.
First we’ll take out the var_dump and instead set our fetchAll to a variable $product.
In our products.php file, we’re currently defining our array in the get_products_all function. We’ll comment out that part, and paste in our db code.
If we were to look at our home, shirts, or search pages, they all still work fine, which is great. We can test to make sure our site is using the db by messing with our connection string, and checking if the error message we set up displays.
Avoiding Duplication
The database code is going to be used a lot so it’s better to add it as an include file in that folder. We want to check and set up a connection every time we run a query, so we’ll leave that block, but, our queries are going to differ each time, so we can remove the second part.
We’ll also update our products.php code to include that so we don’t repeat ourselves.
When you’re working in your local environment, your db credentials will often differ than for your live environment. The best way to handle this is to use your config.php file again and enter them as constants, then use those constants in our database.php include file.
Filtering Input for Queries
Refactoring Shirt Details
Right now our shirt.php page gets the full list of all shirts and then filters down. This is pretty inefficient, so we’ll refactor this page.
This code could be needed in other spots later, so we should probably move it to the model. We’ll start by removing the call to get_products_all. Although it doesn’t exist yet, we can also set a variable $product to the return value of a new function that gives us a single product.
The last bit of code has to do with when someone enters a url for a shirt that doesn’t exist, in which we redirect them back to the general shirts page. Before, it would check if the shirt didn’t exist by checking if product was set. However, $product will be set regardless now, because $product is now being set to the return value of get_product_single. Still, in our upcoming model code we can set the return value if no product is found to whatever we want, in this case we’ll do false. We can then use the empty function, which checks if the variable contains an empty value, like a boolean false.
Using a WHERE Clause
Now to make our get_products_single function, which will receive one argument, the sku. It’s good practice to write the comments for new functions when you create them.
In the function, we’ll start by including the db. We’ll then run a query with a try/catch block, using a WHERE clause. For now, we’ll hard code a number in, like 108. We can then set that to a variable, which will put the returned array into it. We can then set that to $product, which we can use fetch method on (rather then fetchAll, since due to the where clause we only have one row, and fetch grabs the first row). Finally, return $product.
This now works, though it always brings you to shirt 108.
Understanding SQL Injections
If we were to just put the $sku argument in our SQL query, it would work like this.
However, someone could in theory add a semicolon then another sql statement and seriously mess with your db. This kind of attack is called a SQL Injection.
Remember, when dealing with values from outside your code, you want to filter input and escape output (like when we use htmlspecialchars() on our contact form). To filter input here, in shirt.php use the intval function on the $_GET variable, so no matter what someone enters it will be made into a variable. This is also known as sanitizing input, and you should do it as soon as you receive it.
Preparing SQL Statements
Now, after the controller code on shirts.php receives the id for sku, it passes that to the model code get_product_single. Even though it’s already been sanitized, it’s good practice to sanitize it in both places, because model code might be called from other places that might not be getting filtered.
Back in products.php, we can use PDO’s prepare method, which accepts a query and prepares it to run, without actually running it yet. We’ll replace the query method with that. We’ll then replace the sku value with a ?, which works as an unnamed placeholder.
Next, we want to use our $sku argument within our query. The bindParam method, which connects a variable to a placeholder in a SQL statement. The first argument it takes is a reference to the placeholder. For unnamed ones, you use its number, in this case 1. The second is the variable you want to bind to. These steps protect it from a SQL injection.
Finally, we can use the execute method to run the query, and everything works.
Note that with the fetch method, if a sku can’t be found, there will be no rows in the return and fetch will return a boolean false, which is what we want here.
Using Relationship Tables in MySQL
Understanding Relationship Tables
We can’t store the shirts’ sizes in our table because a shirt might have more than one, so we’ll put those in a separate table. He gave us that table to download. First is makes a table for the different possible sizes. Note that he has a column named order that will make them easier to list out later. They’re spaced 10 apart in case you need to put a size in between them later.
This and the original table are related in what’s called a many to many relationship. That means one shirt can have multiple sizes, and one size can be used for multiple shirts. To have this work in a db, you need a third table, a relationship table.
We can see the relationship table is also created in the file.
Querying Two Tables with JOIN
Add the file’s content to your MySQL database with phpMyAdmin like before, and you’ll see two tables have been added.
If we wanted to query for the sizes for a particular shirt, we could write something like this.
To join this with another table, we’d use an INNER JOIN table, which will only give us info on sizes in both tables. We’ll also make sure to order them by ascending largeness. Note that he named the column order, which is a reserved name, and if you want to use it you must surround it by back ticks.
Displaying Shirt Sizes
We now need to add our shirt sizes from the db in the same order they were in the array before. We just had an array element with the sizes, and no associative index, so it must have gone 0,1,2, etc.
Right now this function gives us the shirt details if it found one, and a boolean false if it didn’t. We’ll use a conditional to check for that before having it retrieve the sizes. Note that with a conditional that gives just an early return, apparently you don’t need any curly brackets, though this can make things harder to read.
First we’ll make an empty array called sizes. Next we’ll query the database for sizes for the shirt, using a try/catch block again. The query is using aliases, and is still a bit long. With php strings, you don’t need to put the whole thing on one line.
Fetching in a While Loop
fetchAll won’t work for us here, and apparently usually doesn’t. Usually, it’s better to use a while loop and format them as you go. Our while condition is the fetch method that will run so long as there are rows left to run through. When this happens, $row is set to boolean false, and the loop will stop running.
Inside the loop, we’ll add each $row variable to the array. Note that you add elements to array by adding empty square brackets after it. Note that the $row variable will only have one thing in it, the size, as that’s what was specified in our query above. Still, we’ll specify that with square brackets and the column name.
Now, it works!
Limiting Records in SQL Queries
Using LIMIT with Descending Order
A lot of our functions are retrieving all the shirts from the db and using php to narrow them down. We should instead improve our queries.
We’ll start with get products recent, which returns the four most recent products, by sku.
First, we’ll include the db include file. Then, another try/catch block. Since we don’t have any user input, we don’t have to worry about filtering it, and can use query instead of prepare. The query is similar to what we did before, with the difference being that we want to limit it to the four most recent sku’s. First we need to order by the sku column in descending order. Then, we can use the LIMIT keyword to limit this to 4 rows.
Next, we’ll extract our results into an array using the fetchAll method.
Reversing the Results Set
Right now our function is returning shirts in the opposite order we want. We can do this using the array_reverse() function. Remember, that function returns a reverse copy of the array you pass it, so you must set it equal to a variable. We want to replace our array with this new one, so we’ll set it equal to it.
Using COUNT
Here we’ll refactor the get_products_count function, which lets us know how many pages of shirts to display.
Instead, we’ll get the count from MySQL. We’ll include the db file, then add a try/catch block. We can then use the aggregate function COUNT to count how many rows there are in the column sku, and then return that value.
To extract that value, we can use the fetchColumn method, which fetches just a single column. If we var_dump that, we’ll see it’s given to us as a string, because that’s how MySQL gives php data.
We want that to be an integer, and can convert that with intval().
Using LIMIT with Offset
Now we’ll refactor the get_product_subset function, which takes in two numbers, the starting and the ending shirt.
Once again, include the db file and add a try/catch block. We’ll be receiving input, the starting and ending position, so we’ll want to filter that and use the prepare method. If we use two numbers for limit, the first is the offset, and the second is the number of rows. So, LIMIT 10, 4 would offset ten rows, and return rows 11, 12, 13 and 14.
So, we need to modify our starting and ending values. If we want to start at 1, we need the offset to be zero, so we’ll subtract one from whatever $positionStart is. The number of rows equals the ending number minus the starting number plus one. For our bindParam we’ll pass it an additional argument to make sure it treats the result like an integer.
Using Like
The last function to refactor is the get_products_search one. Right now it uses php to perform the search, but here we’ll make it use SQL instead.
Once again, include the db file and add a try/catch block. We want a WHERE clause to check for a search term, but we don’t want it to be exact, so we’ll use the LIKE clause and some wildcard characters (%).
Now, we can’t use bindParam because we need the search term to be surrounded by the wildcards, so we’ll instead use bindValue, which lets you use concatenation.
Kyle's Awesome Awful Websites 