PHP MySQL: Connecting to MySQL Database

Step 1: Install PHP and MySQL

Ensure that PHP and MySQL are installed on your system. If you're using XAMPP, MAMP, or WAMP, they come bundled together.

Step 2: Create a MySQL Database

1. Open MySQL (via phpMyAdmin, command line, or MySQL Workbench).

2. Create a database using the following SQL command:

   CREATE DATABASE my_database;
   

3. Create a table inside the database:

   CREATE TABLE users (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(100) NOT NULL,
       email VARCHAR(100) UNIQUE NOT NULL,
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   

Step 3: Connect to MySQL Database Using PHP

PHP provides two main ways to connect to MySQL:

1. MySQLi (Improved)

2. PDO (PHP Data Objects)

Method 1: Using MySQLi (Procedural)

<?php
$servername = "localhost";
$username = "root"; // Change if you have a different user
$password = ""; // Change if you have set a 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!";
?>

Method 2: Using MySQLi (Object-Oriented)

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

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

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

Method 3: Using PDO (Recommended for Security)

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

try {
    $conn = new PDO("mysql:host=$servername;dbname=$database", $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 4: Insert Data into MySQL Table

After connecting, you can insert data using SQL queries.

Using MySQLi (Procedural)

$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
if (mysqli_query($conn, $sql)) {
    echo "New record created successfully!";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
mysqli_close($conn);

Using PDO

$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$stmt = $conn->prepare($sql);
$stmt->execute(['name' => 'John Doe', 'email' => 'john@example.com']);
echo "New record created successfully!";

Step 5: Retrieve Data from MySQL Table

Using MySQLi (Procedural)

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {
    echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}
mysqli_close($conn);

Using PDO

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

Step 6: Close the Database Connection

For MySQLi, use:

mysqli_close($conn);

For PDO, the connection closes automatically when the script ends, or you can explicitly set it to null:

$conn = null;

Conclusion

• Use MySQLi if you are working with a simple project.

• Use PDO if you need a secure and flexible approach (especially for prepared statements).

• Always validate user input to prevent SQL injection.

• Consider using environment variables for database credentials instead of hard coding them in the script.