PHP : Connect to MySQL

To connect to a MySQL database in PHP, you can use either the mysqli extension or PDO (PHP Data Objects). Below are examples of connecting to a MySQL database using both mysqli and PDO.

Using mysqli:

Here's an example of connecting to a MySQL database using mysqli :

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

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

Using PDO:

Here's an example of connecting to a MySQL database using PDO:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
 

Explanation:

• Database Credentials: Replace "your_username", "your_password", and "your_database" with your actual database credentials.
• Connection Object: Both mysqli and PDO create a connection object ( $conn ) that you can use for executing queries.

Closing the Connection:

It's good practice to close the database connection when you're done with it:

Using mysqli :

$conn->close();
 

Using PDO:

$conn = null;
 

Full Example with mysqli:

Here's a complete example using mysqli to connect to a MySQL database, execute a query, and fetch results:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

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

// SQL query
$sql = "SELECT * FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // Output data of each row
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

// Close connection
$conn->close();
 

Full Example with PDO:

Here's a complete example using PDO to connect to a MySQL database, execute a query, and fetch results:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

try {
    // Create connection
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL query
    $sql = "SELECT * FROM users";
    $stmt = $conn->prepare($sql);
    $stmt->execute();

    // Fetch all rows
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Output data
    foreach ($result as $row) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
} finally {
    // Close connection
    $conn = null;
}
 

Important Notes:

• Replace "your_username", "your_password", and "your_database" with your actual database credentials.
• Always sanitize user input and use prepared statements to prevent SQL injection attacks.
• Check for connection errors using if ($conn->connect_error) or using try-catch with PDOException for PDO.
• Close the connection after you finish working with the database to free up resources.

These examples should help you get started with connecting to a MySQL database using PHP.