MySQL PHP connection

MySQL Tutorial

MySQL-Home MySQL-Environment setup MySQL- Workbench MySQL-Basic syntax MySQL-Operator MySQL-Data type MySQL-Comments MySQL-Create DB MySQL-Drop DB MySQL-Select DB MySQL-Create Table MySQL-Drop table MySQL-Truncate MySQL-Primary Key MySQL-Foreign Key MySQL-Null MySQL-Increment MySQL-Having MySQL-Top MySQL-Insert Statement MySQL-Select Statement MySQL-Alter Statement MySQL-Where MySQL-And & Or MySQL-Default values MySQL-Exists MySQL-Order by MySQL-View MySQL-Update Statement MySQL-Delete Statement MySQL-Like MySQL-Sort MySQL-Limit MySQL-Min MySQL-Max MySQL-Group MySQL-In MySQL-Between MySQL-Union MySQL-Count MySQL-Average MySQL-Sum MySQL-Date & Time MySQL-Import MySQL-Export MySQL-Index MySQL-Temporary MySQL-Join MySQL-Full Join MySQL-Inner Join MySQL-Left Join MySQL-Right Join MySQL-Store Procedure MySQL-Injection MySQL-PHP connection

« Previous Next »

In order to store or access the data inside a MySQL database, you first need to connect to the MySQL database server.

PHP offers two different ways to connect to MySQL server.

MySQLi (Improved MySQL)

PDO (PHP Data Objects) extensions

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

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

Both support Prepared Statements. Prepared Statements protect from SQL injection.

The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.

For installation details, http://php.net/manual/en/mysqli.installation.php

Before we can access data in the MySQL database, we need to be able to connect to the server:

Syntax : MySQLi, Procedural way


$conn = mysqli_connect("hostname", "username", "password", "database");

Syntax : MySQLi, Object Oriented way


$conn = new mysqli("hostname", "username", "password", "database");

PDO Installation

The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.

For installation details,http://php.net/manual/en/pdo.installation.php

Syntax : MySQLi, PHP Data Objects(PDO) way


$conn = new PDO("mysql:host=hostname;
dbname=database", "username", "password");

The hostname (server name)parameter in the above syntax specify the host name (e.g. localhost), or IP address of the MySQL server,

The username and password parameters specifies the credentials to access MySQL server,

The database parameter, specifies the name of database to performing queries.

Close the Connection

The connection to the MySQL database server will be closed automatically as soon as the execution of the script ends

To close the connection before, use the following:

Syntax : Close Connection


 //MySQLi Object-Oriented:
 $conn->close();
 //MySQLi Procedural:
 mysqli_close($conn);
 //PDO: 
 $conn = null;

Example : PHP Connection

  
 <?php
               
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection MySQLi Object-Oriented
$conn = new mysqli($servername, $username, $password);
// Check connection MySQLi Object-Oriented
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
// Close Connection MySQLi Object-Oriented
$conn->close();
//---------------------------------------------------------- 
// Create connection MySQLi Procedural
$conn = mysqli_connect($servername, $username, $password);
// Check connection MySQLi Procedural
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
// Close Connection  MySQLi Procedural
mysqli_close($conn);

//----------------------------------------------------------- 
// Create connection PDO
try {
    $conn = new PDO("mysql:host=$servername;dbname=myDB",
                   $username, $password);
    // set 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();
  }
  // Close Connection  PDO
  $conn = null;