PHP and MySQL Databases

PHP and MySQL Databases – Complete Guide 🛢️

Want to store user details, comments, or even your secret pizza recipe? 🍕 PHP and MySQL allow you to create, store, and manage database records efficiently.

There are two main ways to interact with MySQL databases in PHP:

  • MySQLi (Improved MySQL extension – supports procedural & OOP)
  • PDO (PHP Data Objects – supports multiple databases)

🔹 Connecting PHP to MySQL

Before performing database operations, you need to establish a connection.

📝 Example: Connecting to MySQL (MySQLi Procedural)

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

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

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

Try It Now

🔹 Creating a MySQL Database

To create a database, use the CREATE DATABASE SQL statement.

📝 Example: Creating a Database

<?php
$conn = new mysqli("localhost", "root", "");

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

// Create database
$sql = "CREATE DATABASE my_database";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully!";
} else {
    echo "Error creating database: " . $conn->error;
}

$conn->close();
?>

Try It Now

🔹 Creating a Table

Tables store data in rows and columns. Let’s create a users table.

📝 Example: Creating a Table

<?php
$conn = new mysqli("localhost", "root", "", "my_database");

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

// Create table
$sql = "CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
)";

if ($conn->query($sql) === TRUE) {
    echo "Table 'users' created successfully!";
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

Try It Now

🔹 Inserting Data into a Table

To add a user, use the INSERT INTO SQL statement.

📝 Example: Inserting Data

<?php
$conn = new mysqli("localhost", "root", "", "my_database");

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

// Insert data
$sql = "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "New record added successfully!";
} else {
    echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();
?>

Try It Now

🔹 Retrieving Data

Use SELECT to get data from a table.

📝 Example: Fetching Data

<?php
$conn = new mysqli("localhost", "root", "", "my_database");

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

// Retrieve data
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No records found!";
}

$conn->close();
?>

Try It Now

🔹 Updating Data

Use UPDATE to modify existing records.

📝 Example: Updating Data

<?php
$conn = new mysqli("localhost", "root", "", "my_database");

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

// Update data
$sql = "UPDATE users SET email='alice.new@example.com' WHERE name='Alice'";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully!";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

Try It Now

🔹 Deleting Data

Use DELETE to remove records.

📝 Example: Deleting Data

<?php
$conn = new mysqli("localhost", "root", "", "my_database");

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

// Delete data
$sql = "DELETE FROM users WHERE name='Alice'";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully!";
} else {
    echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

Try It Now

🎯 Key Takeaways

  • Create, Read, Update, and Delete (CRUD) data in MySQL using PHP.
  • Use MySQLi or PDO for database interactions.