For dynamic website, web application MySQL play an important role. The term MySQLi means MySQL Improved. MySQLi work with MySQL version 4.1.13 or newer and PHP version 5.0.0. Today I will share how to create, read, update and delete (CRUD) data in database using PHP MySQLi.

In this tutorial. How to,

• Opening Database Connection
• Closing Database Connection
• Create Database
• Selecting Database
• Creating Table

OPENING DATABASE CONNECTION

Using mysqli_connect() to establish the database connection.

Syntax

<?PHP
  
connection mysqli_connect(server,user,password); 
?>

Server  − The host name running database server.
User  − The username accessing the database.
Password  − The password of the user accessing the database.

CLOSING DATABASE CONNECTION

Using mysqli_close() to close the database connection

Syntax

<?PHP
  bool mysqli_close($conn); 
?>

The following examples shows how to close a database connection:

<?PHP
    $dbhost = 'localhost';
   $dbuser =  'root';
   $dbpass = '';
   $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
   if(! $conn )
   {
      die('Could not connect: ' . mysqli_error());
   }
   echo 'Connected successfully';
   mysqli_close($conn);
?>

CREATING A DATABASE

The CREATE DATABASE statement is used to create a database.

Syntax

  <?PHP
  bool mysqli_query(connection, sql );
  ?>

The following examples shows how to create a database:

<?PHP
  $dbhost = 'localhost';
  $dbuser = 'root';
  $dbpass = '';
  $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
  if(! $conn ) die('Could not connect: ' . mysqli_error()); 
  echo 'Connected successfully<br>';
  $sql = 'CREATE Database test_db';
  $result = mysqli_query( $conn, $sql );
  if(! $result ) die('Could not create database: ' . mysqli_error());
  echo "Database test_db created successfully...";          
  mysqli_close($conn);
  ?>

SELECTING A DATABASE

Once connect with a database server then it is required to select a particular database where your all the tables are associated. mysqli_select_db() to select a database.

Syntax

<?PHP
  bool mysqli_select_db(connection , db_name);
  ?>

The following examples shows how to select a database:

<?PHP
  $dbhost = 'localhost';
  $dbuser = 'root';
  $dbpass = '';
  $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
  if(! $conn ) die('Could not connect: ' . mysqli_error()); 
  echo 'Connected successfully<br>';
  $result = mysqli_select_db( $conn,  'test_db');
  mysqli_close($conn);
  ?>

CREATING A TABLE

The CREATE TABLE statement is used to create a table.

We will create a table named “account”, with five columns: “acc_id”, “acc_name”, “acc_email”, “password” and “status”:

The following examples shows how to create the table:

<?PHP
  $dbhost = 'localhost';    $dbuser = 'root';    $dbpass = '';
  $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
  if(! $conn ) die('Could not connect: ' . mysqli_error()); 
  $db = mysqli_select_db( $conn,  'test_db');
  $sql = ' CREATE TABLE  account ( acc_id INT(20) NOT NULL AUTO_INCREMENT,    acc_name VARCHAR(155) NOT NULL,     acc_email  VARCHAR(155) NOT NULL,        password   VARCHAR(155) NOT NULL,     created_dt DATETIME NOT NULL,    status INT(1) NOT NULL DEFAULT "1",     primary key ( acc_id )) ';
  $result = mysqli_query( $conn, $sql );
  if(! $result ) die('Could not create table : ' . mysqli_error());
  echo "Table Created Successfully..."; 
  mysqli_close($conn);
  ?>

For MySQLi Manipulation will be in next tutorials.