PHP - MySQL Database

MySQL is an open-source relational database management system (RDBMS).

It is the most popular database system used with PHP.

The data in a MySQL database are stored in tables which consists of columns and rows.

MySQL is a database system that runs on a server.

MySQL Tutorial

You can learn full MySQL tutorial here.

How To Connect PHP to MySQL

PHP 5 and later can work with a MySQL database using:

PDO works on 12 different database systems, whereas MySQLi works only with MySQL databases.

Both PDO and MySQLi are object-oriented, but MySQLi also offers a procedural API.

There are three ways of working with MySQl and PHP.

Connecting to MySQL database using PHP

MySQLi object-oriented procedure

We can use the MySQLi object-oriented procedure to establish a connection to MySQL database from a PHP script.

 <?php
 $servername = "localhost";
$username = "username";
$password = "password";
// Creating connection
$conn = new mysqli($servername, $username, $password);
// Checking connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
    ?>

You can learn full MySQL tutorial here.

MySQLi procedural procedure

There is also a procedural approach of MySQLi to establish a connection to MySQL database from a PHP script.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Creating connection
$conn = mysqli_connect($servername, $username, $password);
// Checking connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

You can learn full MySQL tutorial here.

Using PDO procedure

PDO stands for PHP Data Objects. That is, in this method we connect to the database using data objects in PHP.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {
    $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
    // setting the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>

Closing A Connection

When we establish a connection to MySQL database from a PHP script , we should also disconnect or close the connection when our work is finished.

MySQLi object oriented procedure

$conn->close();

MySQLi procedural procedure

mysqli_close($conn);

MySQLi PDO procedure

$conn = null;

You can learn full MySQL tutorial here.

Insert Data into MySQL Database

Data can be entered into MySQL tables by executing SQL INSERT statement through PHP .

 <?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $sql = 'INSERT INTO students '.
      '(ROLL_NO,STU_NAME, SUBJECT) '.
      'VALUES ( "12", "Jeni","PHP" )'; 
   mysql_select_db('first_db');
   $retval = mysql_query( $sql, $conn );
   if(! $retval ) {
      die('Could not enter data: ' . mysql_error());
   }
   echo "Entered data successfully\n";
   mysql_close($conn);
?>

You can learn full MySQL tutorial here.

Getting Data From MySQL Database

Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP.

    <?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $sql = 'SELECT ROLL_NO, TECH_NAME, SUBJECT FROM Students';
   mysql_select_db('first_db');
   $retval = mysql_query( $sql, $conn );
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "ROLL_NO :{$row['ROLL_NO']}   ".
         "STU_NAME : {$row['TECH_NAME']}  ".
         "SUBJECT : {$row['SUBJECT']}  ".
         "--------------------------------";
   }
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

You can learn full MySQL tutorial here.