MySQL — Connect, Create Database & Table

Use PDO or MySQLi to connect PHP to MySQL. PDO is portable and supports prepared statements on multiple databases.

Connect to MySQL

Use PDO or MySQLi to connect PHP to MySQL. PDO is portable and supports prepared statements on multiple databases.

Real-life example: Connecting to MySQL is plugging a phone charger into the wall — PHP gets power (data) from the database.

PDO connection

<?php
try {
  $pdo = new PDO(
    "mysql:host=localhost;dbname=knowvora;charset=utf8mb4",
    "root",
    "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
  );
  echo "Connected!";
} catch (PDOException $e) {
  die("Connection failed: " . $e->getMessage());
}
?>

Create Database

Run CREATE DATABASE in phpMyAdmin or from PHP after connecting without a dbname. Use utf8mb4 for full Unicode support.

Real-life example: A database is a filing cabinet. CREATE DATABASE builds a new cabinet for one project.

Create database with PDO

<?php
$pdo = new PDO("mysql:host=localhost", "root", "");
$pdo->exec("CREATE DATABASE IF NOT EXISTS knowvora CHARACTER SET utf8mb4");
$pdo->exec("USE knowvora");
?>

SQL — create database

CREATE DATABASE IF NOT EXISTS knowvora
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE knowvora;

Create Table

Tables store rows of data with columns and types. Define PRIMARY KEY, AUTO_INCREMENT, and NOT NULL where needed.

Real-life example: A table is a spreadsheet — columns are Name, Email, Score; each row is one student.

CREATE TABLE

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  score INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create table from PHP

<?php
$sql = "CREATE TABLE IF NOT EXISTS courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  level ENUM('beginner','intermediate','advanced') DEFAULT 'beginner'
)";
$pdo->exec($sql);
?>