PHP / MySQL Tutorial

MySQL is the most popular open source database server in existence. On top of that, it is very commonly used in conjunction with PHP scripts to create dynamic and powerful server applications.

 

MySQL has been criticized in the past because it does not have all the features of other DataBase Management Systems. However, MySQL continues to improve significantly, with each major upgrade, and has great popularity because of these improvements.

A database is a structure that comes in two flavors: a flat database and a relational database. A relational database is much more oriented to the human mind and is often preferred over the gabble-de-gook flat database. MySQL is a relational database.

 

In a relational structured database there are tables that store data. Each table is made up of columns and rows. A table's columns define what kind of information is going to be stored. You need an individual column for each type of information you wished to store (i.e. Age, Weight, Height).

 

While columns define what kind of information is going to be stored, a row is the actual data that is stored. Each table row is an entry of that table and contains values for each of the table's columns (i.e. Entry Bob: 65 years old, 165 lbs, and 6'2")..

Databases are most useful when it comes to storing information that fits into logical categories. For example, say that you wanted to store information of all the employees in a company. With a database you could group different parts of your business into separate tables. Example tables might be: sales, employees, debt, performance measurements, etc.

MySQL Syntax

The great thing about everything you do in MySQL is that the "code" is very human readable, as opposed to hard programming languages like C or C++. There are very little special characters and symbols that are required to create a MySQL query and most queries consist entirely of English words!

 

The MySQL language is not as complicated as most programming languages and so we find that the best way to learn is through direct examples. Because we are going to focus on the combination of MySQL in a PHP context, most of the examples are ready to be copied and pasted into your php scripts.

 

There are many keywords in MySQL and a good programming habit when using ANY of these words is to capitalize them. This helps draw them out from the rest of the code and makes the codes much easier to read. Below is an example MySQL query written in PHP that retrieves all the data from a MySQL table named "example".

$result = mysql_query ("SELECT * FROM example")

As you probably can tell "SELECT" and "FROM" are the MySQL keywords that were used in this query.

 

A MySQL database is nothing in itself. Rather a MySQL database is a way of organizing a group of tables. If you were going to create a bunch of different tables that shared a common theme, then you would group them into one database to make the management process easier.

 

Most web hosts do not allow you to create a database directly through a PHP script. Instead they require that you use the PHP/MySQL administration tools on the web host control panel to create these databases. Create a database and assign a new user to this database. For all of our beginning examples we will be using the following information:

       Server - localhost

       Database - test

       Table - example

       Username - admin

       Password - 1admin

Note: The table may change in the advanced lessons, but everything else will remain the same!

The server is the name of the server we want to connect to. Because all of our scripts are going to be placed on the server with MySQL the correct address is localhost. If the MySQL server was on a different machine than where the script was running, then you would need to enter the correct url!

Your database, table, username, and password do not have to match ours. If you choose a different set of information, remember to replace our code with your information when copying the scripts found in this tutorial.

Before you can do anything with MySQL in PHP you must first establish a connection to your web host's MySql database. This is done with the MySQL connect function.

If you've been around the internet a while you'll know that IP addresses are used as identifiers for computers and web servers. In this example connection script we will assume that the MySQL service is running on the same machine as the script.

When the PHP script and MySQL are on the same machine, you can use localhost as the address you wish to connect to. localhost is a shortcut to just have the machine connect to itself. If your MySQL service is running at a separate location you will need to insert the IP address or URL in place of localhost.

PHP & MySQL Code:

 

<?php
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("test") or die(mysql_error());
echo "Connected to Database";
?>

 

Display:

Connected to MySQL
Connected to Database

 

So far you should have made a MySQL connection and chosen the working database. If you are up-to-date then continue the tutorial. We will be making our first table in the next lesson.

 

MySQL Tables

A MySQL table is completely different than the normal table that you eat dinner on. In MySQL and other database systems, the goal is to store information in an orderly fashion. The table gets this done by making the table up of columns and rows.

This table has three categories, or "columns", of data: Age, Height, and Weight. This table has four entries, or in other words, four rows.

Before you can enter data (rows) into a table, you must first define the table by naming what kind of data it will hold (columns). We are going to do a MySQL query to create this table.

PHP & MySQL Code:

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Create a MySQL table in the selected database
mysql_query("CREATE TABLE example(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30),
age INT)")
or die(mysql_error());

echo "Table Created!";
?>

Display:

Table Created!

Wow! That's a lot of code all at once! Let's get down in the dirt and figure this stuff out. We will be going through the code line by line.

The first part of the mysql_query told MySQL that we wanted to create a new table. We capitalized the two words because they are reserved MySQL keywords.

The word "example" is the name of our table, as it came directly after "CREATE TABLE". It is a good idea to use descriptive names when creating a table, such as: employee information, contacts, or customer orders. Clear names will ensure that you will know what the table is about when revisting it a year after you make it.

'id INT NOT NULL AUTO_INCREMENT'

Here we create a column "id" that will automatically increment each time a new entry is added to the table. This will result in the first row in the table having an id = 1, the second row id = 2, the third row id = 3, etc.

The column "id" is not something that we need to worry about after we create this table, as it is all automatically calculated within MySQL.

Reserved MySQL Keywords

Here are a few quick definitions of the reserved words used in this line of code:

       INT - This stands for integer. 'id' has been defined to be an integer.

       NOT NULL - These are actually two keywords, but they combine together to say that this column cannot be null.

       AUTO_INCREMENT - Each time a new entry is added the value will be incremented by 1.

PRIMARY KEY is used as a unique identifier for the rows. Here we have made "id" the PRIMARY KEY for this table. This means that no two ids can be the same, or else we will run into trouble. This is why we made "id" an auto incrementing counter in the previous line of code.

Here we make a new column with the name "name"! VARCHAR stands for variable character. "character" because it stores characters (letters, numbers, etc) and "variable" because you can store a varied amount of characters in the field (from 0 up to 30).

We will most likely only be using this column to store characters (A-Z, a-z). The numbers inside the parentheses sets the limit on how many characters can be entered. In this case, the limit is 30.

Our third and final column is age, which stores an integer. Notice that there are no paratheses following "INT", as MySQL already knows what to do with an integer. The possible integer values that can be stored within an "INT" are -2,147,483,648 to 2,147,483,647, which is more than enough to store someone's age!

This will print out an error if there is a problem in the creation process.

Using your MySQL administration tool that your web host has, check to see if the table was created correctly. Afterwards, try creating a few of your own tables to be sure that you have gotten the hang of it.

MySQL- Insert

   When data is put into a MySQL table it is referred to as inserting data. When inserting data it is important to remember what kind of data is specified in the columns of the table. If you try to place a 500 word essay into a column that only accepts integers of size three, then you will get a nasty error!

 

Now that you have created your table, let's put some data into that puppy! Here is the PHP/MySQL code for inserting data into the "example" table we created in the previous lesson.

PHP & MySQL Code:

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Insert a row of information into the table "example"
mysql_query("INSERT INTO example
(name, age) VALUES('Timmy Mellowman', '23' ) ")
or die(mysql_error());

mysql_query("INSERT INTO example
(name, age) VALUES('Sandy Smith', '21' ) ")
or die(mysql_error());

mysql_query("INSERT INTO example
(name, age) VALUES('Bobby Wallace', '15' ) ")
or die(mysql_error());

echo "Data Inserted!";
?>

Display:

Data Inserted!

This code is much simpler to understand than the create table code, as will be most of the MySQL you will learn in the rest of this tutorial. Once again, we will cover the code, line by line.

Again we are using the mysql_query function. "INSERT INTO" means that data is going to be put into a table. The name of the table we specified to insert data into was "example".

"(name, age)" are the two columns we want to add data in. "VALUES" means that what follows is the data to be put into the columns that we just specified. Here we enter the name Timmy Mellowman for "name", and 23 for "age".

Be sure to note the location and number of apostrophes and parentheses, as this is where a lot of beginner PHP/MySQL programmers run into problems.

If all goes as well, this .php page will add a three people to the "example" table every time it is run. Be sure to use your MySQL administration program provided by your web host to ensure that the data was inserted into your table.

Be careful not to run this script more than once, otherwise you will be inserting the same people, multiple times.

Usually most of the work done with MySQL involves pulling down data from a MySQL database. In MySQL, data is retrieved with the "SELECT" keyword. Think of SELECT as working the same way as it does on your computer. If you wanted to copy some information in a document, you would first select the desired information, then copy and paste.

Before attempting this lesson be sure that you have created a table that contains some data, preferrably the same one that we had in the MySQL Insert lesson. In this example we will be outputting the first entry of our MySQL "examples" table to the web browser.

PHP & MySQL Code:

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM example") or die(mysql_error());

// store the record of the "example" table into $row
$row = mysql_fetch_array( $result );
// Print out the contents of the entry
echo "Name: ".$row['name'];
echo " Age: ".$row['age'];
?>

Display:

Name: Tim Mellowman Age: 23

This is an example of how to use MySQL's SELECT statement in PHP and although the MySQL code is simple, printing out the information with PHP is somewhat more involved.

Below is a step-by-step walkthrough of the code.

When you perform a SELECT query on the database it will return a MySQL result. We want to use this result in our PHP code, so we need to store it in a variable. $result now holds the result from our mysql_query.

Yes, this is a partial repeat of the same line of code, but we wanted to explain this MySQL statement in greater detail!

In english, this line of code reads "Select everything from the table example". The asterisk is the wild card in MySQL which just tells MySQL to include every single column for that table.

mysql_fetch_array returns the first associative array of the mysql result that we pass to it. Here we are passing our MySQL result $result and the function will return the first row of that result, which includes the data "Tim Mellowman" & "23".

In our MySQL table "example" there are only two fields that we care about: name and age. These names are the keys to extracting the data from our associative array. To get the name we use $row['name'] and to get the age we use $row['age'].

PHP is case sensitive when you reference MySQL column names, so be sure to use capitalization in your PHP code that matches the MySQL column names!

In this lesson we learned how to get the first entry from a MySQL table and output to the browser, using PHP. In the next lesson we will see how to retrieve every entry of a table and put it into a nicely formatted table. However, we recommend that you first understand the PHP and MySQL code in this lesson before proceeding.

There isn't any Fetch Array function in MySQL. mysql_fetch_array is actually a PHP function that is used in PHP scripts when interacting with a MySQL database. If you have been jumping around our MySQL Tutorial then you would have seen this function popping up all over the place.

When you query a MySQL database in PHP using the mysql_query function do you know what it returns? It isn't something you can directly manipulate, that is for sure. Here is a sample SELECT query of a table we created in the MySQL Create Table lesson.

PHP and MySQL Code:

<?php
$result = mysql_query("SELECT * FROM example");
?>

The value that mysql_query returns and stores into $result is a special type of resource that requires the use of additional PHP functions to extract the data retrieved by our "SELECT * FROM example" query.

The mysql_fetch_array function takes a MySQL query resource as an argument ($result) and returns the first row of data returned by the mysql_query. Our table example basically looks like the table below.

example MySQL Table:

name	age
Timmy Mellowman	23
Sandy Smith	21
Bobby Wallace	15

The first row of data in this table is "Timmy Mellowman" and "23". When we fetch an array from our MySQL Resource $result it should Timmy's name and age in it.

mysql_fetch_array returns the first row in a MySQL Resource in the form of an associative array. The columns of the MySQL Result can be accessed by using the column names of the table. In our table example these are: name and age. Here is the code to print out the first MySQL Result row.

PHP and MySQL Code:

<?php
// Make a MySQL Connection

$query = "SELECT * FROM example";

$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());

echo $row['name']. " - ". $row['age'];
?>

Display:

Timmy Mellowman - 23

This is just what we expected would happen! Now, the cool thing about mysql_fetch_array is that you can use it again on the same MySQL Resource to return the second, third, fourth and so on rows. You can keep doing this until the MySQL Resource has reached the end (which would be three times in our example).

Sounds like an awfully repetitive task. It would be nice if we could get all our results from a MySQL Resource in an easy to do script.

As we have said, the mysql_fetch_array function returns an associative array, but it also returns FALSE if there are no more rows to return! Using a PHP While Loop we can use this information to our advantage.

If we place the statement "$row = mysql_fetch_array()" as our while loop's conditional statement we will accomplish two things:

1.       We will get a new row of MySQL information that we can print out each time the while loop checks its conditional statement.

2.       When there are no more rows the function will return FALSE causing the while loop to stop!

Now that we know what we need to do and how to go about doing it, this code will print out all the rows of our MySQL Resource.

PHP and MySQL Code:

<?php
// Make a MySQL Connection

$query = "SELECT * FROM example";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
    echo $row['name']. " - ". $row['age'];
     echo "<br />";
}
?>

Display:

Timmy Mellowman - 23
Sandy Smith - 21
Bobby Wallace - 15

And there we have all the rows from our example table! You could apply this script to any MySQL table as long as you change both the table name in the query and the column names that we have in the associative array.

You have seen two types of MySQL queries thus far: the query which we used to create a table and the query we used to insert data into our newly created table. The query in this lesson is SELECT, which is used to get information from the database, so that its data can be used in our PHP script.

Finally, we get to use the data in our MySQL database to create a dynamic PHP page. In this example we will select everything in our table "example" and put it into a nicely formatted HTML table. Remember, if you don't understand the HTML or PHP code, be sure to check out the HTML and/or PHP Tutorial(s).

PHP & MySQL Code:

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM example")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Age</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['name'];
echo "</td><td>";
echo $row['age'];
echo "</td></tr>";
}
echo "</table>";

?>

Display:

Name	Age
Timmy Mellowman	23
Sandy Smith	21
Bobby Wallace	15

We only had two entries in our table, so there are only two rows that appeared above. If you added more entries to your table then you may see more data than what is above. Useful PHP Tutorials for those who don't understand the above PHP: PHP Array Tutorial & PHP Loop Tutorial

When you select items from a database using mysql_query, the data is returned as a MySQL result. Since we want to use this data in our table we need to store it in a variable. $result now holds the result from our mysql_query.

In english, this line of code reads "Select everything from the table example". The asterisk is the wild card in MySQL which just tells MySQL to get everything.

The mysql_fetch_array function gets the next in line associative array from a MySQL result. By putting it in a while loop it will continue to fetch the next array until there is no next array to fetch. At this point the loop check will fail and the code will continue to execute.

In our MySQL table "example" there are only two fields that we care about: name and age. These names are the keys to extracting the data from our associative array. To get the name we use $row['name'] and to get the age we use $row['age'].

Use the query that we have provided or make a new one and try putting it into a formatted HTML table. Also, try out other methods of HTML formatting and see which one you like best.

By now you be starting to understand how powerful PHP and MySQL are when they are combined. The tasks that you can complete with MySQL and PHP would be nearly impossible to do by hand in HTML. Imagine trying to create an HTML table of 6000 entries without using a MySQL database and a PHP while loop!

In a previous lesson we did a SELECT query to get all the data from our "example" table. If we wanted to select only certain entries of our table, then we would use the WHERE keyword.

WHERE lets you specify requirements that entries must fulfill to be returned in the MySQL result. We will be assuming the data from a previous lesson for the following examples.

There are three entries in our "example" table: Jim, Sandy, and Bobby. To select Sandy only we could either specify Sandy's age (21) or we could use her name (Sandy Smith). In the future there may be other people who are 21, so we will use her name as our requirement.

WHERE is used in conjuction with a mathematical statement. In our example we will want to select all rows that have the string "Sandy Smith" in the "names" column. Here's how to do it.

PHP & MySQL Code:

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Get a specific result from the "example" table
$result = mysql_query("SELECT * FROM example
WHERE name='Sandy Smith'") or die(mysql_error());

// get the first (and hopefully only) entry from the result
$row = mysql_fetch_array( $result ); // Print out the contents of each row into a table
echo $row['name']." - ".$row['age'];
?>

Display:

Sandy Smith-21

If you wanted to select every person in the table who was in their 20's, how could you go about doing it? With the tools you have now, you could make 10 different queries, one for each age 20, 21, 22...but that seems like more work than we need to do.

In MySQL there is a "wildcard" character '%' that can be used to search for partial matches in your database. The '%' tells MySQL to ignore the text that would normally appear in place of the wildcard. For example '2%' would match the following: 20, 25, 2000000, 2avkldj3jklsaf, and 2!

On the other hand, '2%' would not match the following: 122, a20, and 32.

To solve our problem from before, selecting everyone who is their 20's from or MySQL table, we can utilize wildcards to pick out all strings starting with a 2.

PHP & MySQL Code:

<?php
// Connect to MySQL
// Insert a row of information into the table "example"
$result = mysql_query("SELECT * FROM example WHERE age like '2%' ")
or die(mysql_error());

// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row
echo $row['name']." - ".$row['age']. "<br />";
}
?>

Display:

Timmy Mellowman - 23
Sandy Smith - 21

You can use this wildcard at the beginning, middle, and end of the string. Experiment with it so you can see for yourself how powerful this little trick can be.

Note: The wildcard was used for example purposes only. If you really wanted to explicilty select people who are in their 20's you would use greater than 19 and less than 30 to define the 20's range. Using a wildcard in this example would select unwanted cases, like a 2 year old and your 200 year old great-great-great-grandparents.

It would be nice to be able to make MySQL results easier to read and understand. A common way to do this in the real world is to order a big list of items by name or amount. The way to order your result in MySQL is to use the ORDER BY statement.

What ORDER BY does is it takes the a column name that you specify and sorts it in alphabetic order (or numeric order if you are using numbers). Then when you print out the result in PHP the values are already sorted and are easy to read.

Ordering is also used quite frequently to add additional functionality to webpages that use any type of column layout. For example, some forums let you sort by date, thread title, post count, view count, and more

Let's use the same query we had in MySQL Select and modify it to ORDER BY the person's age. The code from MySQL Select looked like...

PHP & MySQL Code:

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM example")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Age</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['name'];
echo "</td><td>";
echo $row['age'];
echo "</td></tr>";
}
echo "</table>";

?>

Display:

Name	Age
Timmy Mellowman	23
Sandy Smith	21
Bobby Wallace	15

What we need to do is add on to the existing MySQL statement "SELECT * FROM example" to include our new ordering requirement. When you choose to order a column, be sure that your ORDER BY appears after the SELECT ... FROM part of the MySQL statement.

PHP & MySQL Code:

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM example ORDER BY age")
or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Age</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['name'];
echo "</td><td>";
echo $row['age'];
echo "</td></tr>";
}
echo "</table>";

?>

Display:

Name	Age
Bobby Wallace	15
Sandy Smith	21
Timmy Mellowman	23

Presto! We have an ordered MySQL result! Notice that we didn't have to change any of our PHP code. Remember this whenever you're editing a PHP script that uses MySQL. Sometimes it may be easier to just fiddle with the MySQL instead of trying to tackle the problem in PHP.

Thus far we have only been getting data from one table at a time. This is fine for simple takes, but in most real world MySQL usage you will often need to get data from multiple tables in a single query.

We like to show examples and code before we explain anything in detail, so here is how you would combine two tables into one using MySQL. The two tables we will be using relate to a families eating habits.

family Table:

Position	Age
Dad	41
Mom	45
Daughter	17
Dog

food Table:

Meal	Position
Steak	Dad
Salad	Mom
Spinach Soup
Tacos	Dad

The important thing to note here is that the column Position contains information that can tie these two tables together. In the family table the Position column contains all the members of the family and their ages. In the food table the Position column contains the family member who enjoys that dish.

It's only through shared column relationship such as this that allows tables to be joined together, so remember this when creating tables you wish to interact with eachother.

Let's imagine that we wanted to SELECT all the dishes that were liked by a family member. If you remember from the previous lesson, this is a situation when we need to use the WHERE clause. We want to SELECT all the dishes WHERE some family member likes it.

We will be performing a generic join of these two tables using the Position column from each table as the connector.

Note: This example assumes you have created the MySQL tables food and family. If you do not have them created either create them using our MySQL Create Table lesson or some other method to create these tables.

PHP and MySQL Code:

<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT family.Position, food.Meal ".
    "FROM family, food ".
     "WHERE family.Position = food.Position";

$result = mysql_query($query) or die(mysql_error());
// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
    echo $row['Position']. " - ". $row['Meal'];
     echo "<br />";
}
?>

The statement "WHERE family.Position = food.Position" will restrict the results to those rows where the Position exists in both the family and food tables.

Display:

Dad - Steak
Mom - Salad
Dad - Tacos

Those are the results of our PHP script. Let's analyze the tables to make sure we agree with these results.

Compare the Tables:

Position Age Dad 41 Mom 45 Daughter 17 Dog

Meal Position Steak Dad Salad Mom Spinach Soup   Tacos Dad

Our results show that there were three meals that were liked by family members. And by manually perusing the tables it looks like there were indeed three meals liked by family members.

Note: This is a very simple example of a join. If you do not understand it yet do not despair. Joins are a very hard concept to grasp for beginning MySQL developers.

In the previous lesson Mysql Joins we learned how to do a basic join of two tables. This lesson will teach you how to do a specialized join: left join

How is a LEFT JOIN different than a normal join? First of all, the syntax is quite different and somewhat more complex. Besides looking different, the LEFT JOIN gives extra consideration to the table that is on the left.

Being "on the left" simply refers to the table that appears before the syntax LEFT JOIN in our SQL statement. Nothing tricky about that.

This extra consideration to the left table translates into each and every entry from the left table showing up in the SQL result, even if there isn't a match with the other table that it is being joined to.

Here are the tables we used in the previous Mysql Joins lesson.

MySQL family and food Tables:

Position Age Dad 41 Mom 45 Daughter 17 Dog

Meal Position Steak Dad Salad Mom Spinach Soup   Tacos Dad

We executed a simple query that selected all meals that were liked by a family member with this simple join query:

Simplified MySQL Query:

SELECT food.Meal, family.Position
FROM family, food
WHERE food.Position = family.Position

Result:

Dad - Steak
Mom - Salad
Dad - Tacos

Now the difference when we instead use a LEFT JOIN in the query should make all the family members be listed, even if they do not have a favorite dish in our food table.

This is because a left join will preserve the records of the "left" table.

The code below is the exact same as the code in the previous lesson, except the LEFT JOIN has been added to the query. Let's see if the results are what we expected.

PHP and MySQL Code:

<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT family.Position, food.Meal ".
    "FROM family LEFT JOIN food ".
     "ON family.Position = food.Position";

$result = mysql_query($query) or die(mysql_error());
// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
    echo $row['Position']. " - ". $row['Meal'];
     echo "<br />";
}
?>

Display:

Dad - Steak
Dad - Tacos
Mom - Salad
Daughter -
Dog -

Success! The left join preserved each family members, including those that don't yet have a favorite meal in the food table! Please feel free to play around with LEFT JOIN until you feel like you have a solid grasp of it. This stuff isn't easy!

Imagine that you have a MySQL table that holds the information of all the employees in your company. One of the columns in this table is called "seniority" and it holds an integer value of how many months an employee has worked at your company. Your job is to update these numbers every month, too bad for you You may be thinking that you'll have to open up your MySQL administration tool and edit each entry by hand, which may take hours. On the other hand, you could master MySQL and have an automated script that you run each month to get the job done.

In this lesson you will learn how to replace the existing data of a MySQL table with freshly supplied up-to-date data using the UPDATE MySQL query.

Once again we will be working with the data from a previous example. Sandy has just had a birthday and is now 22 years old. Our job now is to update her age using and UPDATE, SET, and WHERE.

       UPDATE - Performs an update MySQL query

       SET - The new values to be placed into the table follow SET

       WHERE - Limits which rows are affected

PHP & MySQL Code:

<?php
// Connect to MySQL
// Get Sandy's record from the "example" table
$result = mysql_query("UPDATE example SET age='22' WHERE age='21'")
or die(mysql_error());

$result = mysql_query("SELECT * FROM example WHERE age='22'")
or die(mysql_error());

// get the first (and hopefully only) entry from the result
$row = mysql_fetch_array( $result ); echo $row['name']." - ".$row['age']. "<br />";
?>

Display:

Sandy Smith - 22

Now it is important to note that this query would have updated ALL records that had an age of 21 to the new age 22. Since Sandy was the only 21 year old, this was not a problem.

Maintenance is a very common task that is necessary for keeping MySQL tables current. From time to time you may even need to delete items from your database. Normal reasons for deleting a record from MySQL: on a forum when someone deletes a post, an employee leaves the company, or maybe you're trying to destroy your records before the federalies come.

 

The delete query is very similar to the UPDATE Query in the previous lesson. We need to choose a table, tell MySQL to perform the deletion, and provide the requirements that a record must have for it to be deleted.

We want to delete the youngest employee from our previously created table because he has to go back to school. This is how we do it.

PHP & MySQL Code:

<?php
// Connect to MySQL
// Delete Bobby from the "example" MySQL table
mysql_query("DELETE FROM example WHERE age='15'")
or die(mysql_error());

?>

It is important to note that this query would have deleted ALL records that had an age of 15. Since Bobby was the only 15 year old this was not a problem.

Before performing a large delete on a database, be sure to back up the table/database in case your script takes off a little more than desired. Test your delete queries before even thinking about using them on your table. As long as you take caution when using this powerful query you should not run into any problems.

If you're storing anything in MySQL databases that you do not want to lose, chances are you should be doing weekly or even daily backups. Depending on what you are using your databases for, be it to store forum messages, employee information, or your spending information, you are going to need to choose a backup schedule that meets your needs.

There are many paths you can take to create a MySQL backup. However, no matter which application, control panel tool, or SSH script you use, all of the backups will fit into two types of backup: a dump or raw backup.

A MySQL dump is a bit slower than a raw backup because it creates all the SQL queries required to create the tables of that database, as well as all the insert queries required to place the information back into the database's tables.

If you want to perform the mysql dump manually, without the assistance of your hosts control panel, then run SSH to your web server and do the following (taken from MySql.com):

       mysqldump --tab=/path/to/some/dir --opt db_name

If you were to open up a MySQL dump file you would see a slew of SQL queries that you would probably be able to understand (if you read through this whole tutorial!).

A MySQL Raw Backup is quicker because it does not translate the contents of the database into human readable SQL queries. However, not many control panels support this type of backup, so do not worry if your hosting provider doesn't have this option set up for you.

CPanel is the most widely used web host control panel at this time, so we thought it would make sense to provide a walkthrough specifically for CPanel.

From the application selection screen click "Backup". This will bring you to the backup application that allows you to generate and download complete site backups for your site. However, I have spoken with many a fellows who complained that the full site backup did not backup their databases.

To back up a database individually, look for the title "Download a SQL Database Backup" or something similar. Below that title should be a listing of every database that you have created. Simply click on the name of the database you want to backup and save it to your computer.

That's it! Now just be sure that you have a regular backup schedule, just in case the unthinkable happens and your web host loses all your database information!

After you have mastered the basics of MySQL, it's time to take the next step and take on Aggregate Functions. Before we talk about what they are, let's review the definition of aggregate, as it relates to MySQL:

        Aggregate - Constituting or amounting to a whole; total. ~American Heritage Dictionary

With this type of wording, we can assume that MySQL's aggregate functions are something that will be very top-level, or in other words, the opposite of detailed.

The most common types of aggregate functions let you find out things like the minimum, maximum and even the average of a "grouped" set of data. The trick to understanding aggregate functions is often understanding what kind of data is being grouped and analyzed.

Before we can start throwing around these fancy functions, let's build an appropriate table that has enough data in it to be meaningful to us. Below is the SQL for our "products" table. You can either run this SQL statement in your MySQL administrator software or use MySQL to execute the queries (i.e. create table, then each of the records).

You can download the products.sql file from our website. If you are new to MySQL you will need to know how to Create a MySQL Table and Insert a MySQL Row.

Below is the MySQL table products.

Products Table:

id	name	type	price
123451	Park's Great Hits	Music	19.99
123452	Silly Puddy	Toy	3.99
123453	Playstation	Toy	89.95
123454	Men's T-Shirt	Clothing	32.50
123455	Blouse	Clothing	34.97
123456	Electronica 2002	Music	3.99
123457	Country Tunes	Music	21.55
123458	Watermelon	Food	8.73

Imagine that our store was running an advertisement in the newspaper and we wanted to have a "bargain basement" section that listed the lowest price for each product type. In this case we would be "grouping" by the product type and finding the minimum price of each group.

Our query needs to return two columns: product type and the minimum price. Additionally, we want to use the type column as our group. The SELECT statement we are about to use will look different because it includes an aggregate function, MIN, and the GROUP BY statement, but otherwise it isn't any different than a normal SELECT statement.

PHP and MySQL Code:

<?php
// Make a MySQL Connection

$query = "SELECT type, MIN(price) FROM products GROUP BY type";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
    echo $row['type']. " - $". $row['MIN(price)'];
     echo "<br />";
}
?>

Our products table has four types of products: Music, Toy, Clothing and Food. When we GROUP BY type then we get one result for each of these types.

Display:

Clothing - $32.50
Food - $8.73
Music - $3.99
Toy - $3.99

Group by is good for retrieving information about a group of data. If you only had one product of each type, then GROUP BY would not be all that useful.

GROUP BY only shines when you have many similar things, like a number of products of the same type, and you want to find out some statistical information like the minimum, maximum and other top-level information.

Some technical rules of GROUP BY:

       The column that you GROUP BY must also be in your SELECT statement.

       Remember to group by the column you want information about and not the one you are applying the aggregate function on. In our above example we wanted information on the type column and the aggregate function was applied to the price column.

The next few lessons will provide a walkthrough for using other popular MySQL aggregate functions in conjunction with the GROUP BY statement

This lesson will teach you how to use the aggregate function COUNT(). If you missed the aggregate introduction lesson, please check it out now, as it explains many concepts used in this lesson!

We will be using the products table that we constructed to display the use of MySQL's COUNT function.

You can download the products.sql file from our website. If you are new to MySQL you will need to know how to Create a MySQL Table and Insert a MySQL Row.

Below is the MySQL table products.

Products Table:

id	name	type	price
123451	Park's Great Hits	Music	19.99
123452	Silly Puddy	Toy	3.99
123453	Playstation	Toy	89.95
123454	Men's T-Shirt	Clothing	32.50
123455	Blouse	Clothing	34.97
123456	Electronica 2002	Music	3.99
123457	Country Tunes	Music	21.55
123458	Watermelon	Food	8.73

The COUNT function is an aggregate function that simply counts all the items that are in a group. The products table that is displayed above has several products of various types. One use of COUNT might be to find out how many items of each type there are.

Just as we did in the aggregate introduction lesson, we are going to GROUP BY type to create four groups: Music, Toy, Clothing and Food. For a slight change of pace, let's count the name column to find how many products there are per type.

PHP and MySQL Code:

<?php
// Make a MySQL Connection

$query = "SELECT type, COUNT(name) FROM products GROUP BY type";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
    echo "There are ". $row['COUNT(name)'] ." ". $row['type'] ." items.";
     echo "<br />";
}
?>

Display:

There are 2 Clothing items.
There are 1 Food items.
There are 3 Music items.
There are 2 Toy items.

This lesson will teach you how to use the aggregate function SUM(). If you haven't already read through Tizag's aggregate introduction lesson, please check it out now, as it explains concepts used in the lesson

We will be using the products table again, this time to display the use of MySQL's SUM function.

You can download the products.sql file from our website. If you are new to MySQL you will need to know how to Create a MySQL Table and Insert a MySQL Row.

Here's a visual of the products table.

Products Table:

id	name	type	price
123451	Park's Great Hits	Music	19.99
123452	Silly Puddy	Toy	3.99
123453	Playstation	Toy	89.95
123454	Men's T-Shirt	Clothing	32.50
123455	Blouse	Clothing	34.97
123456	Electronica 2002	Music	3.99
123457	Country Tunes	Music	21.55
123458	Watermelon	Food	8.73

SUM is an aggregate function that totals a specific column for a group. The products table that is displayed above has several products of various types. One use of SUM might be to find the total of all the items' price for each product type.

Just as we did in the aggregate introduction lesson, we are going to apply the aggregate function to price and GROUP BY type to create four groups: Music, Toy, Clothing and Food.

PHP and MySQL Code:

<?php
// Make a MySQL Connection

$query = "SELECT type, SUM(price) FROM products GROUP BY type";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
    echo "Total ". $row['type']. " = $". $row['SUM(price)'];
     echo "<br />";
}
?>

Display:

Total Clothing = $67.47
Total Food = $8.73
Total Music = $45.53
Total Toy = $93.94

This lesson will teach you how to use the aggregate function AVG(). If you missed the aggregate introduction lesson, please check it out now, it explains the meaning of aggregates and describes the GROUP BY statement.

The table we will be using is products and you can download the products.sql fso you can follow along. The table can be entered through you MySQL interface or through PHP.

If you are new to MySQL/PHP programming you will need to know how to Create a MySQL Table and Insert a MySQL Row.

Products Table:

id	name	type	price
123451	Park's Great Hits	Music	19.99
123452	Silly Puddy	Toy	3.99
123453	Playstation	Toy	89.95
123454	Men's T-Shirt	Clothing	32.50
123455	Blouse	Clothing	34.97
123456	Electronica 2002	Music	3.99
123457	Country Tunes	Music	21.55
123458	Watermelon	Food	8.73

The AVG function returns the average value for the specified column of a group.

Our imaginary customers have been complaining recently that our prices are too high, so we would like to find out the average price of each product type to see if this is in fact the truth.

To find out this metric we are going to apply the aggregate function to the price and GROUP BY type to create four price groups: Music, Toy, Clothing and Food.

PHP and MySQL Code:

<?php
// Make a MySQL Connection

$query = "SELECT type, AVG(price) FROM products GROUP BY type";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
    echo "The average price of ". $row['type']. "is $".$row['AVG(price)'];
     echo "<br />";
}
?>

Display:

The average price of Clothingis $33.735000
The average price of Foodis $8.730000
The average price of Musicis $15.176667
The average price of Toyis $46.970000

Those prices seem very reasonable, in my opinion. I think our imaginary customers should change their view and keep buying products from us

This lesson will teach you how to use the aggregate function MIN(). If you missed the aggregate introduction lesson, you might want to check it out to learn about the GROUP BY statement and its use with MySQL aggregate functions

You can download the table used in this example, products.sql, from our website. A SQL file can be run through your MySQL administrator interface to create the table.

However, if you would like to create the table with PHP/MySQL, check out our Create a MySQL Table and Insert a MySQL Row lessons.

Products Table:

id	name	type	price
123451	Park's Great Hits	Music	19.99
123452	Silly Puddy	Toy	3.99
123453	Playstation	Toy	89.95
123454	Men's T-Shirt	Clothing	32.50
123455	Blouse	Clothing	34.97
123456	Electronica 2002	Music	3.99
123457	Country Tunes	Music	21.55
123458	Watermelon	Food	8.73

The MIN function is an aggregate function that finds the smallest value ina group. The products table that is displayed above has several products of various types. One use of MIN might be to find out the cheapest item in each group.

Just as we did in the aggregate introduction lesson, we are going to GROUP BY type to create four groups: Music, Toy, Clothing and Food. The column that will have the MIN function applied to it is, of course, price.

PHP and MySQL Code:

<?php
// Make a MySQL Connection

$query = "SELECT type, MIN(price) FROM products GROUP BY type";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
    echo "The cheapest ". $row['type']. " is $" .$row['MIN(price)'];
     echo "<br />";
}
?>

Display:

The cheapest Clothing is $32.50
The cheapest Food is $8.73
The cheapest Music is $3.99
The cheapest Toy is $3.99

This lesson will teach you how to use the MAX() aggregate function . If you missed the aggregate introduction lesson, please check it out now, as it explains many concepts used in this lesson

You can download the example the products.sql file from our website, which contains the SQL for the table below.

If you would like to use PHP/MySQL to create the table, then you will need to know how to Create a MySQL Table and Insert a MySQL Row.

Below is the MySQL table products.

Products Table:

id	name	type	price
123451	Park's Great Hits	Music	19.99
123452	Silly Puddy	Toy	3.99
123453	Playstation	Toy	89.95
123454	Men's T-Shirt	Clothing	32.50
123455	Blouse	Clothing	34.97
123456	Electronica 2002	Music	3.99
123457	Country Tunes	Music	21.55
123458	Watermelon	Food	8.73

MySQL's MAX aggregate function will find the largest value in a group. The products table that is displayed above has several products of various types. We could use the MAX function to find the most expensive item for each type of product.

Just as we did in the aggregate introduction lesson, we are going to GROUP BY type to create four groups: Music, Toy, Clothing and Food. We will also be applying the aggregate function to the price column.

PHP and MySQL Code:

<?php
// Make a MySQL Connection

$query = "SELECT type, MAX(price) FROM products GROUP BY type";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
    echo "The most expensive ". $row['type']. " is $" .$row['MAX(price)'];
     echo "<br />";
}
?>

Display:

The most expensive Clothing is $34.97
The most expensive Food is $8.73
The most expensive Music is $21.55
The most expensive Toy is $89.95