Creating and Populating Tables

Welcome to the next lesson! In this lesson, you’ll learn how to create tables in a MySQL database and populate them with data using PHP. Let’s build your database structure!

What Are Tables in a Database?

Tables are the building blocks of relational databases. They organize data into rows and columns:

• Rows: Represent individual records.
• Columns: Represent fields in each record.

Example Table: A table for storing user information:

| ID | Name     | Email           |
|----|----------|-----------------|
| 1  | Alice    | alice@mail.com  |
| 2  | Bob      | bob@mail.com    |

Creating a Table Using SQL

Use the CREATE TABLE statement to define the structure of your table. Example:

Explanation:

• id: A unique identifier for each record.
• name and email: Fields for user information.
• created_at: Timestamp for when the record was created.

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

Creating Tables with PHP

Use PHP to execute the CREATE TABLE statement. Example:

Key Point: Use the exec() method for statements that don’t return results.

<?php
  $sql = "CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )";

  try {
    $pdo->exec($sql);
    echo "Table created successfully!";
  } catch (PDOException $e) {
    echo "Error creating table: " . $e->getMessage();
  }
?>

Inserting Data into a Table

Use the INSERT INTO statement to add records to your table. Example:

Tip: Use placeholders (:name, :email) to prevent SQL injection.

<?php
  $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
  $stmt = $pdo->prepare($sql);
  $stmt->execute(["name" => "Alice", "email" => "alice@mail.com"]);

  echo "Data inserted successfully!";
?>

Inserting Multiple Records

Use a loop to insert multiple records into your table. Example:

Tip: Batch processing is efficient for inserting multiple records.

<?php
  $users = [
    ["name" => "Bob", "email" => "bob@mail.com"],
    ["name" => "Charlie", "email" => "charlie@mail.com"]
  ];

  $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
  $stmt = $pdo->prepare($sql);

  foreach ($users as $user) {
    $stmt->execute($user);
  }

  echo "Multiple records inserted successfully!";
?>

Viewing Data in a Table

After populating your table, use the SELECT statement to view the data. Example:

Key Point: Use the PDO::FETCH_ASSOC mode to fetch data as an associative array.

<?php
  $sql = "SELECT * FROM users";
  $stmt = $pdo->query($sql);

  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: " . $row["id"] . ", Name: " . $row["name"] . ", Email: " . $row["email"] . "<br>";
  }
?>

Handling Errors

Use try-catch blocks to handle errors when creating or populating tables. Example:

Tip: Log errors for debugging instead of displaying them to users.

<?php
  try {
    $stmt = $pdo->prepare($sql);
    $stmt->execute(["name" => "Dave", "email" => "dave@mail.com"]);
    echo "Data inserted successfully!";
  } catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
  }
?>

Great Job!

Congratulations! You’ve learned how to create and populate tables in a MySQL database using PHP. In the next lesson, we’ll explore querying data with advanced SQL techniques. Let’s keep coding!