PHP Connect to MySQL Server

Previous Page Next Page

Methods of Connecting to MySQL via PHP

To interact with data stored in a MySQL database, you must establish a connection to the MySQL database server. PHP provides two main methods for connecting to MySQL: MySQLi (Improved MySQL) and PDO (PHP Data Objects).

While PDO is more versatile and supports over twelve different databases, MySQLi is tailored specifically for MySQL and offers both object-oriented and procedural APIs. MySQLi's procedural API is particularly beginner-friendly.

Tip: MySQLi provides superior performance and features for MySQL-specific projects compared to PDO.

Establishing Connection to MySQL Database Server

In PHP, you can connect to MySQL using the mysqli_connect() function. This connection is essential for all interactions between PHP and the MySQL server. Below are the basic syntaxes for connecting using MySQLi and PDO:

Syntax: MySQLi, Procedural Style

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

Syntax: MySQLi, Object-Oriented Style

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

Syntax: PHP Data Objects (PDO)

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

In these syntaxes, hostname specifies the MySQL server's host name or IP address (e.g., localhost), username and password are the credentials for accessing MySQL, and database (if provided) is the default database for executing queries.

Below is an example demonstrating how to connect to a MySQL database server using MySQLi (both procedural and object-oriented approaches) and PDO:

Example

Procedural Object Oriented PDO

Download

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Print host information
echo "Connect Successfully. Host info: " . mysqli_get_host_info($link);
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");

// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}

// Print host information
echo "Connect Successfully. Host info: " . $mysqli->host_info;
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost", "root", "");

// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Print host information
echo "Connect Successfully. Host info: " . 
$pdo->getAttribute(constant("PDO::ATTR_CONNECTION_STATUS"));
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
?>

Note: By default, the username for the MySQL database server is root and there is no password set. However, for security reasons, it's advisable to set a strong password for MySQL accounts to prevent unauthorized access to your databases.

Tip: When you set the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION, PDO will throw exceptions whenever a database error occurs.

Closing the MySQL Database Server Connection

The connection to the MySQL database server closes automatically when the script execution ends. To close it earlier, you can use the PHP mysqli_close() function.

Example

Procedural Object Oriented PDO

Download

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Print host information
echo "Connect Successfully. Host info: " . mysqli_get_host_info($link);

// Close connection
mysqli_close($link);
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");

// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}

// Print host information
echo "Connect Successfully. Host info: " . $mysqli->host_info;

// Close connection
$mysqli->close();
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");

// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Print host information
echo "Connect Successfully. Host info: " . 
$pdo->getAttribute(constant("PDO::ATTR_CONNECTION_STATUS"));
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}

// Close connection
unset($pdo);
?>

Previous Page Next Page