New terms from treehouse – The WordPress Template Hierarchy Part 2 – Archive Templates in WordPress, Custom Post Type Templates in WordPress, and Miscellaneous Templates in WordPress

Archive Templates in WordPress

Archive Templates in WordPress

One archive template can work for a range of different purposes, for blog posts archived by date, author, categories, etc. archive.php is the fallback template for all of these, but you can make custom ones for each type.

Looking on the left, you can see they’re divided by author, category, custom post, custom taxonomy, date and tag. You have a main template for each (the ones in light blue), as well as more specific ones that use the slug or id method we saw before.

---

Date Archives

This uses the date.php file. If we look at our blog and are using the archives widget, we’ll see we can click on a certain month.

The url ends with 201412, and if we remove the month it will show us everything for 2014. Looking at the template you can see that date.php is the main fallback, but we could make a template for year, month and day as well.

In the date.php file we can see a conditional that checks if it is_year, which means a year based archive, and acts accordingly adding the months if it is.

---

Category Archives

Uses category.php, but can be more specific and use the slug/id naming method. You can manage your categories under posts > categories. We have the category widget on our blog page, so we’ll see that on the right. We can then make custom templates for each with a slug like category-travel.php or the category id like category-4.php. You can find the cat id by the url on the site, or by editing one.

Note that for the styling, the body_class classes make this a lot easier. For the travel page, the category-travel class is what we’re using for our rules.

---

Author Archives

If you click the author’s name on a blog post, it will take you to the author page for that person.

You can add more users under Admin > Users > Add New. We’ll add one named editor with the Role editor.

In our author.php file we have some custom code to display the name, picture, etc of the author, then a loop to pull in their recent posts. Since this is an author.php page, WP knows to only display posts by that person.

Now, you can also make a template based off the authors “nicename”, which is their username, or their author id. So, I could do one called author-kyle.php. So, you can make a custom template for each author if you wanted to do that for some reason.

---

Custom Post Type Templates in WordPress

An Important Review of Custom Post Type Setup

Quick Review:

You’ll need the plugins Custom Post Types UI. Install and activate, then add a new one for portfolio. Click Advanced Options. If you set Has Archive to true, it will automatically use the archive.php or archive-posttype.php page for its archive. If it’s set to false, you can still query out the data and make your own, but WP won’t automatically do it.

For the Rewrite option, this lets WP rewrite the URL’s for custom post types for the archives and permalinks. The Custom Rewrite Slug lets you change the main slug you set at the top to something else, like if you wanted to replace portfolio with work.

We can now add this to our menu by linking to it. The link should be yourdomain/portfolio. Note that you need to make sure you’ve set your permalinks to use the post name rather than the default setting, or this won’t work.

If we were to edit the archive.php template, we could see the changes on this page now. However, we could get more specific and make a file archive-portfolio.php, and WP would use that template instead.

---

Custom Post Type Templates Continued

Sometimes if you change a template and refreshing the page doesn’t show the changes it may be a caching issue and you’ll need to either click around or do a hard refresh.

If when setting up our custom post type, if we had the Archive option set to false, we’d get a page not found if we tried to go to the portfolio archive.

Another option is to make your own custom page template, for example page-portfolio.php, where in the comments at the top you’d write its name. The template would contain a WP_Query loop where it would include posts that had the type portfolio. You’d then make a page named portfolio, and set it to that template.

So, what happens if we have Archive set to true and make a custom page with a template? The custom page will take priority and will be the one displayed. Still, either approach works and it depends on your situation.

Looking at the hierarchy, when you have a single page, and it’s a custom post type you can control it with single-posttype.php, in this case single-portfolio.php. If you don’t have that file, it will default to the single.php template.

Note that there’s a broken link on the single-portfolio.php page for the header element, because it’s link to the page id. Change that to portfolio and it will work.

---

Miscellaneous Templates in WordPress

Media Templates

When adding a media file, you’ll see a Link To option on the right, which lets you link directly to the file, and if someone clicks on it, it will take them to a page of just that photo. You can copy the link under it to see. The Attachment Page option lets us set up a special template so the image is displayed surrounding by other content. The Custom option lets you link to anything, and the none is no link.

We’ll be using attachment page here. When clicked, it will take you to the template for that.

In the hierarchy, these fall under Single page > Attachment post > mimetypes.

Mime types are the different types of media files you’ll find on the web. WP has a function get_allowed_mime_types that will show you the ones you can use on your site. We can make templates based off of the mime types, like one for images, one for movies, etc. Note that when looking at the hierarchy, the mime type (like image.php) overrides the subtype (like png.php). If you don’t have any files for a specific mime type though, it will fall back to attachment.php.

---

Search Templates

The search.php template controls what our search page looks like. To add a search form, you can either add one as a widget, or use the function get_search_form, which displays a search form for you. It does this by loading the the file searchform.php in your template folder. If you don’t have that file, WP will use the default search form.

If you use the search, it will take you to the results page based on the search.php template, and the url will have a query string ?s=yoursearchterm. WP will know what to link to if you use the_permalink as your link.

Note that you really should include an else block in your loop, for if there are no matching results. Here we give a message and a search form for them.

---

404 Pages

The 404.php template displays if the page someone looking for is not found. You should display a message saying so. With 404 pages, you can either have it be entertaining/humorous, or, engaging, as in having it try to help them figure out what they may have done wrong, or list the most popular pages or give them a search bar.

New terms from treehouse – Database Foundations Part 2 – Manipulating Schema with SQL, Joining Relational Data Between Tables in SQL, SQL Calculating, Aggregating and Other Functions, and Securing and Maintaining a MySQL Database

Manipulating Schema with SQL

Altering Tables

To rename a table, type RENAME TABLE, then the table name, the TO keyword, then the new name. Remember, you can use more than one statement, so long as you have a semi colon at the end of each.

However, you can write that in one statement by comma separating them.

To delete a table, use the DROP keyword, then TABLE, then its name. You can optionally add IF EXISTS to make it so that if the table doesn’t exist it just throws a warning rather than an error.

To remove a table without deleting it forever, you can use the TRUNCATE keyword, then the table name (you don’t need the TABLE keyword). Note that this differs from DML, it’s still DDL. It actually deletes the table, then makes a new one with the same name but no rows in it.

---

Altering Columns

To add a column, write ALTER TABLE, then the table name, then ADD, then the optional keyword COLUMN (to specify what we’re adding), then its name and data type.

To add multiple columns, simply wrap them in parentheses and comma separate them.

To change the names of preexisting columns, replace ADD with CHANGE. Once again COLUMN is optional. Then, write it’s old name, a space, then its new one, then its data type definition. Unlike ADD, CHANGE cannot accept multiple columns. You’d need a second statement.

Now, for our year column in the movies table, year is actually a MySQL keyword, so its better to rename it to something else.

To remove a column, you’d just use DROP.

---

Removing Databases

You used to be able to rename a db, but they removed it because it was dangerous. It’s better to back one up, then move that data into a new one.

Deleting a db deletes the schema and the data.  To delete a db, you’d write DROP DATABASE (or SCHEMA), an optional IF EXISTS, then the name of the db you want to delete.

---

Joining Relational Data Between Tables in SQL

Normalization

SQL_SAFE_UPDATES is there to make sure we’ve modeled our data in a safe way.

So, we’d make a separate table with the different movie genres listed, each with an id, that we could then reference in another table.

---

Keys and Auto-Incrementing Values

There are different kinds of keys.

We’ll now use the treehouse db, and create a new table called genres, with the needed column definitions. We’ll add PRIMARY KEY for the id one, to specify it as such. Before that we’ll add AUTO_INCREMENT, which increases the value by one with each new row. The name is a unique key, so we’ll add UNIQUE, with an optional KEY. Since unique keys can be null, but we don’t want that, we’ll add NOT NULL, where specified by the documentation.

Now, lets add a genre to our table. If we try adding that same one a second time, it will give us an error, due to that fact that that column is for unique keys.

Next we’ll add a id column to our movies table, which will auto increment and be a primary key. We’ll also make it the first column in our table by adding FIRST at the end.

Now we’ll add an genre_id, which we don’t want to increment (since we’ll use the same one for multiple rows), nor be primary or the first column. It can be null so we’ll add NULL. We want a constraint so only certain values can be put into this column, in this case the ids that are listed in the genres table. To do that we write ADD CONSTRAINT, then FOREIGN KEY, then the column name to be affected by it in parentheses, then REFERENCES, then the table name, and its column in brackets. Note that even though I broke it onto different lines it’s still one statement.

Now we’ll add the genre id of sci fi to the aliens movies. In our genres table, sci fi is 1.

---

Joining Tables and Aliasing

To join our two tables, we’ll do SELECT * FROM, then JOIN between their names, then ON for the condition where we set two columns equal to each other, in this case the foreign key on the movies table, genre_id, with the primary key on the genres table, id.

The JOIN here is known as and can also be written as an INNER JOIN, which is the data between the two that overlaps in both. If you want to pull in data outside of the overlapping data, write LEFT OUTER instead.

The left here means that we get all the info from the left join, the movies table, plus the inner join columns. If we wrote RIGHT we’d get this.

Let’s say we just wanted to return the title from the movie table and the genre from the genre table. As we’ve seen, to select a column in a table you write table_name.column_name. So, we can replace the star with the needed columns.

But for the name column, it’s not very clear that it’s the genre name. So, we can use aliasing, which lets us rename that. To do that, after the column name simply add AS, then the new name.

Note that these alias’s can’t be queried on later in the string, you’d have to the table_name.column_name format.

If the column name is unique, and only in one table, you can just write its name, but if it’s not and is in both tables, like id, it won’t work.

---

SQL Calculating, Aggregating and Other Functions

Numeric and Aggregate Functions

We have a new table for reviews, containing columns for id, movie_id (a foreign key) and a score. We’ll check out our table where movie_id = 1, which if we check our movies table we can see is the movie alien.

To see how many reviews there are, use the COUNT function, which takes an argument, in this case * for all the columns.

Once again, we can use an alias to make the column name more readable.

To check for the minimum value, we can use MIN, and pass it the column we want to check, in this case score. MAX works the same way.

To get the average we could use SUM() and divide it by the COUNT(), but it’s better to use the AVG() function.

Keep in mind that at this time these values are not being stored anywhere.

---

Grouping, Joining and Cleaning Up

If we were to remove our WHERE clause, it would average for all movies. Even if we selected the column for movie.id, it would still only return one row.

So, we can add GROUP BY at the end, then what we want to group the returned values by, in this case movie_id.

But just returning the id doesn’t make much sense, so we’ll use a INNER JOIN, which will return the subset of info that is common between the two.

But what if a movie doesn’t have any reviews yet? We can use a LEFT OUTER JOIN and see Tron has no reviews.

We can use IFNULL, which takes two arguments, the thing to check for, and what to put if it’s null.

To filter for results with an average above 3, use the keyword HAVING, then the condition. You use HAVING after grouping and aggregating, and since we used a GROUP BY, we’ll use that here. If we wanted to use a WHERE clause we’d have to do so before it the GROUP BY.

---

String Functions

Here we have our users table.

Note that the email column has some upperspace characters.  We can use the LOWER function to lowercase all of them. UPPER makes thing uppercase.

LENGTH() gives you the length of a string. Lets find people with a username less than 19 characters.

CONCAT() lets you join strings together. If you want a space, make sure to add one.

The SUBSTRING() method takes three arguments, the string to truncate, the the character to start from, and the one to end from. Here, we shorten the emails. Note that in SQL, they don’t start with 0, but with 1. We can also use CONCAT to add some dots to the end.

There are lots of functions. Learn more about them in the documentation.

---

Securing and Maintaining a MySQL Database

Indexing Columns in MySQL

If you’re filtering over a certain column frequently, you should add an index to improve performance. It’s like having an address book for your data. Without one, the db would have to search through the whole column for a name, but with one it can make things much faster.

Keep in mind though that having an index means that when you write to a db, it also has to write to an index.

Let’s look through our users table for people with the last name Chalkley. When we run the command, the db is going through each row and checking if the last_name column equals Chalkley.

We can use the keyword EXPLAIN at the start of our statement to get some ideas on whats happening when we run this command. Under type, we see that it’s running against all rows, under rows we can see its running through all three rows, and under Extra we can see its using the WHERE clause.

We’ll make an index for last name to improve this search. Start with CREATE INDEX, then the name you want to give it, then ON, then the name of the table, with the name of the column you want it on in that table in parentheses.

Let’s look at our explained query again. We can see now that the type is ref, and the keys it’s looking on is the last_name_idx. For rows, it’s just going to one, and under Extra we can see that it’s using the index condition.

We’ll now add a new user to the database with the same last name.

If we explain our search again, we’ll see that it now brings up two rows, so Henry was added to the index.

If we make it more specific and search for a first name as well, we can see that it still searches for 2 rows based on the index, but is also using the WHERE clause.

---

Setting up Multiple Users

It’s good to think about what permissions you want to give users, to keep them from messing with your database in ways you don’t want them to. To make a user, start with the GRANT keyword, then the permissions you want to give them. The first user will only have read permissions, which we’d write as SELECT. Then we’d write ON, then the name of the db we’re referring to. Following the db name with a .* means it includes all the tables in the db.

Then, you write TO, then the name of the user, then @’%’, which means the user is at a particular host name address. So, a user with this username and the password we specify can connect. If this were for a certain website, you may want to instead specify your web server. Finally, you write IDENTIFIED BY, then the password in single quotes.

To be able to connect as user1 though, you must first do FLUSH PRIVILEGES, which resets and reloads all the privileges of all the users for the database.

Now, at this point you’re supposed to be able to create a new connection for that user, using their username and password, but this didn’t work for me at all and unsurprisingly treehouse provided no help.

Signed in as the user, you’ll find you can SELECT * FROM to view tables, but cannot edit them in any way. You’ll get an error if you try.

To make a user with read and write permissions, you’d simply add more keywords – INSERT, UPDATE and DELETE. Make sure to flush the privileges when you’re done.

If you create a connection for this user and use it, you’ll find you have access to all the DML CRUD operations. However, if you try to drop a table, a DDL command, it will be denied.

To create a user with DDL permissions, use the keywords ALTER, CREATE, and DROP.

---

Backing Up and Restoring Databases

If you load up your connection and choose management on the left hand nav, you’ll see the Data Export option. You can choose what db you want to back up, and even which tables within it. You can then choose whether to export into separate files, or all into one. Choose your file path and make sure you use a .sql extension, and click Start Export.

If there’s a discrepancy between versions, you may get an error. To fix this, go to Preferences > Admin > Path to MySQL dump tool > /usr/local/mysql/bin/mysqldump.

So, keep in mind that the GUI interface is just running commands for you. If you typed it yourself in a command line, it would start with the path to the dump, then some parameters for the hostname, user and path to the db.

Once you have the file, you can open it with a text editor to view it.

Notice it starts by creating the db, if it doesn’t already exist, then the next line is to USE it. Then it creates the tables, first by dropping them if there’s already one with that name, then creating one and inserting the values. If you wanted, you could modify this and rename the db to something like treehouse_movie_db_clone.

To restore a db, select the Data Import/Restore option from the left nav > Import from Self-Contained file > choose path to file. Under Default Target Schema you can choose which one you’d like to use for the queries to be run in. However, this is only used if the dump file doesn’t contain its schema (our first two lines to create and use), and is otherwise ignored. So, it will actually be ignored here. Click Start Import > Ok.

Now after refreshing our schemas, we see our clone. If we hadn’t changed the name, it would have overwritten our previous db.