codescracker


php

PHP and MySQL Database



« Previous Tutorial Next Tutorial »


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:

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);
?>

« Previous Tutorial Next Tutorial »



Tools
Calculator

Quick Links
Signup - Login - Give Online Test