PHP | MySQL Database Introduction

What is MySQL? 
MySQL is an open-source relational database management system (RDBMS). It is the most popular database system used with PHP. MySQL is developed, distributed, and supported by Oracle Corporation.  

• 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 is ideal for both small and large applications.
• MySQL is very fast, reliable, and easy to use database system.It uses standard SQL
• MySQL compiles on a number of platforms.

Downloading MySQL Database 
MySQL can be downloaded for free from this link. 

How to connect PHP with MySQL Database? 
PHP 5 and later can work with a MySQL database using:  

1. MySQLi extension.
2. PDO (PHP Data Objects).

Difference Between MySQLi and PDO  

• 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.
• If at some point of development phase, the user or the development team wants to change the database then it is easy to that in PDO than MySQLi as PDO supports 12 different database systems.He would have to only change the connection string and a few queries. With MySQLi,he will need to rewrite the entire code including the queries.

There are three ways of working with MySQl and PHP 

1. MySQLi (object-oriented)
2. MySQLi (procedural)
3. PDO
    

Connecting to MySQL database using PHP
There are 3 ways in which we can connect to MySQl from PHP as listed above and described below: 

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

  Syntax: 

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

Output: 

Explanation: We can create an instance of the mysqli class providing all the necessary details required to establish the connection such as host, username, password etc. If the instance is created successfully then the connection is successful otherwise there is some error in establishing connection. 
 

• Using MySQLi procedural procedure : There is also a procedural approach of MySQLi to establish a connection to MySQL database from a PHP script as described below. 

  Syntax: 

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

Output: 

Explanation: In MySQLi procedural approach instead of creating an instance we can use the mysqli_connect() function available in PHP to establish a connection. This function takes the information as arguments such as host, username , password , database name etc. This function returns MySQL link identifier on successful connection or FALSE when failed to establish a connection. 

• Using PDO procedure: PDO stands for PHP Data Objects. That is, in this method we connect to the database using data objects in PHP as described below: 

  Syntax: 

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

Output: 

Explanation:The exception class in PDO is used to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block. 

                                                                                     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. Here we have described the syntax of closing the connection to a MySQL database in all 3 methods described above. We have assumed that the reference to the connection is stored in $conn variable.

• Using MySQLi object oriented procedure 
  Syntax 

$conn->close();

• Using MySQLi procedural procedure 
  Syntax 

mysqli_close($conn);

• Using PDO procedure 
  Syntax 

$conn = null;