• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Connect

← Previous Next →

Before you can interact with a MySQL database, the first and most important step is to connect PHP to the database server.
This connection acts as a bridge that allows PHP scripts to send queries, fetch data, and manage records inside MySQL.

In this tutorial, you’ll learn how to establish a connection between PHP and MySQL using two popular extensions — MySQLi and PDO — along with best practices, error handling, and examples.

What is Database Connection?

A database connection is like opening a communication line between your PHP application and the MySQL database server. Without this connection, PHP cannot perform any SQL operations.

To establish a connection, you need:

  1. Server name – usually localhost if the database is on the same system.

  2. Username – by default, root for local development.

  3. Password – empty ("") by default in XAMPP or WAMP setups.

  4. Database name – the specific database you want to access.

Once the connection is successful, PHP can execute SQL queries and return results.

Connecting to MySQL Using MySQLi

MySQLi stands for MySQL Improved. It supports both procedural and object-oriented styles.

Example (Procedural Style)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "school_db";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully!";
?>

Explanation:

  • mysqli_connect() creates a new connection.

  • If the connection fails, mysqli_connect_error() gives the reason.

  • die() stops the script and prints the error.

Example (Object-Oriented Style)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "school_db";

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

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully!";
$conn->close();
?>

Explanation:

  • $conn = new mysqli(...) creates a new object connection.

  • $conn->connect_error checks for connection errors.

  • $conn->close() closes the connection after work is done.

Both methods are correct, but the object-oriented approach is cleaner and preferred in modern PHP applications.

Connecting Using PDO (PHP Data Objects)

PDO is a more flexible and secure method for connecting to databases.
The biggest advantage is that PDO works with multiple database types, not just MySQL (for example, PostgreSQL or SQLite).

Example

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "school_db";

try {
  $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully!";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>

Explanation:

  • new PDO() initializes a new connection.

  • The try...catch block handles connection errors gracefully.

  • setAttribute() sets the error mode to throw exceptions, making debugging easier.

PDO is highly recommended for large projects or applications that may need to switch databases in the future.

Verifying the Connection

After connecting, it’s good practice to verify the connection status:

if ($conn) {
  echo "Connection is active.";
} else {
  echo "Connection failed.";
}

In PDO, the connection fails automatically if an exception is thrown, so explicit checks aren’t required.

Closing the Connection

Always close your connection when it’s no longer needed to free resources:

  • MySQLi (Procedural): mysqli_close($conn);

  • MySQLi (OOP): $conn->close();

  • PDO: $conn = null;

Although PHP automatically closes connections when the script ends, doing it manually is a good habit, especially in larger applications.

Common Connection Errors

Error Message Cause Solution
Access denied for user Wrong username or password Check credentials
Unknown database Database name doesn’t exist Create the database first
Can’t connect to MySQL server Server not running Start MySQL service
Host not allowed Remote connections restricted Update MySQL config

MySQL Connection Best Practices

  1. Always use error handling (especially with PDO).

  2. Avoid hardcoding credentials — store them in a separate configuration file.

  3. Use environment variables for sensitive data in production.

  4. Close connections properly after finishing database operations.

  5. Use PDO for scalability — supports multiple databases and is safer with prepared statements.

Example: Central Database Connection File

Instead of writing connection code in every file, create one reusable file db_connect.php:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "school_db";

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

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
?>

Now, include it wherever you need to access the database:

<?php
include 'db_connect.php';

$sql = "SELECT * FROM students";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    echo $row['name'] . "<br>";
  }
} else {
  echo "No records found.";
}
$conn->close();
?>

This keeps your code clean and easy to maintain.

Summary of the Tutorial

  • A database connection allows PHP to communicate with MySQL.

  • PHP supports two main methods: MySQLi and PDO.

  • Always handle errors properly and close connections.

  • For scalability and flexibility, PDO is the recommended approach.

  • Use a central connection file to simplify database management across your application.

Practice Questions

  1. Write a PHP script using mysqli (procedural) to connect to a MySQL server on localhost with username root and no password.

  2. Write the same connection using the object-oriented style of mysqli.

  3. Create a PHP script using PDO to connect to a database named school_db on localhost. Include proper error handling.

  4. Modify a PDO connection script to catch exceptions and display a custom error message if the connection fails.

  5. Write a PHP script that checks if a connection is active and prints “Connection is successful” or “Connection failed”.

  6. Create a reusable PHP file db_connect.php that connects to MySQL using mysqli and include it in another PHP script to query a students table.

  7. Write a PHP script that connects using PDO and sets the error mode to throw exceptions.

  8. Demonstrate how to properly close a MySQLi connection in procedural style and object-oriented style.

  9. Write a PHP script that attempts a connection to a non-existent database and catches the error, printing “Database not found”.

  10. Explain the difference between MySQLi and PDO for connecting to a MySQL database and why you might choose one over the other.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top