PHP ─ PHP and MySQL

PHP will work with virtually all database software, including Oracle and Sybase but most commonly used is freely available MySQL database.

What you should already have?

• You have gone through MySQL tutorial to understand MySQL Basics.
• Downloaded and installed a latest version of MySQL.
• Created database user guest with password guest123.
• If you have not created a database, then you would need root user and its password to create a database.
  
  We have divided this chapter in the following sections:
  
  
• Connecting to MySQL database - Learn how to use PHP to open and close a MySQL database connection.
• Create MySQL Database Using PHP - This part explains how to create MySQL database and tables using PHP.
• Delete MySQL Database Using PHP - This part explains how to delete MySQL database and tables using PHP.
• Insert Data To MySQL Database - Once you have created your database and tables, then you would like to insert your data into created tables. This session will take you through real example on data insert.
• Retrieving Data From MySQL Database - Learn how to fetch records from MySQL database using PHP.
• Using Paging through PHP - This one explains how to show your query result into multiple pages and how to create the navigation link.
• Updating Data Into MySQL Database - This part explains how to update existing records into MySQL database using PHP.
• Deleting Data From MySQL Database - This part explains how to delete or purge existing records from MySQL database using PHP.
• Using PHP To Backup MySQL Database - Learn different ways to take backup of your MySQL database for safety purpose.

Connecting to MySQL Database

Opening a Database Connection

PHP provides mysql_connect function to open a database connection. This function takes five parameters and returns a MySQL link identifier on success, or FALSE on failure.

Syntax

Sr.No	Parameter & Description
1	server Optional − The host name running database server. If not specified, then default value is localhost:3306.
2	user Optional − The username accessing the database. If not specified, then default is the name of the user that owns the server process.
3	passwd Optional − The password of the user accessing the database. If not specified then default is an empty password.
4	new_link Optional − If a second call is made to mysql_connect() with the same arguments, no new connection will be established; instead, the identifier of the already opened connection will be returned.
5	client_flags Optional − A combination of the following constants − MYSQL_CLIENT_SSL − Use SSL encryption MYSQL_CLIENT_COMPRESS − Use compression protocol MYSQL_CLIENT_IGNORE_SPACE − Allow space after function names MYSQL_CLIENT_INTERACTIVE − Allow interactive timeout seconds of inactivity before closing the connection

NOTE − You can specify server, user, passwd in php.ini file instead of using them again and again in your every PHP scripts. Check php.ini fileconfiguration.

Closing Database Connection

Its simplest function mysql_close PHP provides to close a database connection. This function takes connection resource returned by mysql_connect function. It returns TRUE on success or FALSE on failure.

Syntax


If a resource is not specified, then the last opened database is closed.

Example

Try the following example to open and close a database connection −


Create MySQL Database Using PHP

Creating a Database

To create and delete a database, you should have admin privilege. It’s very easy to create a new MySQL database. PHP uses mysql_query function to create a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

Sr.No	Parameter & Description
1	sql Required - SQL query to create a database
2	connection Optional - if not specified, then the last opened connection by mysql_connect will be used.

Example

Try the following example to create a database −


Selecting a Database

Once you establish a connection with a database server, then it is required to select a particular database with which all your tables are associated.

This is required because there may be multiple databases residing on a single server and you can do work with a single database at a time.

PHP provides function mysql_select_db to select a database. It returns TRUE on success or FALSE on failure.

Syntax

Sr.No	Parameter & Description
1	db_name Required - Database name to be selected
2	connection Optional - if not specified, then the last opened connection by mysql_connect will be used.

Example

Here is the example showing you how to select a database.


Creating Database Tables

To create tables in the new database, you need to do the same thing as creating the database. First create the SQL query to create the tables, then execute the query using mysql_query() function.

Example

Try the following example to create a table −


In case you need to create many tables, then it’s better to create a text file first and put all the SQL commands in that text file and then load that file into $sql variable and execute those commands.

Consider the following content in sql_query.txt file


Delete MySQL Database Using PHP

Deleting a Database

If a database is no longer required, then it can be deleted forever. You can use pass an SQL command to mysql_query to delete a database.

Example

Try the following example to drop a database.


WARNING – It’s very dangerous to delete a database and any table. So before deleting any table or database you should make sure you are doing everything intentionally.

Deleting a Table

It’s again a matter of issuing one SQL command through mysql_query function to delete any database table. But be very careful while using this command because by doing so you can delete some important information you have in your table.

Example

Try the following example to drop a table −


Insert Data to MySQL Database

Data can be entered into MySQL tables by executing SQL INSERT statement through PHP function mysql_query. Below a simple example to insert a record into employee table.

Example

Try the following example to insert record into employee table.


In real application, all the values will be taken using HTML form and then those values will be captured using PHP script and finally they will be inserted into MySQL tables.

While doing data insert its best practice to use function get_magic_quotes_gpc() to check if current configuration for magic quote is set or not. If this function returns false, then use function addslashes() to add slashes before quotes.

Example

Try this example by putting this code into add_employee.php, this will take input using HTML Form and then it will create records into database.


Retrieving Data from MySQL Database

Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function mysql_query. You have several options to fetch data from MySQL.

The most frequently used option is to use function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

Following is a simple example to fetch records from employee table.

Example

Try the following example to display all the records from employee table.


The content of the rows are assigned to the variable $row and the values in row are then printed.

NOTE − Always remember to put curly brackets when you want to insert an array value directly into a string.

In the above example, the constant MYSQL_ASSOC is used as the second argument to mysql_fetch_array(), so that it returns the row as an associative array. With an associative array, you can access the field by using their name instead of using the index.

PHP provides another function called mysql_fetch_assoc() which also returns the row as an associative array.

Example

Try the following example to display all the records from employee table using mysql_fetch_assoc() function.


You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.

Example

Try the following example to display all the records from employee table using MYSQL_NUM argument.


All the above three examples will produce the same result.

Releasing Memory

It is a good practice to release cursor memory at the end of each SELECT statement. This can be done by using PHP function mysql_free_result(). Below is the example to show how it has to be used.

Example

Try the following example.


While fetching data, you can write as complex SQL as you like. The procedure will remain the same as mentioned above.

Using Paging through PHP

It’s always possible that your SQL SELECT statement query may result into thousands of records. But it is not good idea to display all the results on one page. So we can divide this result into many pages as per requirement.

Paging means showing your query result in multiple pages instead of just put them all in one long page.

MySQL helps to generate paging by using LIMIT clause which will take two arguments. First argument as OFFSET and second argument how many records should be returned from the database.

Following is a simple example to fetch records using LIMIT clause to generate paging.

Example

Try the following example to display 10 records per page.


Updating Data into MySQL Database

Data can be updated into MySQL tables by executing SQL UPDATE statement through PHP function mysql_query.

Below is a simple example to update records into employee table. To update a record in any table it is required to locate that record by using a conditional clause. Below example uses primary key to match a record in employee table.

Example

Try the following example to understand update operation. You need to provide an employee ID to update an employee salary.


Deleting Data from MySQL Database

Data can be deleted from MySQL tables by executing SQL DELETE statement through PHP function mysql_query.

Following is a simple example to delete records into employee table. To delete a record in any table it is required to locate that record by using a conditional clause. Below example uses primary key to match a record in employee table.

Example

Try the following example to understand delete operation. You need to provide an employee ID to delete an employee record from employee table.


Using PHP to Backup MySQL Database

It is always a good practice to take a regular backup of your database. There are three ways you can use to take backup of your MySQL database.

• Using SQL Command through PHP.
• Using MySQL binary mysqldump through PHP.
• Using phpMyAdmin user interface.

Using SQL Command through PHP

You can execute SQL SELECT command to take a backup of any table. To take a complete database dump you will need to write separate query for separate table. Each table will be stored into separate text file.

Example

Try the following example of using SELECT INTO OUTFILE query for creating table backup −


There may be instances when you would need to restore data which you have backed up some time ago. To restore the backup, you just need to run LOAD DATA INFILE query like this −


Using MySQL binary mysqldump through PHP

MySQL provides one utility mysqldump to perform database backup. Using this binary you can take complete database dump in a single command.

Example

Try the following example to take your complete database dump −


Using phpMyAdmin user interface

If you have phpMyAdmin user interface available, then it is very easy for you to take backup of your database.

To back up your MySQL database using phpMyAdmin click on the "export" link on phpMyAdmin main page. Choose the database you wish to backup, check the appropriate SQL options and enter the name for the backup file.

---