How to Connect PHP Database from Scratch | MySQL and PDO Connection Example

How to Connect PHP Database from Scratch | MySQL and PDO Connection Example

When developing websites and applications, establishing a connection to a database is an essential step. PHP offers several ways to connect to MySQL or MariaDB databases. The two most common methods are using the mysqli extension and PDO (PHP Data Objects). This article explains how to connect to a PHP database using these two methods from scratch.

Preparation: Configuring PHP and Database

Before we begin, make sure you have PHP and a database management system (such as MySQL or MariaDB) installed. You will also need to create a database and configure the appropriate user and password for PHP access.

Using the mysqli Extension to Connect to the Database

PHP's mysqli extension provides powerful features for connecting to and operating MySQL databases. Below is an example of how to connect to a MySQL database using the mysqli extension:

$servername = "localhost";

$username = "root";

$password = "mypassword";

$dbname = "mydatabase";

try {

  $conn = new mysqli($servername, $username, $password, $dbname);

  if ($conn->connect_error) {

    throw new Exception($conn->connect_error);

  }

  $result = $conn->query("SELECT * FROM mytable");

  while ($row = $result->fetch_assoc()) {

    echo $row['id'] . " - " . $row['name'] . "<br>";

  }

  $conn->close();

} catch (Exception $e) {

  echo $e->getMessage();

}

Using PDO to Connect to the Database

In addition to mysqli, PHP also provides a more versatile way — PDO. PDO supports not only MySQL but also other database management systems. Below is an example of how to connect to a MySQL database using PDO:

try {

  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $stmt = $conn->prepare("SELECT * FROM mytable");

  $stmt->execute();

  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

    echo $row['id'] . " - " . $row['name'] . "<br>";

  }

  $conn = null;

} catch (PDOException $e) {

  echo $e->getMessage();

Practical Example: Connecting to a MySQL Database

In real development, we often need to use either the mysqli extension or PDO to connect to a MySQL database. Below is a practical example:

// Using mysqli extension to connect to the database

$conn = new mysqli("localhost", "root", "mypassword", "mydatabase");

if ($conn->connect_error) {

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

}

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

$users = $result->fetch_all(MYSQLI_ASSOC);

foreach ($users as $user) {

  echo $user['id'] . " - " . $user['username'] . "<br>";

}

$conn->close();

Conclusion

This article explained how to establish a PHP database connection from scratch using both the mysqli extension and PDO. With practical example codes, you can easily connect to a MySQL database and perform queries. Whether you choose mysqli or PDO, both can meet most development needs. We hope this article helps you get started with PHP database connections.