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're 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.
You may or may not know that MySQL databases are just files that are stored on your web server. This fact makes the whole backup and restore process extremely simple and painless once you have figured out how to do it.
Different Ways to Get That Backup Done:
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 backups: a dump or raw backup.
MySQL Dump:
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've already read through this whole tutorial!).
MySQL Raw Backup:
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.
MySQL Backup in Control Panel cpanel:
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 backups for your site.
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!
Creating The Database Backup
Deleting Data In MySQL Database Using PHP
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. Some potential reasons for deleting a record from MySQL include when: someone deletes a post from a forum, an employee leaves a company, or you're trying to destroy your records before the federalies come!
MySQL DELETE Example:
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.
Say 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.
// Connect to MySQL
// Delete Bobby from the "example" MySQL table
mysql_query("DELETE FROM example WHERE age='15'")
or die(mysql_error());
MySQL DELETE Tips:
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.
MySQL Join Operation Using PHP
Thus far we have only been getting data from one table at a time. This is fine for simple tasks, but in most real world MySQL usage you will often need to get data from multiple tables in a single query.
The act of joining in MySQL refers to smashing two or more tables into a single table. This means everything you have learned so far can be applied after you've created this new, joined table.
MySQL Join Table Setup:
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.
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 respective ages. In the "food" table the Position column contains the family member who enjoys that dish.
It's only through a shared column relationship such as this that tables can be joined together, so remember this when creating tables you wish to have interact with each other.
MySQL Join Simple Example:
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 a 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 either of them created, you can either create them using our MySQL Create Table lesson or do it manually yourself.
// 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 "
";
}
The statement "WHERE family.Position = food.Position" will restrict the results to the rows where the Position exists in both the "family" and "food" tables.
Those are the results of our PHP script. Let's analyze the tables to make sure we agree with these results.
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.
Showing Database Data In Specific Order: Use Of Order By
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 take the a column name that you specify and sort it in alphabetical order (or numeric order if you are using numbers). Then when you use mysql_fetch_array to print out the result, the values are already sorted and 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.
Sorting a MySQL Query - ORDER BY:
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...
// 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());
// 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 $row['name'];
echo $row['age'];
}
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.
// 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());
// 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 $row['name'];
echo $row['age'];
}
This will work I guess.
Reading Data In MySQL Database Using PHP Under Where Condition
In a previous lesson we did a SELECT query to get all the data from a table. If we wanted to select only certain entries of our table, then we would use the keyword WHERE.
WHERE lets you specify requirements that entries must meet in order to be returned in the MySQL result. Those entries that do not pass the test will be left out. We will be assuming the data from a previous lesson for the following examples.
Being Selective With Your MySQL Selection:
// 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'];
MySQL Wildcard Usage '%':
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.
MySQL Query WHERE With Wildcard:
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.
// 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']. "
";
}
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.
Deleting Data In MySQL Database Using PHP
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.
Say 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.
// Connect to MySQL
// Delete Bobby from the "example" MySQL table
mysql_query("DELETE FROM example WHERE age='15'")
or die(mysql_error());
MySQL DELETE Tips
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.
Updating Data In MySQL Database Using PHP
So far we have seen the writing and reading methods in the database. Now we will see the update process.See the following example:
Often it is necessary to change the data you have in your database. Let's say that Peggy (from our example) came in for a visit on her 7th birthday and we want to overwrite her old data with her new data. If you are using phpMyAdmin you can do this by clicking your database on the left (in our case "people") and then choosing "Browse" on the right. Next to Peggy's name you will see a pencil icon, this means EDIT. Click on the pencil. You can now update her information as shown (see above.)
You can also do this through the query window or command line. You have to be very careful when updating records this way and double check your syntax, as it is very easy to inadvertently overwrite several records.
UPDATE people SET age = 7, date = "2006-06-02 16:21:00", height = 1.22 WHERE name = "Peggy"