PHP and MySQL Database

You are free to work with database like MySQL, using your PHP script.

PHP Connect to MySQL Database

You can connect to MySQL Database using your PHP script. Let's start with opening database connection.

PHP Open Database Connection

The PHP mysql_connect() function is used to open a database connection. The function mysql_connect() takes total five parameters and returns a MySQL link identifier on success, otherwise FALSE on failure. Here is the general form to use mysql_connect() function to open a database connection in PHP:

connection mysql_connect(server, user, passwd, new_link, client_flag);

Here is the explanation of the above parameters:

server - This is an optional parameter. This parameter represents the host name running the database server. The default value is localhost:3306
user - This is also an optional parameter. This represent the username accessing the database. The default is the name of the server, who owns the server process
passwd - This is also an optional parameter. This represents the password of the user accessing the database. The default is an empty password
new_link - This is an optional parameter. If a second call is made to mysql_connect() with the same arguments, then no new connection will be established, instead the identifier of the already opened connection will be returned
client_flags - This is an optional parameter. Represents a combination of these 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

PHP Close Database Connection

Use mysql_close() function to close database connection in PHP. This function returns TRUE on success, otherwise FALSE on failure. Here is the general form of this function:

bool mysql_close (resource $link_identifier);

PHP Open and Close Database Connection Example

Let's go through the following example which will open and close a database connection:

<?php
$database_host = 'localhost:3036';
$database_user = 'guest';
$database_password = 'guest123';
$conn = mysql_connect($database_host, $database_user, $database_password);
if(! $conn)
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($conn);
?>

PHP Create MySQL Database

To create and delete a database using PHP, you should have admin priviledge. PHP uses mysql_query() function to create a MySQL database. This function takes two parameters and returns TRUE on success, otherwise, FALSE on failure. Here is the general form of this function:

bool mysql_query(sql, connection);

Here is an example, creates MySQL database using PHP:

<?php
$database_host = 'localhost:3036';
$database_user = 'root';
$database_password = 'rootpassword';
$conn = mysql_connect($database_host, $database_user, $database_password);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
$sql = 'CREATE Database test_db';
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not create database: ' . mysql_error());
}
echo "Database test_db created successfully\n";
mysql_close($conn);
?>

PHP Create Database Tables

<?php
$database_host = 'localhost:3036';
$database_user = 'root';
$database_password = 'rootpassword';
$conn = mysql_connect($database_host, $database_user, $database_password);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
$sql = 'CREATE TABLE employee( '.
       'emp_id INT NOT NULL AUTO_INCREMENT, '.
       'emp_name VARCHAR(20) NOT NULL, '.
       'emp_address  VARCHAR(20) NOT NULL, '.
       'emp_salary   INT NOT NULL, '.
       'join_date    timestamp(14) NOT NULL, '.
       'primary key ( emp_id ))';

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not create table: ' . mysql_error());
}
echo "Table employee created successfully\n";
mysql_close($conn);
?>

PHP Insert Data to MySQL Database

<?php
$database_host = 'localhost:3036';
$database_user = 'root';
$database_password = 'rootpassword';
$conn = mysql_connect($database_host, $database_user, $database_password);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'INSERT INTO employee '.
       '(emp_name,emp_address, emp_salary, join_date) '.
       'VALUES ( "guest", "XYZ", 2000, NOW() )';

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not enter data: ' . mysql_error());
}
echo "Entered data successfully\n";
mysql_close($conn);
?>

PHP Retrieve Data from MySQL Database

<?php
$database_host = 'localhost:3036';
$database_user = 'root';
$database_password = 'rootpassword';
$conn = mysql_connect($database_host, $database_user, $database_password);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "EMP ID :{$row['emp_id']}  <br> ".
         "EMP NAME : {$row['emp_name']} <br> ".
         "EMP SALARY : {$row['emp_salary']} <br> ".
         "--------------------------------<br>";
} 
echo "Data Fetched successfully\n";
mysql_close($conn);
?>

PHP Online Test

« Previous Tutorial Next Tutorial »