php mysql

How to Connect to a MySQL Database Using PHP and Use Prepared Statements

by

Connecting to a MySQL database using PHP is a fundamental skill for web developers. MySQL, a popular open-source relational database management system, combined with PHP, a server-side scripting language, forms the backbone of many dynamic websites and applications. This tutorial will guide you through the process of establishing a connection between PHP and MySQL and using prepared statements for secure data handling.

Prerequisites

Before we begin, ensure you have the following:

  1. A web server with PHP installed (e.g., Apache, Nginx).
  2. MySQL installed and running.
  3. Basic knowledge of PHP and SQL.
  4. Access to a MySQL database (username, password, database name, and host).

Steps to Connect to MySQL Database Using PHP

There are two primary ways to connect PHP to MySQL:

  1. Using the mysqli extension (MySQL Improved).
  2. Using PDO (PHP Data Objects).

Method 1: Using mysqli

The mysqli extension is an improved version of the original mysql extension, offering enhanced features and security.

Step 1: Establishing a Connection

Create a PHP file (e.g., connect_mysqli.php) and add the following code:

<?php
$servername = "localhost";
$username = "Da_username";
$password = "Da_password";
$dbname = "Da_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

Step 2: Using Prepared Statements

Prepared statements are crucial for preventing SQL injection attacks. Here’s how you can use them with mysqli:

<?php
$servername = "localhost";
$username = "Da_username";
$password = "Da_password";
$dbname = "Da_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

// Prepare and bind
$stmt = $conn->prepare("INSERT INTO Users (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// Set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john.doe@example.com";
$stmt->execute();

echo "New records created successfully";

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

Explanation

  • prepare(): Prepares an SQL statement for execution.
  • bind_param(): Binds variables to the prepared statement as parameters. The “sss” string specifies the types of the variables (in this case, all strings).
  • execute(): Executes the prepared statement.
  • close(): Closes the statement and connection.

Method 2: Using PDO

PDO (PHP Data Objects) is a database access layer providing a uniform method of access to multiple databases.

Step 1: Establishing a Connection

Create a PHP file (e.g., connect_pdo.php) and add the following code:

<?php
$servername = "localhost";
$username = "Da_username";
$password = "Da_password";
$dbname = "Da_database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $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();
}
?>

Step 2: Using Prepared Statements

Here’s how you can use prepared statements with PDO:

<?php
$servername = "localhost";
$username = "Da_username";
$password = "Da_password";
$dbname = "Da_database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Prepare and bind
    $stmt = $conn->prepare("INSERT INTO Users (firstname, lastname, email) VALUES (:firstname, :lastname, :email)");
    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);

    // Set parameters and execute
    $firstname = "John";
    $lastname = "Doe";
    $email = "john.doe@example.com";
    $stmt->execute();

    echo "New records created successfully";
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>

Explanation

  • prepare(): Prepares an SQL statement for execution.
  • bindParam(): Binds a parameter to the specified variable name.
  • execute(): Executes the prepared statement.
  • null: Closes the connection.

Conclusion

Connecting to a MySQL database using PHP and using prepared statements are essential skills for secure and efficient database operations. The mysqli and PDO methods both provide robust ways to connect and interact with MySQL databases. Prepared statements are particularly important for preventing SQL injection attacks, making your applications more secure. By following the steps outlined above, you should be able to establish a secure connection to your MySQL database and handle data safely.

Leave a Comment