Creating a PHP and MySQL Connection

Establishing a connection between PHP and MySQL is essential for building dynamic web applications. With this connection, you can access and manipulate data stored in a MySQL database through PHP scripts. This article walks you through every way to connect, when to use each one, and the safety practices that keep your application secure.

This page covers:

• The three connection APIs PHP offers and which one to pick
• Connecting with the procedural mysqli style and checking for errors
• The object-oriented mysqli style and the modern PDO style
• Selecting a database, running a first query, and closing the connection
• Common gotchas: character sets, error reporting, and credential handling

Prerequisites

Before we dive into creating the connection, it's important to make sure you have the following prerequisites in place:

• A web server with PHP installed (such as Apache or Nginx)
• A MySQL database
• PHP MySQL extension (included in most PHP installations)

Understanding PHP and MySQL Connection

A PHP and MySQL connection involves two main components: PHP and a MySQL database. PHP is a server-side scripting language used for creating dynamic web pages, while a MySQL database is used for storing and retrieving data.

PHP gives you three ways to talk to MySQL. Knowing the difference up front saves you from rewriting code later:

The old mysql_* functions (without the i) were removed in PHP 7 — never use them. For new projects, prefer PDO or object-oriented mysqli because they support prepared statements cleanly, which are your main defense against SQL injection. This page shows all three so you can read and maintain any codebase. For a deeper look at the mysqli extension itself, see PHP MySQLi.

Establishing a Connection

To establish a connection between PHP and MySQL, you'll need to use the mysqli_connect() function. This function takes several parameters, including the server name, username, and password.

Here is an example of how to use the mysqli_connect() function:

PHP example of how to use the mysqli_connect function

<?php 
$server = "localhost";
$username = "your_username";
$password = "your_password";

// Establish connection 
$conn = mysqli_connect($server, $username, $password); 

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

In the example above, $server is set to localhost, which is the default location of the MySQL server. $username and $password should be set to the username and password used to access the database.

After establishing the connection, we use the mysqli_connect_error() function to check if the connection was successful. If the connection fails, the script outputs an error message and terminates the connection. If the connection is successful, the script outputs "Connected successfully".

Tip: You can also pass the database name as the fourth parameter to mysqli_connect() to simplify the connection logic and skip a separate database selection step: $conn = mysqli_connect($server, $username, $password, "your_database");

Object-Oriented mysqli

The same connection in the object-oriented style creates a mysqli object with new. Many developers find it cleaner because the connection and its methods live on one object:

<?php
$conn = new mysqli("localhost", "your_username", "your_password", "your_database");

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

Connecting with PDO

PDO (PHP Data Objects) gives you one consistent API across many databases. The connection details go into a DSN (Data Source Name) string:

<?php
$dsn = "mysql:host=localhost;dbname=your_database;charset=utf8mb4";

try {
    $pdo = new PDO($dsn, "your_username", "your_password");
    // Make PDO throw exceptions on error instead of failing silently
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

Set the character set. Always specify charset=utf8mb4 (PDO) or call $conn->set_charset("utf8mb4") (mysqli). Without it, multi-byte characters and emojis can be stored or returned incorrectly.

Selecting a Database

Once you have established a connection to the MySQL server, you'll need to select a database to work with. You can do this using the mysqli_select_db() function.

Here is an example of how to use the mysqli_select_db() function:

PHP example of how to use the mysqli_select_db function

<?php 
$server = "localhost";
$username = "your_username";
$password = "your_password";
$db = "your_database";

// Establish connection 
$conn = mysqli_connect($server, $username, $password); 

// Check connection 
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Select database 
$select_db = mysqli_select_db($conn, $db); 

// Check database selection 
if (!$select_db) {
    die("Error selecting database: " . mysqli_error($conn));
}
echo "Database selected successfully";
?>

In the example above, $db is set to the name of the database you want to select. After establishing the connection, we use the mysqli_select_db() function to select the database. The function takes two parameters: the connection and the name of the database.

We then use the mysqli_error() function to check if the database was selected successfully. If the selection fails, the script outputs an error message and terminates the connection. If the selection is successful, the script outputs "Database selected successfully".

Running Your First Query

A connection is only useful once you run a query. The example below selects rows from a users table and loops over the results. Because the value comes from a variable, it uses a prepared statement so user input can never be injected into the SQL:

<?php
$conn = new mysqli("localhost", "your_username", "your_password", "your_database");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare a parameterized query
$stmt = $conn->prepare("SELECT id, name FROM users WHERE id > ?");
$stmt->bind_param("i", $minId); // "i" = integer parameter
$minId = 0;
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row["id"] . ": " . $row["name"] . "\n";
}

$stmt->close();
$conn->close();
?>

Never build queries by concatenating raw user input into the SQL string — that opens the door to SQL injection. To go deeper on parameter binding, see MySQL Prepared Statements. For the full CRUD set, see Insert Data and Select Data.

Closing the Connection

When you are finished working with a MySQL database, it's important to close the connection. This helps to free up resources and prevent potential security issues.

To close the connection, you can use the mysqli_close() function. Here is an example of how to use the mysqli_close() function:

PHP example of how to use the mysqli_close function

<?php 
$server = "localhost";
$username = "your_username";
$password = "your_password";
$db = "your_database";

// Establish connection 
$conn = mysqli_connect($server, $username, $password); 

// Check connection 
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Select database 
$select_db = mysqli_select_db($conn, $db); 

// Check database selection 
if (!$select_db) {
    die("Error selecting database: " . mysqli_error($conn));
}

// Close connection 
mysqli_close($conn); 
echo "Connection closed";
?>

In the example above, we use the mysqli_close() function to close the connection. The function takes one parameter: the connection. The script then outputs "Connection closed" to confirm that the connection has been closed.

Security Note: In production environments, avoid hardcoding credentials. Use environment variables or configuration files to store sensitive data.

Conclusion

In this article, we covered the prerequisites, the three connection APIs (procedural mysqli, object-oriented mysqli, and PDO), establishing a connection, selecting a database, running a safe parameterized query, and closing the connection. You should now be able to connect PHP to MySQL and start performing CRUD operations.

For modern PHP applications, prefer PDO or the object-oriented mysqli interface with prepared statements: they offer better security and clearer error handling. From here, continue with Create a MySQL Database, Create a Table, and the MySQL Database overview.

Practice