Understand how to interact with databases using PHP (e.g., MySQL, PostgreSQL) through MySQLi or PDO.

Interacting with databases using PHP can be accomplished through either MySQLi (MySQL Improved) or PDO (PHP Data Objects). Below is an overview of each approach.

MySQLi (MySQL Improved)

1. Connect to the Database

   $mysqli = new mysqli("localhost", "username", "password", "database");
   
   // Check connection
   if ($mysqli->connect_error) {
       die("Connection failed: " . $mysqli->connect_error);
   }
   

2. Performing Queries

   • Select Query

     $result = $mysqli->query("SELECT * FROM table_name");
     
     while ($row = $result->fetch_assoc()) {
         echo $row['column_name'];
     }
     

   • Insert Query

     $mysqli->query("INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2')");
     

3. Close the Connection

   $mysqli->close();
   

PDO (PHP Data Objects)

1. Connect to the Database

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

2. Performing Queries

   • Select Query

     $stmt = $pdo->query("SELECT * FROM table_name");
     while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
         echo $row['column_name'];
     }
     

   • Insert Query

     $stmt = $pdo->prepare("INSERT INTO table_name (column1, column2) VALUES (:value1, :value2)");
     $stmt->execute(['value1' => 'value1', 'value2' => 'value2']);
     

3. Close the Connection

   $pdo = null;  // Close the connection
   

Conclusion

Both MySQLi and PDO are effective for interacting with databases in PHP. MySQLi is specific to MySQL databases, while PDO supports multiple database types. Choose according to your project requirements and database compatibility.