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


Using COUNT

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

Advertisements

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.

Screen Shot 2014-12-28 at 4.23.11 PM

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

Screen Shot 2014-12-28 at 4.24.10 PM

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.

Screen Shot 2014-12-28 at 4.27.20 PM

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.

Screen Shot 2014-12-28 at 4.29.46 PM


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.

Screen Shot 2014-12-28 at 4.34.06 PM

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

Screen Shot 2014-12-28 at 4.35.08 PM

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.

Screen Shot 2014-12-28 at 4.38.04 PM

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

Screen Shot 2014-12-28 at 4.39.21 PM

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

Screen Shot 2014-12-28 at 4.40.33 PM


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.

Screen Shot 2014-12-28 at 4.46.07 PM


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.

Screen Shot 2014-12-28 at 4.53.19 PM

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.

Screen Shot 2014-12-28 at 4.54.22 PM


Keys and Auto-Incrementing Values

There are different kinds of keys.

Screen Shot 2014-12-28 at 4.55.55 PMScreen Shot 2014-12-28 at 4.56.25 PMScreen Shot 2014-12-28 at 4.56.45 PM

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.

Screen Shot 2014-12-28 at 5.04.30 PM

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.

Screen Shot 2014-12-28 at 5.06.58 PM

Screen Shot 2014-12-28 at 5.09.10 PM

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.

Screen Shot 2014-12-28 at 5.12.20 PM Screen Shot 2014-12-28 at 5.12.12 PM

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.

Screen Shot 2014-12-28 at 5.21.19 PM

Screen Shot 2014-12-28 at 5.22.10 PM

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

Screen Shot 2014-12-28 at 5.24.08 PM Screen Shot 2014-12-28 at 5.24.03 PM


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.

Screen Shot 2014-12-28 at 5.49.43 PM

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.

Screen Shot 2014-12-28 at 5.51.56 PM

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.

Screen Shot 2014-12-28 at 5.53.13 PM

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.

Screen Shot 2014-12-28 at 5.55.54 PM

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.

Screen Shot 2014-12-28 at 5.57.27 PM

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

Screen Shot 2014-12-28 at 6.00.02 PM

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.

Screen Shot 2014-12-28 at 6.14.38 PM

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

Screen Shot 2014-12-28 at 6.15.58 PM

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

Screen Shot 2014-12-28 at 6.16.40 PM

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.

Screen Shot 2014-12-28 at 6.19.09 PM

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

Screen Shot 2014-12-28 at 6.22.12 PM

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.

Screen Shot 2014-12-28 at 6.32.11 PM

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

Screen Shot 2014-12-28 at 6.33.21 PM

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.

Screen Shot 2014-12-28 at 6.36.54 PM

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

Screen Shot 2014-12-28 at 6.38.41 PM

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

Screen Shot 2014-12-28 at 6.40.12 PM

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.

Screen Shot 2014-12-28 at 6.41.48 PM

Screen Shot 2014-12-28 at 6.43.51 PM


String Functions

Here we have our users table.

Screen Shot 2014-12-28 at 6.50.55 PM

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

Screen Shot 2014-12-28 at 6.52.34 PM

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

Screen Shot 2014-12-28 at 6.54.28 PM

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

Screen Shot 2014-12-28 at 6.57.10 PM

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.

Screen Shot 2014-12-28 at 7.00.52 PM

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.

Screen Shot 2014-12-30 at 2.50.46 PMScreen Shot 2014-12-30 at 2.51.25 PM

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.

Screen Shot 2014-12-30 at 2.55.40 PM

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.

Screen Shot 2014-12-30 at 2.58.24 PM

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.

Screen Shot 2014-12-30 at 3.01.14 PM

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.

Screen Shot 2014-12-30 at 3.03.00 PM

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

Screen Shot 2014-12-30 at 3.05.59 PM

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

Screen Shot 2014-12-30 at 3.07.32 PM

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.

Screen Shot 2014-12-30 at 3.08.47 PM


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.

Screen Shot 2014-12-30 at 3.27.26 PM

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.

Screen Shot 2014-12-30 at 3.29.36 PM

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.

Screen Shot 2014-12-30 at 3.41.43 PM

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.

Screen Shot 2014-12-30 at 3.44.18 PM

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.

Screen Shot 2014-12-30 at 3.49.55 PM


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.

Screen Shot 2014-12-30 at 3.59.23 PM

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.

Screen Shot 2014-12-30 at 4.02.27 PM

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.

Screen Shot 2014-12-30 at 4.06.33 PM

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.

Screen Shot 2014-12-30 at 4.09.21 PM

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.

Screen Shot 2014-12-30 at 4.13.05 PM

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

Screen Shot 2014-12-30 at 4.14.55 PM