🗄️ PHP MySQL – Connect, Query, and Manage Databases with PHP

Learn how to interact with MySQL databases using PHP to build dynamic, data-driven applications efficiently and securely.

---

🧲 Introduction – Why Use MySQL with PHP?

PHP and MySQL are a classic duo in web development. MySQL is an open-source relational database management system (RDBMS), and PHP offers native support for connecting and executing SQL queries. Together, they power thousands of websites, applications, and content management systems like WordPress, Joomla, and Drupal.

🎯 In this guide, you’ll learn:

• How to connect PHP to a MySQL database
• How to run SQL queries using mysqli
• How to handle data securely
• Common operations like SELECT, INSERT, UPDATE, DELETE

---

🗄️ PHP MySQL

You can connect to MySQL using:

Extension	Description
mysqli	Object-oriented and procedural; modern and secure
PDO	More abstract and flexible (supports multiple DBs)

This article focuses on mysqli for native MySQL interaction.

---

🔗 Connect to MySQL using mysqli

$host = "localhost";
$user = "root";
$pass = "";
$db   = "test_db";

$conn = new mysqli($host, $user, $pass, $db);

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

✅ Use $conn->connect_error to catch connection issues
✅ Replace credentials with secure values in production

---

🔍 SELECT Data from MySQL

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

while ($row = $result->fetch_assoc()) {
    echo "ID: {$row['id']} - Name: {$row['name']}<br>";
}

📌 Always check if $result->num_rows > 0 before looping

---

📝 INSERT Data into MySQL

$name = "Alice";
$sql = "INSERT INTO users (name) VALUES ('$name')";

if ($conn->query($sql) === TRUE) {
    echo "✅ New record created successfully";
} else {
    echo "❌ Error: " . $conn->error;
}

⚠️ Don’t use plain strings with user input — use prepared statements for security

---

🛡️ Prevent SQL Injection with Prepared Statements

$stmt = $conn->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->bind_param("s", $name);
$stmt->execute();

✅ Use bind_param() to bind variables safely
✅ "s" means string; use "i" for integer, etc.

---

✏️ UPDATE and DELETE Examples

✅ UPDATE

$stmt = $conn->prepare("UPDATE users SET name = ? WHERE id = ?");
$stmt->bind_param("si", $new_name, $id);
$stmt->execute();

✅ DELETE

$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();

📌 Always confirm deletion actions on the frontend to prevent accidental data loss

---

📊 Common mysqli Functions

Function	Purpose
mysqli_connect()	Establish connection (procedural)
$conn->query()	Run SQL queries
$conn->prepare()	Prepare SQL statements securely
$stmt->bind_param()	Bind parameters to a prepared statement
$stmt->execute()	Execute the statement
$stmt->fetch()	Fetch results (with bind_result)
$conn->close()	Close connection

---

🧠 Best Practices for PHP + MySQL

• ✅ Use prepared statements for all user input
• ✅ Use utf8mb4 charset for full Unicode support (emojis, special characters)
• ✅ Keep DB credentials in separate configuration files
• ✅ Avoid displaying raw errors in production (log them instead)
• ✅ Use PDO for portability if you plan to support other DBs in the future

---

📌 Summary – Recap & Next Steps

PHP and MySQL are tightly integrated, allowing developers to quickly build powerful, data-driven applications. With mysqli, you can securely query and manage your database directly from PHP scripts.

🔍 Key Takeaways:

• Use mysqli or PDO for database interaction
• Always protect input using prepared statements
• Perform common SQL operations (SELECT, INSERT, UPDATE, DELETE) safely
• Secure credentials and database access in production

⚙️ Real-World Use Cases:
Login systems, content management, blog platforms, e-commerce sites, data dashboards

---

❓ Frequently Asked Questions (FAQs)

❓ What is the difference between mysqli and PDO?
✅ mysqli is MySQL-specific. PDO supports multiple databases and provides a uniform interface.

❓ Should I use procedural or OOP with mysqli?
✅ OOP is cleaner and more scalable for modern applications.

❓ Is mysqli_real_escape_string() safe enough?
⚠️ It helps, but prepared statements are the recommended method for securing input.

❓ Can I fetch results as arrays?
✅ Yes, use $result->fetch_assoc() or $result->fetch_array().

❓ How do I close a MySQL connection in PHP?
✅ Use $conn->close(); when you’re done querying the database.

---

Share Now :