Sunday, July 19, 2009

How To Use PHP to Interact with MySQL

1. Introduction. This article will discuss how to connect PHP and MySQL, to create a dynamic database-driven website. I will assume that you already have a PHP and MySQL copy installed on your system and they all work. I will also assume that you have basic knowledge about PHP including PHP syntax and how to run a PHP script.

2. PHP and MySQL. PHP already includes a MySQL module in its distribution, so you won't need to install an additional software module.

3. Create MySQL user.

To create a username and password for your script execute the MySQL console application. You must login as root, then execute this SQL statement:

CREATE DATABASE db_test;
USE db_test;
CREATE TABLE tbl_phonebook
(
phone_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
phone_name VARCHAR(40),
phone_number VARCHAR(40),
INDEX phone_name_idx (phone_name),
INDEX phone_number_idx (phone_number)
);
GRANT SELECT,UPDATE,DELETE,INSERT TO db_test.*
TO 'test_user' IDENTIFIED BY 'test_user';

After you execute those SQL commands, MySQL will create a database called db_test which contains one table called tbl_phonebook. We will use this table for our sample web application.

4. Connecting to MySQL database.

To establish connection to a MySQL database, you need to call mysql_connect() or mysql_pconnect(). Basically they're all similar except the second one will create a persistent connection. A persistent connection is a connection that persists even if connection is closed.

$link=mysql_connect('localhost','test_user','test_user');
$persist_link=mysql_pconnect('localhost','test_user','test_user');
?>

mysql_pconnect() is faster than mysql_connect() because it eliminates overhead to establish a connection to the MySQL database. If mysql_pconnect() finds that a persistent connection is already available for a user, it will use it, otherwise it creates new connection.

Upon connection completion, mysql_connect() and mysql_pconnect() returns a handle to connection.


5. Selecting a database to use. To select database to use, use must call mysql_select_db(). This function requires a database name and optional handle of database connection. If you omit the handle of connection, it will use the currently open connection.

$link=mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test',$link);
?>

Selecting a database is an optional step, which you can skip. However, if you do skip it, you must include the database name and table name to access a table. For example, to access tbl_phonebook you must provide the complete name db_test.tbl_phonebook instead of tbl_phonebook. Please note, you must use a period to separate the database name and table name.

6. Execute SQL statement. After you have a valid connection and have selected an active database, you are ready to execute the SQL statement to do some data manipulation. To execute the SQL statement, use mysql_query() function.

This function requires at least one parameter, which is the SQL command you want to execute. The second paramater is the connection you want to use. This parameter is optional. If you omit it, the currently open connection is used. If there is no open connection, this function will try to establish a new connection.

mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test');
$sql="SELECT * FROM tbl_phonebook";
mysql_query($sql);
?>

7. Inserting new data into the database. To insert a new record into the database, you must execute the INSERT statement. For example:

mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test');
$sql="INSERT INTO tbl_phonebook
(
phone_name,
phone_number
)
VALUES
(
'Johny Greenwood' ,
'+6281455632112'
) ";
mysql_query($sql);
?>

For the INSERT operation, to know whether the execution succeeded or failed, we use value return by mysql_query(). It returns boolean value, i.e, TRUE if it succeeded or FALSE if it failed.

To know how what records were affected by the execution of this SQL command, we call mysql_affected_rows(). This function needs one optional connection parameter. If it is omitted then the curently open connection will be used. This function returns the number of rows affected.

mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test');
$sql="INSERT INTO tbl_phonebook
(
phone_name,
phone_number
)
VALUES
(
'Johny Greenwood' ,
'+6281455632112'
) ";
mysql_query($sql);
print('Number of rows affected : '.mysql_affected_rows());
?>

8. Retrieving data from a database. To retrieve data from a database, we use SELECT command. This example was displayed previously. Here, we show it again.

mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test');
$sql="SELECT * FROM tbl_phonebook";
mysql_query($sql);
?>

The above piece of code did nothing useful. To make data available for viewing, we must fetch rows. If we use mysql_query() for the SELECT operation, it will return resources to rows by the SELECT statement. To fetch data from this resource we use mysql_fetch_row(), mysql_fetch_assoc(), mysql_fetch_array() or mysql_fetch_object. Everytime mysql_fetch_***() is called, the database cursor is increment to the next data. If these return NULL, the cursor is at the end of file (EOF), so there is no more data to return.

mysql_fetch_rows() will fetch data and return data as a numeric array. For example:

Use of mysql_fetch_rows():

mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test');
$sql="SELECT * FROM tbl_phonebook";
$res=mysql_query($sql);
if ($res)
{
while ($data=mysql_fetch_row($res))
{
print("

Name : ".$data[1]);
print("Phone : ".$data[2]."

");
}
mysql_free_result($res);
}
?>

Data from the phone_name field is returned in index number 1, because we selected all fields. So index 0 will contain data of phone_id field whereas phone_number field will be returned in index 2.

mysql_free_result() frees resources returned by mysql_query(). PHP automatically frees all resources when the script is finished, so calling mysql_free_result() is optional, but surely a good programming habit.

mysql_fetch_row() will fetch data and return data as an associative array. For example:

Use of mysql_fetch_assoc():

mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test');
$sql="SELECT * FROM tbl_phonebook";
$res=mysql_query($sql);
if ($res)
{
while ($data=mysql_fetch_assoc($res))
{
print("

Name : ".$data['phone_name']);
print("Phone : ".$data['phone_number']."

");
}
mysql_free_result($res);
}
?>

Note that the name of the field is used to index the array.

mysql_fetch_array() is a combination of mysql_fetch_row() and mysql_fecth_assoc(). It returns a numeric array as well as an associative array.

mysql_fetch_object() fetches rows and returns data as an object. For example:

mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test');
$sql="SELECT * FROM tbl_phonebook";
$res=mysql_query($sql);
if ($res)
{
while ($data=mysql_fetch_object($res))
{
print("

Name : ".$data->phone_name);
print("Phone : ".$data->phone_number."

");
}
mysql_free_result($res);
}
?>

To know how many rows have been returned by mysql_query(), you call mysql_num_rows(). It requires one parameter, i.e, resource returned by mysql_query().

$tot_record=mysql_num_rows($res);

9. Updating data. To update data you execute the UPDATE command.

mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test');
$sql="UPDATE tbl_phonebook SET phone_number='0135928549' WHERE phone_id=1";
$res=mysql_query($sql);
if ($res)
{
print('Update succeed');
} else
print('Update failed');
?>

Similar to INSERT, the UPDATE command doesn't generate a result set but only the boolean value; TRUE if it succeeds of FALSE otherwise. To find out how many rows are affecteded by the UPDATE command, use mysql_affected_rows().


10. Removing data. To remove a record you can use the DELETE command:

mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test');
$sql="DELETE FROM tbl_phonebook WHERE phone_id=1";
$res=mysql_query($sql);
if ($res)
{
print('Phone ID =1 deleted');
} else
print('Unable to delete');
?>


11. Closing connection. To close what we have opened, we call mysql_close(). It requires an optional parameter, i.e. a connection to close. If we wanto to close the currently open connection, omit the optional parameter.

$link=mysql_connect('localhost','test_user','test_user');
mysql_select_db('db_test',$link);
$sql="SELECT * FROM tbl_phonebook";
$res=mysql_query($sql,$link);
if ($res)
{
while ($data=mysql_fetch_object($res))
{
print("

Name : ".$data->phone_name);
print("Phone : ".$data->phone_number."

");
}
mysql_free_result($res);
}
mysql_close($link);
?>


12. Conclusion. We have discussed basic steps to let PHP interact with MySQL, including how to establish a database connection, how to select an active database, and how to execute an SQL command.

No comments:

Post a Comment