Back to course

Setting up a Database and Tables

PHP: The Complete 0 to Hero Bootcamp

44. Setting up a Database and Tables

Before we can use PDO to interact with data, we need to create a database and structure our data using SQL tables.

Using phpMyAdmin

If you are using XAMPP/MAMP, you can manage your database through the web interface tool phpMyAdmin (accessible at http://localhost/phpmyadmin/).

Steps:

  1. Navigate to phpMyAdmin.
  2. Click the 'Databases' tab.
  3. Create a new database (e.g., user_db).

Creating a Table (SQL)

Tables require columns, each with a specific data type and constraints.

We will create a simple users table:

ColumnData TypeConstraint/Description
idINTPRIMARY KEY, AUTO_INCREMENT
usernameVARCHAR(50)NOT NULL, UNIQUE
emailVARCHAR(100)NOT NULL
created_atDATETIMETime of creation

SQL Query to Create the Table:

sql CREATE TABLE users ( id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Executing the SQL: You can run this command directly in the SQL tab of phpMyAdmin. Once the table is created, PHP can connect and perform CRUD (Create, Read, Update, Delete) operations.