New terms from treehouse – Using PHP with MySQL – Connecting PHP to MySQL, Querying the Database with PHP, Filtering Input for Queries, Using Relationship Tables in MySQL, and Limiting Records in SQL Queries

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.

Screen Shot 2014-12-30 at 4.42.36 PM

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.

Screen Shot 2014-12-30 at 4.47.01 PM

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.

Screen Shot 2014-12-30 at 4.50.23 PM

You can click on it to see the table it contains, and the info within it.

Screen Shot 2014-12-30 at 4.51.16 PM

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.

Screen Shot 2014-12-30 at 5.08.38 PM Screen Shot 2014-12-30 at 5.08.32 PM

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.

Screen Shot 2014-12-30 at 5.17.19 PM

To test this, we can mess with the arguments we’re passing to PDO.

Screen Shot 2014-12-30 at 5.18.07 PM

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.

Screen Shot 2015-01-02 at 4.56.21 PM

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.

Screen Shot 2015-01-02 at 5.03.20 PM

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.

Screen Shot 2015-01-02 at 5.14.32 PM

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.

Screen Shot 2015-01-02 at 5.12.24 PM

We’ll call fetchall on our $results object, and set a pre tag before it so it displays nicely.

Screen Shot 2015-01-02 at 5.15.03 PM Screen Shot 2015-01-02 at 5.15.08 PM

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.

Screen Shot 2015-01-02 at 5.21.13 PM Screen Shot 2015-01-02 at 5.21.07 PM

We’ll need more info from our db for the site though, so we’ll add the other columns to our query string.

Screen Shot 2015-01-02 at 5.23.33 PMScreen Shot 2015-01-02 at 5.23.40 PM

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.

Screen Shot 2015-01-02 at 5.30.55 PM

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.

Screen Shot 2015-01-02 at 5.36.45 PM

We’ll also update our products.php code to include that so we don’t repeat ourselves.

Screen Shot 2015-01-02 at 5.38.51 PM

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.

Screen Shot 2015-01-02 at 5.46.14 PM Screen Shot 2015-01-02 at 5.46.08 PM

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.

Screen Shot 2015-01-02 at 5.50.20 PM

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.

Screen Shot 2015-01-02 at 5.58.24 PM

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.

Screen Shot 2015-01-02 at 6.12.06 PM

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.

Screen Shot 2015-01-02 at 6.13.54 PM

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.

Screen Shot 2015-01-02 at 6.14.49 PM

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.

Screen Shot 2015-01-02 at 6.19.08 PM

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.

Screen Shot 2015-01-02 at 6.27.34 PM

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.

Screen Shot 2015-01-03 at 1.50.35 PM

We can see the relationship table is also created in the file.

Screen Shot 2015-01-03 at 1.51.12 PM

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.

Screen Shot 2015-01-03 at 1.55.48 PM

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.

Screen Shot 2015-01-03 at 2.00.51 PM Screen Shot 2015-01-03 at 2.00.35 PM

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.

Screen Shot 2015-01-03 at 2.05.44 PM

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.

Screen Shot 2015-01-03 at 2.13.15 PM

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!

Screen Shot 2015-01-03 at 2.20.12 PM

Screen Shot 2015-01-03 at 2.20.41 PM

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.

Screen Shot 2015-01-03 at 2.25.34 PM

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.

Screen Shot 2015-01-03 at 2.30.08 PM

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.

Screen Shot 2015-01-03 at 2.38.36 PM


Here we’ll refactor the get_products_count function, which lets us know how many pages of shirts to display.

Screen Shot 2015-01-03 at 2.42.02 PM

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.

Screen Shot 2015-01-03 at 2.46.55 PM Screen Shot 2015-01-03 at 2.46.52 PM

We want that to be an integer, and can convert that with intval().

Screen Shot 2015-01-03 at 2.48.11 PM

Using LIMIT with Offset

Now we’ll refactor the get_product_subset function, which takes in two numbers, the starting and the ending shirt.

Screen Shot 2015-01-03 at 2.50.00 PM

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.

Screen Shot 2015-01-03 at 2.59.59 PM

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.

Screen Shot 2015-01-03 at 3.03.56 PM

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 (%).

Screen Shot 2015-01-03 at 3.06.12 PM

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.

Screen Shot 2015-01-03 at 3.09.01 PM

New terms from treehouse – Customizing the WordPress Admin Area – Admin Color Schemes, Customizing the WordPress Login Screen, Controlling Admin Navigation in WordPress, and Custom Dashboard Widgets in WordPress

Admin Color Schemes

What are Admin Color Schemes?

Color schemes are skins that style what the admin area looks like. It’s basically a css file. You can make it so each user can choose their own, or override it so everyone sees the same one.

If you go to Users > click on one, you’ll see some default schemes to choose from.

Screen Shot 2014-12-31 at 4.06.12 PM

Customizing Admin Color Schemes via a Plugin

Admin Color Schemes simply gives you a bunch extra to choose from.

Admin Color Schemer lets you create your own custom color scheme on the fly using color pickers. After you install it, you can find it under Tools > Admin Colors. Once you’re done, you can save and use it, and you’ll also find it in the Users option with the others.

Customizing Admin Color Schemes from Scratch

Start by making a child theme with the style.css and funtions.php files.

If you go to the root WP folder > wp-admin > css > colors, you’ll see each color scheme as a folder there. The colors-rtl.css file is for people whose languages read right to left. We’ll open the colors.css file, and copy its contents.

Screen Shot 2014-12-31 at 4.14.15 PM

Next, go back to your child theme, and make a folder called admin-colors, and inside that, make a folder for each theme color you want to have, and paste in the colors.css. To tell WP we now have a new color and we want it to appear in the admin area, we can use the wp_admin_css_color function in functions.php.

First, create a name-scaped function, in this case called wpt_admin_color_schemes. Set the theme directory to a variable, then the wp_admin_css_color function, which takes some parameters. The first is its short name, the next is it’s translatable full name, then the link to the folder where it can be found, then finally an array of hex values to appear as the four color options when viewing this in the admin option color select section.

Finally, you use add_action to call the function during admin_init, which is as soon as the admin area is initialized.

Screen Shot 2014-12-31 at 4.23.24 PM

To change the colors, go into the colors.css file and do a find and replace for the corresponding colors. So if we inspect element for the ectoplasm color scheme that ours is based on, we can find and replace that with the corresponding colors from our array.

Screen Shot 2014-12-31 at 4.24.55 PM

Screen Shot 2014-12-31 at 4.25.39 PM

To ensure that a certain user can only have one applied, you’d add a conditional statement within your wp_admin_css_color function based on the user.

Customizing the WordPress Login Screen

Customizing the WordPress Login Screen

For when you go to This lets you build a custom branded site for your client.

How to Customize the WordPress Login Screen via functions.php

Here’s what the default log in screen looks like.

Screen Shot 2014-12-31 at 4.32.55 PM

Here’s a link to the codex page on customizing this. The code will go inside your funcitons.php file. It starts with how to replace the logo, then how to add your own css file for it. You’d really just enqueue them in the same way you would for the main site. They also give you some highly helpful/specific classes you’ll need for this.

You can also add in some login hooks for things like errors. Note that you don’t just find and copy a file here, or need to take the child theme approach.

How to Customize the WordPress Login Screen Using a Plugin

Plugins make customizing the login form a lot easier. Custom Login is a good one for this. After installing it, you’ll find it under Settings > Custom Login. First, you should activate it, and put in a background color to test it. Log out and see if it worked. You can also set a background image.

You can easily upload a logo and customize the settings for it. Note that for the logo, a lot of the heights are hard coded in, so it’s often better to upload a file with the dimensions you need rather than try changing them in this form.

Screen Shot 2014-12-31 at 4.42.18 PM

You can customize the login form as well. You can also paste in any custom html, css or jQuery you’d like to use, right in the form.

Controlling Admin Navigation in WordPress

How and Why to Change Admin Navigation in WordPress

Your end user might not need to see or use all the options here, so it’s a good idea to remove those.

How to Remove and Add Admin Menu Links via the functions.php File

Note that the user level affects what they see, so an Editor wouldn’t see links to Settings, Plugins or Appearance.

Let’s say the user just needed access to the Pages section. Go to functions.php. The remove_menu_page function lets you remove things. It accepts the slug of the page you’d like to remove. For example, if you wanted to remove the link to plugins you’d write remove_menu_page( “plugins.php” ). It’s easy to just copy it over from the codex, and commenting out the things they don’t need access to. Note that add_action is using admin_menu, so when the admin menu is loaded, this function is called.

Now, we only see Pages in the menu.

Screen Shot 2014-12-31 at 4.53.40 PM

Screen Shot 2014-12-31 at 4.54.03 PM

Note that this just removes the links, and if someone knew the url they’d still be able to get to that page.

Remember, custom post types often make their own sections as well, so remove those too if you don’t want them appearing.

If the top menu bar is also customizable.

You can use add_menu_page to add links as well, though that takes a bit more knowledge then is covered here.

How to Customize the WordPress Admin Menu with a Plugin

Plugins make this easy. Admin Menu Editor is a good one for this. It also lets you control submenu links. After installing it, you’ll find it under Settings > Menu Editor. Note that there is a pro version, which let’s you set them per user role.

On the left you’ll see your main links, and on the right your sub links, which you can open and edit.

Screen Shot 2014-12-31 at 5.00.52 PM

Custom Dashboard Widgets in WordPress

Custom Dashboard Widgets in WordPress

When a user first signs in, they’re taken to their dashboard page. You can make custom ones, which is helpful if you want to display a custom message to some one as soon as they sign in. This can contain helpful links and contact info.

How to Add Custom WordPress Dashboard Widgets via the functions.php File

You can click Screen Options at the top to toggle widgets on and off. Note that these are customized on a per user basis. There are some global option plugins for this though.

Here is a link to the documentation for the Dashboard Widgets API. You can copy the code here right into your functions.php file. You make one function which contains the main wp_add_dashboard_widget function, which takes some parameters. The first is the slug, the second is the title, and the third is another function which will contain the content. That one is shown below, echoing out a message.

Screen Shot 2014-12-31 at 5.14.13 PM

Screen Shot 2014-12-31 at 5.14.42 PM

It will now appear on your dashboard, and under the Screen Options menu as well. The API documentation also tells you how to remove widget options.

How to Add Custom WordPress Dashboard Widgets via a Plugin

The Custom Dashboard Help Widget plugin is good for this. The premium version lets you set the widgets for all users. Once installed, it’s found under Settings.

Screen Shot 2014-12-31 at 5.19.40 PM

Updating the Footer Text in the Admin Area in WordPress

This is what we’re editing.

Screen Shot 2014-12-31 at 5.20.58 PM

The Custom Admin Footer Text plugin is good for this and will appear under Settings. It basically lets you set what you want for the left and right sides. It allows html, so you can put links in there as well.

Note that you can also do this with code via the functions.php file.