Contecting PHP with SQL

Introduction to PHP and MySQL

If you are developing a dynamic website or a web application using PHP, connecting it to a MySQL database is an essential step. This guide explains how to connect PHP to MySQL database using both MySQLi and PDO, the two most commonly used extensions for secure and efficient database interaction.

Whether you're a beginner or an experienced developer, understanding this connection process ensures smooth data handling, better performance, and enhanced security for your web applications.

What is PHP and MySQL?

PHP (Hypertext Preprocessor) is a popular open-source server-side scripting language. It's commonly used to develop dynamic and interactive websites.

MySQL is a widely used relational database management system (RDBMS). It stores and manages data efficiently and is often used in combination with PHP in web development.

Why Connect PHP with MySQL?

Here are the main reasons developers connect PHP to a MySQL database:

• Store user data (e.g., registration, login)
• Manage blog posts, product listings, and comments
• Perform dynamic queries and analytics
• Build scalable and secure data-driven applications

PHP MySQL Connection Methods

There are two primary ways to connect PHP to a MySQL database:

1. MySQLi (MySQL Improved Extension)
   • Procedural style
   • Object-oriented style
2. PDO (PHP Data Objects)
   • Object-oriented and supports multiple databases

How to Connect PHP to MySQL using MySQLi (Procedural)

<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";

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

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

This is the simplest way to connect PHP with MySQL.

MySQLi (Object-Oriented) Connection Example

<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";

// Create connection
$conn = new mysqli($host, $username, $password, $database);

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

💡 Recommended for better structure and object-oriented development.

Connecting PHP to MySQL Using PDO (PHP Data Objects)

<?php
$host = "localhost";
$db = "mydatabase";
$user = "root";
$pass = "";

try {
    $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    // Set error mode
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

PDO is highly secure and allows support for multiple databases like PostgreSQL and SQLite.

MySQLi vs PDO: Which One Should You Choose?

Use PDO if:

• You want cross-database compatibility
• You prefer prepared statements for enhanced security

Use MySQLi if:

• You're only working with MySQL
• You're building performance-focused applications

Handling Errors in PHP MySQL Connection

Instead of showing raw errors to the user, handle them gracefully:

if (!$conn) {
    error_log("Connection failed: " . mysqli_connect_error());
    die("We're experiencing technical difficulties.");
}

Secure PHP MySQL Connection Tips

• Use Prepared Statements to prevent SQL injection
• Never store passwords in plain text
• Limit database user permissions
• Use environment variables for DB credentials
• Enable SSL connection for remote databases

Best Practices for PHP Database Connection

• Use PDO with try-catch blocks
• Use persistent connections for performance (when needed)
• Close your connection when done: mysqli_close($conn);
• Avoid hard-coding credentials
• Use a configuration file for DB settings

Example config file:

<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'mydatabase');
?>

Complete CRUD Operations in PHP and MySQL

INSERT:

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(["John Doe", "john@example.com"]);

SELECT:

$stmt = $pdo->query("SELECT * FROM users");
while ($row = $stmt->fetch()) {
    echo $row['name'] . "
";
}

UPDATE:

$stmt = $pdo->prepare("UPDATE users SET name = ? WHERE id = ?");
$stmt->execute(["Jane", 1]);

DELETE:

$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([1]);

Common Errors and Troubleshooting

Always check:

• Your database name
• Credentials
• Server is running (especially in localhost)

Frequently Asked Questions (FAQs)

Q1: How to connect PHP to MySQL using XAMPP?

Start Apache and MySQL in XAMPP, then use:

$conn = mysqli_connect("localhost", "root", "", "your_db");

Q2: Should I use MySQLi or PDO?

Use PDO for cross-platform compatibility and MySQLi if you're using only MySQL and prefer either style.

Q3: How do I test if my PHP-MySQL connection is successful?

Use echo "Connected!" after the connection block or var_dump($conn).

Q4: Can I connect PHP to a remote MySQL database?

Yes, just replace "localhost" with the remote IP or domain name and allow remote access in the server.

Q5: How do I close the database connection?

Use mysqli_close($conn); or $pdo = null; to close connections.