Creating The Database Backup

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!

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" 

Reading Data From MySQL Database With PHP

To read records from a database, the technique is usually to loop round and find the ones you want. To specify which records you want, you use something called SQL. This stands for Structured Query Language. This is a natural, non-coding language that uses words like SELECT and WHERE. At it's simplest level, it's fairly straightforward. But the more complex the database, the more trickier the SQL is. We'll start with something simple though.
What we want to do, now that we have a connection to our database, is to read all the records, and print them out to the page. Here's some new code, added to the PHP script you already have. The new lines are in red:

    $user_name = "root";
    $password = "";
    $database = "addressbook";
    $server = "127.0.0.1";

    $db_handle = mysql_connect($server, $user_name, $password);
    $db_found = mysql_select_db($database, $db_handle);

   if ($db_found) {
   $SQL = "SELECT * FROM tb_address_book";
   $result = mysql_query($SQL);
   while ($db_field = mysql_fetch_assoc($result)) {
       print $db_field['ID'];
       print $db_field['First_Name'];
       print $db_field['Surname'];
       print $db_field['Address'];
   }
   mysql_close($db_handle);
  }
  else {
  print "Database NOT Found ";
  mysql_close($db_handle);
}

Here ID,First_Name,Surname,Address are the attributes of the table tb_address_book in the MySQL Database.

What Is Content Management System

A content management system (CMS) is a system providing a collection of procedures used to manage work flow in a collaborative environment. These procedures can be manual or computer-based. The procedures are designed to do the following:

• Allow for a large number of people to contribute to and share stored data
• Control access to data, based on user roles (defining which information users or user groups can view, edit, publish, etc.)
• Aid in easy storage and retrieval of data
• Control of data validity and compliance
• Reduce repetitive duplicate input
• Improve the ease of report writing
• Improve communication between users

In a CMS, data can be defined as nearly anything: documents, movies, text, pictures, phone numbers, scientific data, and so forth. CMSs are frequently used for storing, controlling,revising semantically enriching, and publishing documentation. Serving as a central repository, the CMS increases the version level of new updates to an already existing file. Version control is one of the primary advantages of a CMS.

Enterprise content management systems:
Main article: Enterprise content management
An enterprise content management system (ECM) is content, documents, details and records related to the organizational processes of an enterprise. The purpose and result is to manage the organization's unstructured information content, with all its diversity of format and location. The system manages the content related to commercial organizations. The main objectives of Enterprise content management are to streamline access, eliminate bottlenecks, optimize security and maintain integrity.

Component content management system:
Main article: Component content management system
In a component content management system (CCMS), the content is stored and managed at the sub-document (or component) level for greater content reuse.CMS has five main functions:

• Maintaining Security
• Managing Objects
• Managing Servers
• Managing Auditing
• Maintaining Reports.

Web Content Management System:
Main article: Web Content Management System
Web content management (WCM) is a bundled or stand-alone application used to create, manage, store and deploy content on Web pages. Web content types can include text, graphics and photos, video or audio, and application code that renders other content or interacts with the visitor. WCM may also catalog or index content, select or assemble content at runtime, or deliver content to specific visitors in a personalized way or in different languages.

The CRUD Operation

All database operations can be broadly classified into 4 categories:

1. Insert data (Create)
2. Get existing data (Read)
3. Modify existing data (Update)
4. Delete data (Delete)

This set of tasks are called CRUD.

The term CRUD stands for Create, Read, Update, Delete which corresponds to the basic database operations mentioned above.

If you are working on a database application, you may be asked to implement the CRUD tasks for the commonly used tables (Entities). So be sure that you know what is meant by CRUD !

For example, if you are developing a student management software, you may have a table called 'Students'. Most probably, you will have to implement the following screens:

1. Screen to add a student
2. Screen which displays all the existing students
3. Screen to edit and modify existing student
4. Screen to select and delete a student.

Did you notice that the above operations match with the CRUD tasks?

So far we have discussed about the inserting of data in the database .In the following posts we will discuss about other operations which will ultimately lead us to creating a CMS or Content Management System.

Creating MySQL Table And Inserting Data Using PHP

We have learned about connecting to the MySQL database and running queries using PHP. It is now time create a MySQL table and Inserting data into the table.Both of these two works is done by the PHP function mysql_query().
Creating Table:
We can execute a MySQL command by sending the command as a string into the PHP function mysql_query().The following code will show the process to create a table.

$query="CREATE TABLE tablename(  ";
$query.="Name VARCHAR(50) ";
$query.="Age INT  ";
$query.=")";
mysql_query($query) or die(mysql_error());

Inserting Into The Table:
To insert data into the table the corresponding command to insert data in MySQL is executed in the same way though mysql_query().The following code fragment demonstrate the process.

$query="INSERT INTO tablename ";
$query.="(Name,Age) ";
$query.="VALUES('name',10)";
mysql_query($query) or die( mysql_error() );

In the second line 'Name' and 'Age' are the attributes of the table created earlier.Third line determines what values you want to insert to the specific attribute.Finally the mysql_query() function executes the command.
The die function in both of the code fragment handles the fact if the query does not execute correctly.

Running MySQL Queries Using PHP

After connecting to the MySQL database you are ready to run some queries. The function used to perform queries is named - mysql_query(). The function returns a resource that contains the results of the query, called the result set. To examine the result we're going to use the mysql_fetch_array() function, which returns the results row by row. In the case of a query that doesn't return results, the resource that the function returns is simply a value true or false.
A convenient way to access all the rows is with a while loop. Let's add the code to our script:

//execute the SQL query and return records
$result = mysql_query("SELECT id, model, year FROM cars");
//fetch tha data from the database
while ($row = mysql_fetch_array($result)) {
echo "ID:".$row{'id'}." Name:".$row{'model'}."".$row{'year'}."";
}

Connecting To MySQL Database

Before you can get content out of your MySQL database, you must know how to establish a connection to MySQL from inside a PHP script. To perform basic queries from within MySQL is very easy. This article will show you how to get up and running.
Let's get started. The first thing to do is connect to the database.The function to connect to MySQL is called mysql_connect. This function returns a resource which is a pointer to the database connection. It's also called a database handle, and we'll use it in later functions. Don't forget to replace your connection details.Here is a code to connect to the MySQL database.

//set the variables
$username = "your_name";
$password = "your_password";
$hostname = "localhost";
//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
echo "Connected to MySQL";

All going well, you should see "Connected to MySQL" when you run this script. If you can't connect to the server, make sure your password, username and hostname are correct.Once you've connected, you're going to want to select a database to work with. Let's assume the database is called 'examples'. To start working in this database, you'll need the mysql_select_db() function:

//select a database to work with
$selected = mysql_select_db("examples",$dbhandle)
or die("Could not select examples");

A opened connection should be closed at the end of the script.Here is the small code to close a MySQL database connection.

//close the connection
mysql_close($dbhandle);

This three code fragments is sufficient to connect to a MySQL Database.Here is the complete to for your convenience.The total code should be written inside the php tag.

//stating of the script
$username = "your_name";
$password = "your_password";
$hostname = "localhost";

 //connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
echo "Connected to MySQL";
//select a database to work with
$selected = mysql_select_db("examples",$dbhandle)
or die("Could not select examples");
 //close the connection
mysql_close($dbhandle);
//end of the script