Question Answer

How to Create and Select a Database in PHP (MySQL)

This guide shows how to create a MySQL database and select it in PHP using both mysqli and PDO. It is easy, beginner-friendly, and suitable for B.Tech CSE web technologies students.

Prerequisites

• PHP installed with MySQL support
• MySQL Server running (username, password, host, and port)
• User must have permission to CREATE DATABASE

Method 1: Using mysqli (Procedural)

Step-by-step: connect to MySQL server, create database, and select it.

// 1) Connect to MySQL server (no database yet)
$host = "localhost";
$user = "root";
$pass = ""; // change as needed

$conn = mysqli_connect($host, $user, $pass);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// 2) Create database (use IF NOT EXISTS and utf8mb4)
$dbName = "college_app";
$sql = "CREATE DATABASE IF NOT EXISTS `$dbName` 
        CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci";

if (mysqli_query($conn, $sql)) {
    echo "Database ready.
";
} else {
    die("Error creating database: " . mysqli_error($conn));
}

// 3) Select the database
if (mysqli_select_db($conn, $dbName)) {
    echo "Database selected.
";
} else {
    die("Cannot select database: " . mysqli_error($conn));
}

// 4) Optional: verify by creating a test table
$createTable = "CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
)";

if (mysqli_query($conn, $createTable)) {
    echo "Table check OK.";
} else {
    echo "Table creation error: " . mysqli_error($conn);
}

mysqli_close($conn);

Method 2: Using mysqli (Object-Oriented)

$host = "localhost";
$user = "root";
$pass = "";
$dbName = "college_app";

$mysqli = new mysqli($host, $user, $pass);
if ($mysqli->connect_error) {
    die("Connect error: " . $mysqli->connect_error);
}

$mysqli->query("CREATE DATABASE IF NOT EXISTS `$dbName` 
                CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");

$mysqli->select_db($dbName);

// Quick check
$result = $mysqli->query("SELECT DATABASE() AS current_db");
$row = $result->fetch_assoc();
echo "Using DB: " . $row["current_db"];

$mysqli->close();

Method 3: Using PDO (Recommended for flexibility)

PDO provides a consistent API and supports exceptions for error handling.

$host = "localhost";
$user = "root";
$pass = "";
$dbName = "college_app";

try {
    // 1) Connect without a default DB to create it
    $pdo = new PDO("mysql:host=$host;charset=utf8mb4", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);

    // 2) Create database
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$dbName` 
                CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");
    echo "Database ready.\n";

    // 3) Reconnect selecting the database
    $pdo = new PDO("mysql:host=$host;dbname=$dbName;charset=utf8mb4", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
    echo "Database selected.\n";

    // 4) Optional: verify selection
    $stmt = $pdo->query("SELECT DATABASE() AS current_db");
    echo "Using DB: " . $stmt->fetch(PDO::FETCH_ASSOC)["current_db"];

} catch (PDOException $e) {
    die("DB error: " . $e->getMessage());
}

Quick Summary

1. Connect to MySQL server without a database.
2. Run CREATE DATABASE IF NOT EXISTS dbname CHARACTER SET utf8mb4.
3. Select the database using mysqli_select_db, $mysqli->select_db, or by reconnecting with dbname in the DSN.
4. Run a simple query (SELECT DATABASE() or create a test table) to confirm.

Common Tips and Troubleshooting

• If you get permission errors, ask your DBA to grant CREATE and ALL privileges for that user.
• Always use utf8mb4 to fully support emojis and multilingual text.
• For production, store DB credentials in environment variables, not in code.
• Check port and host (e.g., 3306 for MySQL, or 127.0.0.1 vs localhost) if connection fails.

Exam-Friendly Points (8 Marks)

• State: Connect → Create DB → Select DB → Verify.
• Mention mysqli and PDO methods with a short code snippet.
• Highlight utf8mb4 charset and IF NOT EXISTS.
• Include error handling (die/try-catch) and privileges note.