PHP & MySQL Connection

PHP

"PHP is a server-side programming language that is flexible, easy to learn, and still the backbone of many websites, even though it is often thought to be outdated"

Hey, PHP wizard!  Ready to connect your PHP scripts to MySQL and store some real data? Without a database, your website is like a diary without pages—useless! Today, we’ll learn how to connect PHP with MySQL, fetch and insert data securely, and make your website dynamic! Let’s dive in!

Why Connect PHP with MySQL?

Imagine running a website where users can: Sign up & log in, Store and retrieve data, Manage posts, comments, or orders.

That’s where MySQL shines! Let’s set up the ultimate PHP-MySQL connection.

Setting Up the MySQL Database

Before connecting, let’s create a database and a table. Open phpMyAdmin or use the MySQL CLI and run:

CREATE DATABASE my_database;
USE my_database;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

We now have a users table!

Connecting PHP to MySQL

Method 1: Using MySQLi (Recommended)

$conn = new mysqli("localhost", "root", "", "my_database");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully!";

Secure & easy!

Method 2: Using PDO (More Flexible)

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

Works with multiple databases, not just MySQL!

Inserting Data into MySQL

Using MySQLi

$sql = "INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully!";
} else {
    echo "Error: " . $conn->error;
}

Boom! Data saved!

Using PDO (Prepared Statements)

$stmt = $conn->prepare("INSERT INTO users (username, email) VALUES (?, ?)");
$stmt->execute(["jane_doe", "jane@example.com"]);

Prevents SQL injection!

Fetching Data from MySQL

Using MySQLi

$result = $conn->query("SELECT * FROM users");
while ($row = $result->fetch_assoc()) {
    echo "Username: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
}

 Displays all users!

Using PDO

$stmt = $conn->query("SELECT * FROM users");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "Username: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
}

 Same result, but PDO-style!

Congrats, PHP hero!  You just: Connected PHP to MySQL, Inserted data securely, Fetched data dynamically.