New terms from treehouse – Database Foundations Part 1 – Introduction to Data, Databases and SQL, Installing MySQL Server and MySQL Workbench, Reading Data from Databases with SQL, and Creating Tables and Manipulating Data with SQL

NOTE: If you’re looking for the instructions on installing/using the new version of MySQL Workbench on OS X, click here. 

Introduction to Data, Databases and SQL

Introduction to Data, Databases and SQL

A database is something that lets you store data, and then create, read, update and delete that data. It acts as the memory for a website. Ruby and other languages can abstract the finer details, but this course we’ll cover them as they are often the key to solving problems and speeding up performance. There are two main components to databases. The first is schema, which is the organization and relationships within the database. It is the different sections, and how they should store data. This is also known as modeling. A model is a plan/blueprint for building a database. This involves choosing what you’re going to store for each thing. If you were storing movies, you may choose the title and release year, and not include the director.

The second main component is data, whose formatting is described by the schema. There are different kinds of databases out there, but in this case we’ll be covering relational ones, which include MySQL, SQLite and Postgres, mainly MySQL. Databases contain collections of info called tables, which are spreadsheet like structures with rows (sometimes called tuples) and columns (which is a property of the entity you want to store). Depending on each database there is usually a naming convention for them.

Screen Shot 2014-12-23 at 2.19.33 PM

To get data in and out of the database we use Structured Query Language (SQL). It is written in keywords and phrases to make a sentence, which is sometimes known as a statement. Here’s an example. select is a keyword, and the * means every column in the table. From is the clause to say where it should be retrieved from, and movies is the table.

Screen Shot 2014-12-23 at 2.22.28 PM

Introduction to Data Types

Columns can have data types, which are like a type of formatting strictly enforced for that column. The three main kinds are strings, numeric and date/time types. There are two kinds of strings in MySQL. varchar contain short strings of characters, ideal for things like product names and movie titles. text are for longer strings like descriptions.

For numeric, there’s integers (whole numbers), fixed point (where the number of decimal places is fixed, like for currency), and float point (where the number of decimals can vary). How they are stored by the db (database) can vary though, if you put in 2.3 it may be stored as 2.2999.

For date and time types, which can contain a date, a time, or a combo of both, which is known as datetime. The types you choose are important, especially when it comes to sorting the columns later. Strings take up more space than integers, so it’d be silly to store a whole number in one.

Creating Tables

A basic SQL statement to create a table would look like this:

Screen Shot 2014-12-23 at 2.33.21 PM

The CREATE keyword tells the db what to do, and the TABLE keyword tells it what to create. Then, you put the table name (actors). Then, in the parentheses you have the column definitions, in this case we only have one. You put the column name, then the data type, and in this case we’re setting the character length with 50. To add more columns, simply separate them with commas.

Screen Shot 2014-12-23 at 2.35.31 PM

Inserting Data

Here’s a basic SQL statement for inserting some info into a table.

Screen Shot 2014-12-23 at 2.37.52 PM

INSERT INTO says you want to add info to a table. The VALUES keyword sets up the values, which appear in quotes. Make sure to enter your info here in the same order as your columns.

Installing MySQL Server and MySQL Workbench

Introduction to MySQL Server and MySQL Workbench

The client server model is when you use a GUI interface or programming language to interacting with a database on a server. For this lesson, the client will be the MySQL workbench, and the server will be the MySQL community server.

Installing MySQL Server and MySQL Workbench on OSX


1. Go to > downloads > community > MySQL community server > download the file for your version of OSX. There’s two options, compressed TAR and DMG, select DMG. > No thanks, just start my download.

2. Download and Install it.

3. Download the MySQL Workbench GUI tool under > downloads > community > MySQL Workbench > download the one for OSX. Note that in this lesson he’s using an older version of this so some things may look/work differently.

4. Download and install it. Under System Pref > MySQL > Start MySQL Server

5. MySQL requires a special config file in a folder called ETC. Open Terminal > enter this: sudo cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

6. Open MySQL workbench. Click the little + next to MySQL Connection. For connection name use localhost, for connection method standard TCP/IP, and for hostname localhost. Port should be 3306 and username should be root. Click Configure Server Management in the lower left hand corner.

Screen Shot 2014-12-23 at 3.35.41 PM

7. Click Continue. It should now test your connection. If you’re getting an error, make sure that under System Preferences > MySQL you have the server running. Click Continue.

Screen Shot 2014-12-23 at 3.37.10 PM

8. For Operating System is should be MacOS X and for the MySQL Installation type it should be MacOS X(MySQL Package). Click Continue.

Screen Shot 2014-12-23 at 3.39.15 PM

9. Now it can test the connection, and if it passes should look like this. Click Continue, then Continue again.

Screen Shot 2014-12-23 at 3.39.36 PM

10. You can now start and stop the server by clicking on it’s panel in the main window.

Screen Shot 2014-12-23 at 3.41.27 PM

11. If you click Server Status on the left you can confirm its running.

Screen Shot 2014-12-23 at 3.43.07 PM

Running a Script and Using MySQL Workbench

He gave us a file to download. Open the connection then go to File > Open SQL Script > select it > Open. You should now see the scripts for the db. Click the lightning at the top to run the script. You’ll see a series of action outputs at the bottom, if there are green check marks it’s all good.

Screen Shot 2014-12-23 at 3.49.28 PM

On the left, you’ll now see a section called schemas. Right click > Refresh all and you should now see one called treehouse_movie_db. In there, open tables to see the names of each, each of which can be expanded to show the columns. So, this GUI lets us see a graphical representation of the db. Click on the treehouse_movie_db to select it, and it will turn bold.

Screen Shot 2014-12-23 at 3.52.21 PM

Close the movie_db import script tab, and in the query 1 tab type SELECT * FROM movies and you’ll see it returns the data. If you type movie you’ll get an error because that table doesn’t exist.

Screen Shot 2014-12-23 at 3.56.05 PM

Reading Data from Databases with SQL

Introduction to CRUD

CRUD stands for the four operations you’ll want to perform in the db – Create Read Update and Delete. Each has a SQL keyword associated with it. For Read, it’s SELECT.

Retrieving a Result Set

NOTE: the shortcut for execute is Command + Return. Before we had SELECT * FROM movies; for all movies. But, you can also write SELECT movies.title, movies.year FROM movies;.

Screen Shot 2014-12-23 at 4.14.38 PM

You can change the order of the columns returns by switching the column names. You can also write it without the movies before each column name as comma separated values. Note you don’t need a period here.

Screen Shot 2014-12-23 at 4.16.46 PM

If you just want data from only one column, write just that columns name.

Screen Shot 2014-12-23 at 4.17.47 PM

Querying Tables

clause lets you filter a result set. The where clause makes it so it only returns the data matching that condition. You right it at the end. Here, we test for movies where the year column equals 1999. Note that = is not an assignment operator here, but rather a comparison one.

Screen Shot 2014-12-23 at 4.21.29 PM

Use != to find the ones that aren’t from 1999.

Screen Shot 2014-12-23 at 4.22.36 PM

You can also use >, <, >=, <= as well. You can combine conditions using AND and OR. Here, we look for movies from 1999 with the title the matrix.

Screen Shot 2014-12-23 at 4.25.00 PM

To search between values, write the column name, then BETWEEN, then the number to start, the keyword AND, and then the ending number.

Screen Shot 2014-12-23 at 4.27.16 PM

You can search a string using the keyword LIKE with a combo of wildcards. If we were to search for “godfather” we’d get no results since the movie starts with “the”. The wildcard is a percent sign. Placing that before the g makes it wildcard, so anything that starts with something and then has godfather in it will come up. Note that LIKE is case insensitive.

Screen Shot 2014-12-23 at 4.30.04 PM

If we place a wildcard at the end of it as well, then it will bring up any godfather matches that also have something after them.

Screen Shot 2014-12-23 at 4.31.25 PM

Ordering the Result Set

Use the keyword combo ORDER BY to order results. You add it at the end, then what you want to order things by. See that is gives you an ascending order, and that null values come at the top.

Screen Shot 2014-12-23 at 4.35.49 PM

To have them descend, ass the keyword DESC at the end. If you needed to explicitly ascend them, add the keyword ASC to the end.

Screen Shot 2014-12-23 at 4.37.07 PM

You can chain ordering together by comma separating them. The first one listed is what the table sorts by, then the second sorts within the first column specified. So, here we have the table sorting by date, then for each year the titles sorting descending.

Screen Shot 2014-12-23 at 4.39.34 PM

Limiting the Result Set

If you don’t need all the results from the table, you can use the LIMIT keyword followed by the number of elements you want returned.

Screen Shot 2014-12-23 at 4.42.47 PM

To still pull in 10 but start later in your database, use OFFSET after that and a number, which is the number of rows you want to offset by.

Screen Shot 2014-12-23 at 4.43.49 PM

If you type OFFSET 0, it will start from the first row, so like an array the database starts counting the rows at 0. So, if you wanted to start at the 11th row, you’d offset by ten. You can also write this with just LIMIT, then two comma separated numbers, the first being the offset, and the second being the limit number.

Screen Shot 2014-12-23 at 4.46.22 PM

Note that if you don’t specify a limit, it will auto specify one for you at 0, 1000. You’ll find that some clients add one. If you were using a language like php or a command line tool though, these limits would not be there.

Screen Shot 2014-12-23 at 4.47.11 PM

Dealing with NULL

NULL is a value used to indicate data isn’t present in the database. To find the movies where the year is null, you’d simply write WHERE year IS null; Note that you can’t use = here, you need to use the IS keyword.

Screen Shot 2014-12-23 at 4.55.02 PM

We can combine this with our ORDER BY keyword to sort our movies and not include ones with a NULL year. In this case we’ll use IS NOT.

Screen Shot 2014-12-23 at 4.56.05 PM

Manipulating Data with SQL

Manipulating Schema and Data

The SQL syntax is split into two parts: DDL (Data Definition Language), which has to do with the keywords that create and modify tables and databases, aka schema, and DML (Data Manipulation Language), which deals with the CRUD operations in a database, aka the data.

Creating a Database

Think of a database as a container that keeps groups of tables separate from other groups of tables. To make a new one, in your workbench click the little icon that looks like a cylinder cut into three pieces.

Screen Shot 2014-12-28 at 2.53.58 PM

This makes a new schema, which is a database. Give it a name, and ignore default collection for now > Apply. Then, a screen will pop up showing you the keywords you’d use to do this by typing > apply > close > you’ll now see it on the left under schemas.

Screen Shot 2014-12-28 at 2.55.26 PM

Screen Shot 2014-12-28 at 2.56.21 PM

To select our new db from the schema list on the left, rather than clicking on it we’ll type USE movie_db_1;

Screen Shot 2014-12-28 at 2.59.04 PM

Note that if we try to make a new db with the same name we’ll get an error. The documentation on creating databases gives us more info. First it has the keyword needed, CREATE, then in curly braces it has the database and schema separated by a pipe, which means or. The words in the square brackets are optional.

Screen Shot 2014-12-28 at 3.02.34 PM

If we add the IF NOT EXISTS to our statement, we now get a warning rather than an error. This is good because when a script errors, it stops running, but with a warning it will continue on. Whether or not you’d want it to stop or not depends on the situation.

Screen Shot 2014-12-28 at 3.03.36 PM

Note that in the documentation database and schema are interchangeable, so we can use that to make a db as well. Make sure to right click > refresh your schema list after to see it there.

Screen Shot 2014-12-28 at 3.06.53 PM

You have the option to set your character set when making your db. To see the options, use the GUI to add a new schema, and select one from Default Collection. UTF-8 is the standard for the www. When creating it, it shows the nomenclature, which is DEFAULT CHARACTER SET utf-8;

Screen Shot 2014-12-28 at 3.09.38 PM

Screen Shot 2014-12-28 at 3.10.44 PM

However, the documentation says the default is optional, and that we can add an equals sign. This isn’t necessary, but shows we can modify it.

Screen Shot 2014-12-28 at 3.11.59 PM

Creating a Table

Here’s how we’d make some tables. CREATE TABLE, then the name, then the columns, commas separated in parentheses.

Screen Shot 2014-12-28 at 3.14.30 PM

The documentation on creating tables shows us how to do that. We have the option to make a temporary table, which is visible only in the current session, and is dropped automatically when closed. You can add IF NOT EXISTS like before. Then, you can create definitions, which would be the column name and its definition, which is its data type. You see that for the data type you have the option between not null, which means there has to be something set for it, or null, which means it can be empty. This would be good for our actor name, so we’ll add that in, and run the command to make the table.

Screen Shot 2014-12-28 at 3.22.24 PM

Now we’ll create one for movies. We always want the title, and don’t always need the year, so we’ll add not null and null respectively. Note that if we didn’t specify not null or null, it would default to null, so we don’t need to write it for year.

Screen Shot 2014-12-28 at 3.25.51 PM

Another way to make a table is to add table option, in this case ENGINE, which is a specific way to store data in the db. If we open a new tab, and type SHOW ENGINES, it will show us the engines available. InnoDB is the default and the one you’ll like want to use, but to specify one, simply add ENGINE then the name of the one you want at the end.

Screen Shot 2014-12-28 at 3.30.20 PM

Inserting, Updating and Deleting Rows

So all of that was DDL, this will cover the DML, aka the CRUD stuff. Here’s a table of the keywords you need for each part.

Screen Shot 2014-12-28 at 3.33.28 PM

Here we’ll insert a movie. Start with INSERT INTO, then the table you want to use, then VALUES, then in parentheses the values for the corresponding tables.

Screen Shot 2014-12-28 at 3.36.12 PM

If you didn’t know the ordering of the columns, you could list the column names between the table name and VALUES, and the values would correspond to how you wrote them.

Screen Shot 2014-12-28 at 3.37.42 PM

Another way to write this is to insert multiple rows at once, by adding a section set of values in a second set of parentheses.

Screen Shot 2014-12-28 at 3.39.08 PM

You can also use the SET keyword, then the column name equal to the value, separated by commas for each.

Screen Shot 2014-12-28 at 3.40.39 PM

If we SELECT * FROM movies, we can see the rows we’ve created.

Screen Shot 2014-12-28 at 3.41.17 PM

To update, use the UPDATE keyword, then with SET and a clause to only do this for movies where the title equals Avatar 2 (if we didn’t do this it would set it for all movies). Note that for this to work you have to turn off safe updates under Preferences > SQL Editor > uncheck Safe Updates, or you can write SET SQL_SAFE_UPDATES = 0; in a new tab.

Screen Shot 2014-12-28 at 3.45.17 PM

To update multiple values, simply comma separate them.

Screen Shot 2014-12-28 at 3.48.18 PM

To delete, us the DELETE FROM keywords, and a WHERE clause to ensure you don’t delete everything in the table.

Screen Shot 2014-12-28 at 3.50.07 PM

New terms from treehouse – Build a Simple PHP Application Part 2 – Integrating with PayPal, Working with Functions, and Wrapping up the Project

Integrating with PayPal

Creating a PayPal Account

This makes things wayyy easier than doing it yourself. Also, when someone adds something to their cart, you want it to stay there as they navigate the site. This is called persistence. Paypal offers a shopping cart service for this as well.

Configuring Tax and Shipping Rates

In PayPal, under My Account > Profile you can specify your business’s tax id. Under My Money you can add a bank account. You can also set up your sales tax under My Selling Tools. You can also set up shipping costs and options based on weight or number of products sold.

Creating PayPal Buttons

We’ll now make some buttons to add items to the paypal shopping cart. Under My selling tools > Paypal buttons > Update > Create New Button. Choose shopping cart for the type, so it adds the item to the cart. Give it a name, price and an item id, which should match the key from our associative array of products. Under Customize button you can add a drop-down for shirt sizes. When you’re done, click Create button. This will give you some code you can paste into your site.

Screen Shot 2014-12-12 at 4.51.05 PM Screen Shot 2014-12-12 at 4.52.11 PM

This form element has a POST method, and its action is set to paypal’s servers. The target attribute opens the page in a new window or tab. The hidden inputs aren’t displayed on the page but do get submitted with the form. The hosted_button_id is paypal’s id for our red shirt, to help prevent fraud. They then have a table for the drop down. The input with the name on0 lets us know this is the first drop-down. The input with os0 stands for the selection in that drop-down. We’ll add some markup like a label to make things more semantic. They give you an image type input for your submit button, but we’ll instead use the normal submit type with our own text. The last img element is used for tracking. It’s a 1×1 px transparent image that the user can’t see. When the page loads, the image is still requested from the server, which records that request. We don’t need this, so we’ll remove it.

Screen Shot 2014-12-12 at 4.58.18 PMNow we have this. When you click Add to cart, it will take you to the paypal cart page.Screen Shot 2014-12-12 at 4.58.18 PMScreen Shot 2014-12-12 at 4.59.51 PM

Screen Shot 2014-12-12 at 5.00.59 PM

Now we need to replace some of the info in that form with info from our product array, and understand the balance between security on paypal’s end, and ease of use on ours. If we try adding a new shirt size via html, like X-large, paypal will accept it. It will also accept if we decide to change the item_name via a hidden input element’s value.

Screen Shot 2014-12-12 at 5.06.41 PM Screen Shot 2014-12-12 at 5.00.59 PM

However, this means any visitor to our site can change the html and mess with things and paypal will accept it. The only value that doesn’t have this flexibility is price.

Let’s copy the hosted_button_id into our product array.

Screen Shot 2014-12-12 at 5.10.42 PM

We now need to make a button for each item in our catalog. To make it easier simply go back to paypal and click action > create similar button. Once you do that, copy the paypal id for each into your array.

Including the Products Array

Now we’ll make one page that can act as the details page for each of our 8 shirts. First we’ll make a file called shirt.php, and link to it in our shirts.php file. The format will be the same, only the data will differ and we have that in our products array. Since both pages need the array, it’s a good idea to put that in it’s own file so that both can link to it with an include.

Now, we need to link to the right shirt details page address. We can do this by adding a ? to the shirt.php link, then a variable name (in this case we’ll choose id, remember it’s arbitrary), then a value, which will be the shirt’s product_id from the array.

Screen Shot 2014-12-12 at 5.29.08 PM

However, what we need it to do is pull that from each array in the array, so we don’t need to manually write it each time. Right now in the foreach loop we don’t have access to that, only the main array, not the sub ones.. We can load the key of each element into a variable using the =>. This will give us two working variables in the foreach loop, one with the key and the other with the value.

Screen Shot 2014-12-12 at 5.31.22 PM

Screen Shot 2014-12-12 at 5.30.35 PM

Screen Shot 2014-12-12 at 5.28.06 PM

Remember the key name of each array was the number we gave, 101, 102, etc. When you click on the shirt, it takes you to the appropriate page.

Screen Shot 2014-12-12 at 5.38.07 PM

Building the Shirt Details Page

For the shirt.php page, we can access the id from the pages web address using the $_GET variable. First, we’ll load that into a regular php variable. We can then choose the array we want by putting it’s key in square brackets after the main array. Rather than typing a specific number, we’ll use the variable declared above, which is set by the id in the url. $product_id is the key, $product is the array itself.

Screen Shot 2014-12-12 at 5.43.33 PM Screen Shot 2014-12-12 at 5.43.24 PM

If you click a different shirt, you’ll get a different array.

Now, to set up the page. Like the other pages, first we’ll set the section and page variables and include the header and the footer. Then, we’ll set up the html. Note how we dynamically pulled in the name of the shirt.

Screen Shot 2014-12-12 at 5.51.49 PM

We’ll dynamically add the picture as well, using php for it’s src and alt attributes.

Screen Shot 2014-12-12 at 5.56.41 PM

Now to do the details on the right.

Screen Shot 2014-12-12 at 6.00.15 PMScreen Shot 2014-12-12 at 6.00.20 PM

Finally, we’ll pop in the form for the paypal button. We need to add some php so that the button is customized for each shirt.

Screen Shot 2014-12-12 at 6.02.07 PMScreen Shot 2014-12-12 at 6.02.20 PM

Redirecting Invalid Shirt ID’s

What if someone manually changes the web url to an invalid id? We can check this using a conditional at the top of the page, and redirect them back to the shirt.php page if it’s invalid. Let’s do a quick test and echo out our $product and $product_id variables. You can see if the url is invalid, there is still a $product_id, but $product comes out as null, since there’s no array for it.

Screen Shot 2014-12-12 at 6.07.38 PM Screen Shot 2014-12-12 at 6.08.49 PM Screen Shot 2014-12-12 at 6.07.30 PM

We’ll start with a conditional with the isset function to make sure that the $_GET variable named id exists. If it does, it’ll set the product_id variable. Next, we’ll check if our main products array has a shirt with that id in it. If it does, then it sets the $product variable on this page. Using else blocks, if there is no id at all, it will say that message. If there is an id but it’s invalid, then that message will be displayed.

Screen Shot 2014-12-12 at 6.17.58 PM

We can write a second conditional after that that checks whether $product has been set. Due to the previous code block, if it was, we know it was a valid ID.

Screen Shot 2014-12-12 at 6.19.38 PM

The negation operator ! let’s you check if something is NOT something. Here, we make it so that if the $product variable is not set, it redirects back to teh shirts.php page using the header variable.

Screen Shot 2014-12-12 at 6.24.04 PM

Adding Available Sizes

We can customize the shirt sizes for each shirt by adding a new value to our second dimension array, which will actually be another array, a third dimension array.

Screen Shot 2014-12-12 at 6.27.10 PM

Now we can modify our form to use that array instead of the static html. We will use a foreach loop like we did before for the shirts page.

Screen Shot 2014-12-12 at 6.29.58 PM

The shirt sizes listed on the page will now update dynamically based on what is listed in the main product array.

Working with Functions

Introducing Functions

Now to update the main landing page. There are some shirts here, but right now they’re just using static html, rather than the stuff we set up for the shirts page.

Screen Shot 2014-12-13 at 4.43.55 PM

First, we’ll include the product array right above it. Then, we’ll rewrite the list to be generated by a foreach loop. However, we’ve already done something like this before, so it might be better to set up a function to use. Functions usually perform a small, specific task, and don’t do anything until they’re called. There are three parts to a function: 1. Its name. 2. The argument list – a list of values you can tell it you will pass it when it executes it code. 3, It’s return value. In the example below, the name is count, the argument (which you put in parentheses after the name) is the array, and the return value it gives you is the number of elements in the array. Other examples include isset and date.

Screen Shot 2014-12-13 at 4.54.25 PM

User-Defined Functions

Here, we’ll make a function that mimics the count function. First, write the function command, then its name. Then, parentheses with the argument list, in this case $array. We can then use that variable $array in our code. Then, curly brackets which will surround the code block that will be executed when the function is run. Note that variables created outside of a function are not available inside of it.

Screen Shot 2014-12-13 at 5.01.54 PM

Now to make the code to be run. We need it to count the items, so we’ll make a variable and add 1 to it during each run of the loop, which will give us the total. Before that we’ll make a variable for count and set it to 0. Note that this $count variable is completely unrelated to the $count one outside of the function.

Screen Shot 2014-12-13 at 5.04.37 PM

To return this value back to the main code with the return command. Then in our main code we can replace count() with our mimic_count function.

Screen Shot 2014-12-13 at 5.06.00 PM

Screen Shot 2014-12-13 at 5.06.40 PM

Creating the Shirt Display Function

Here’s the gist of the function we need here:

Screen Shot 2014-12-13 at 5.12.30 PM

Since we need this in both shirts.php and index.php, we’ll put it in an include file, and since it will only be used with the products array, we can just put it in there. We’ll copy over the code from shirts.php that already does this. Rather than returning a bunch of echo’d things, it’s better to use the technique before where we make a blank variable and add things to it with concatenation.

Screen Shot 2014-12-13 at 5.18.24 PMScreen Shot 2014-12-13 at 5.19.01 PM

Now to use this function in our pages. Remember that we need to pass in the arguments as well. What’s nice is that now whenever we need to have a list items of shirts, we can just use this function.

Screen Shot 2014-12-13 at 5.23.24 PM Screen Shot 2014-12-13 at 5.23.16 PM

Displaying Only Four Shirts

However, on the home page we only want four shirts displayed. First we’ll make an element called total_products, and set it equal to the number of elements in the array, using the count function so the number will always be accurate. We’ll create a second variable called position and set it 0, which we’ll use to keep track of how many items we’ve looped through. In our foreach loop, we’ll add one to it each time we loop.

Screen Shot 2014-12-13 at 5.28.59 PM

Now we’ll add a conditional to only add the shirt if it’s one of the last 4 elements in the array. The logic will go like this – we can subtract the total number of shirts from the position. When the result less than 4, we know we’re on the last 4 shirts in the array. Total products = 8, position = 5, remaining = 3. So, the math will be total products – position < 4.

Screen Shot 2014-12-13 at 5.34.51 PM

Now, let’s also make it so it adds to a string like we did before. We’ll make an empty variable called list_view_html before the foreach loop. In the loop we’ll have it concatenate to that variable each time it loops. Then, we can echo the variable containing the for list items outside the loop.

Screen Shot 2014-12-13 at 5.37.56 PM

Screen Shot 2014-12-13 at 5.39.21 PM

If we had a ton of shirts, looping through each one like this to find the last four might not be the most efficient approach, but this is okay for now.

The last thing we’d like to do is have the shirts appear in reverse order, so the most recent one comes first. To fix this, we’ll modify the line of code with the concatenation and have the shirt get added to the beginning of the variable.

Screen Shot 2014-12-13 at 5.41.08 PM Screen Shot 2014-12-13 at 5.41.05 PM

Wrapping Up the Project

Validating Contact Form Data

Now to set up the email that gets sent when you fill out the form. First we need to validate the form data we receive. We want to make sure that each field has a value entered, starting with name.

Screen Shot 2014-12-13 at 6.11.00 PM

But what if someone just puts spaces? There’s a php function called trim you can use which will remove the white space at the beginning and end of the text. We can put it in our conditional, but then that doesn’t help us if we need a trimmed name later in the code.

Screen Shot 2014-12-13 at 6.12.51 PM

Instead, let’s trim it up top when we set the variables.

Screen Shot 2014-12-13 at 6.14.39 PM

Next we’ll make the conditional return false if the name, email, or message are blank, using OR.

Screen Shot 2014-12-13 at 6.17.37 PM

If you submit any of those blank, it now takes you to a new page with just the echo message.

Screen Shot 2014-12-13 at 6.18.30 PM

Ok, now we need to protect our form from spam robots. 2 kinds 1. Fill out all forms hoping they’ll be displayed on a website somewhere, like a comments section. 2. More harmful – they have a way of using your form to send out emails to thousands of other people. This is called the email header injection exploit. There it’s described and a code snippet is provided to protect against the attack. Basically, it’s a foreach loop that goes through each post submission to see if it contains a malicious value, and if it does, uses exit to shut it down. We’ll add an error message as well with echo.

Screen Shot 2014-12-13 at 6.26.43 PM

To combat the first kind of spammer we’ll use a spam honeypot field. The spammer fills out every field in the form, so we’ll add one our normal visitors can’t see with a normal name and id like address, then hide it with display:none;

Screen Shot 2014-12-13 at 6.30.28 PM

Then up top, we’ll add a conditional to see if that field has a value, and if it does display an error message and exit it.

Screen Shot 2014-12-13 at 6.32.42 PM

Finally, if there’s an issue with the CSS and a real user accidentally sees it, we’ll add a note to let them know to leave it blank.

Screen Shot 2014-12-13 at 6.33.48 PM

Using a Third-Party Library

A third-party library is basically a set of include files that other people maintain. We’ll use phpmailer to send our emails. The file we need is class.phpmailer.php, which you should copy over to your project into the inc folder, in it’s own folder. Each library you use should get its own folder in the inc folder.

The require_once command is like include, the difference being that if there’s an error, include will give an error but still execute the rest of the code. But, require_once will instead give an error and execute no more code. There’s require, require_once, include, and include_once. The once ones are good if you only want a file to be loaded once, like if it contains functions or sends emails.

Screen Shot 2014-12-13 at 6.44.49 PM

Next we’ll create an object from phpmailer named $mail. This has a method that let’s you validate email addresses. To access an object method or property, first write the objects name, then ->, then the method name, the argument list in parentheses, then curly brackets for the code.

Screen Shot 2014-12-13 at 7.00.06 PM Screen Shot 2014-12-13 at 7.00.01 PM

Sending the Contact Form Email

Now, we’ll use code from the phpmailer example file to set up our email.  Notice that we’re using the -> to set different methods for our $mail object.

Screen Shot 2014-12-13 at 7.05.58 PM

Now, a lot of what we need is already in the variables created from the form submission, so we’ll go through and replace those out. AddReplyTo adds new reply to names to the email. Since we want to reply to the person who filled out the form, we’ll use the variables. SetFrom let’s us set who is sending us the email, in this case once again the person who filled out the form. Since we have that, we don’t actually need the AddReplyTo. The $address variable let’s us set the recipient of this email, in this case us, which we’ll then plug into the AddAddress method.

Methods have parentheses after their names for the argument lists, properties do not. For the Subject property, we’ll edit the message and concatenate the person’s name to the end of it. This makes things more clear and prevents each email from having the exact same subject and getting them all grouped in the same convo. The msgHTML() method sets the body of the email, and we’ll pass in our $email_body variable.

The conditional at the end actually sends the email, calling the method name within it. Here’s how this works. PHP calls the send method, which should send the email, and will return a value of true or false. Since it’s what our conditional is checking, if it’s true, the if block will execute. To check if it did not work we can use the negation operator !.

Screen Shot 2014-12-13 at 7.17.05 PM

If there’s an error, we’ll display a message and stop our code from executing. ErrorInfo doesn’t have parentheses after it so we know it’s a property and not a method.

Screen Shot 2014-12-13 at 7.19.14 PM

The final thing to do is to modify the email body so it uses html break tags instead of php ones.

Screen Shot 2014-12-13 at 7.19.59 PM

If you don’t use a separate mail server, it will use your sites server to send the email, which isn’t recommended.

Deploying the Site

In Paypal, under my selling tools you can upload your logo to your cart page. You can also have it redirect back to your site once the transaction is complete. We’ll make a receipt.php page for that. $section can be set to none, because we don’t need anything underlined in the main nav.

Screen Shot 2014-12-13 at 7.29.30 PM

Now, we need to make the cart picture in the nav actually link to the cart. It’s under paypal > my selling tools > paypal buttons. We can make this into one link using GET variables using a ? to start, then variable=value, then & to add each new one.

Screen Shot 2014-12-13 at 7.31.41 PMScreen Shot 2014-12-13 at 7.32.38 PM

We can now post that link in our header file. Make sure to escape the ampersands with &amp;. The link now works.

Now, load up your site to your server! It’s good to go.

Finishing PayPal Configuration

You can customize your payment page under my selling tools.