×
☰ See All Chapters

PHP Connecting to MySQL

In this chapter we demonstrate three ways to connect with PHP to MySQL database.

  • Object oriented style 

  • Procedural style 

  • PDO style 

While connecting to database, you may face the issue “The server requested authentication method unknown to the client”.

By default and for some reason, MySQL 8 default plugin is auth_socket. Applications will most times expect to log in to your database using a password. If you have not yet already changed your MySQL default authentication plugin, you can do so by:

1. Log in as root to mysql

2. Run this sql command:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password

BY 'password';  

Replace 'password' with your root password. In case your application does not log in to your database with the root user, replace the 'root' user in the above command with the user that your application uses.

Object oriented style

Connect to Server

$conn = new mysqli("localhost","my_user","my_password");

Set the default database: $conn->select_db("my_db");

Connect to Database

$conn = new mysqli("localhost","my_user","my_password","my_db");

Object oriented style Example

<?php

$servername = "localhost";

$username = "root";

$password = "pass";

 

// Create connection

$conn = new mysqli($servername, $username, $password);

 

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

echo "Connected successfully";

 

?>

Procedural style

Connect to Server

$conn = mysqli_connect("localhost","my_user","my_password");

Set the default database: mysqli_select_db($conn, "my_db");

Connect to Database

$conn = mysqli_connect("localhost","my_user","my_password","my_db");

Procedural style Example

<?php

$servername = "localhost";

$username = "root";

$password = "pass";

 

// Create connection

$conn = mysqli_connect($servername, $username, $password);

 

// Check connection

if (!$conn) {

    die("Connection failed: " . mysqli_connect_error());

}

echo "Connected successfully";

?>

PDO style

The above styles described can connect to only MySQL database, suppose if you have to connect to other databases like oracle, then you should use PDO style. PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases. So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code - queries included. Another great benefit of PDO is that it has an exception class 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.

<?php

$servername = "localhost";

$username = "username";

$password = "password";

 

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

When we are finished querying the database, it is recommended to close the connection to free up resources.

Object oriented style

$conn->close();

Procedural style

mysqli_close($conn);

All Chapters
Author