MySQL Create DB

PHP: Create a MySQL Database

A relational database like MySQL organizes data into tables. Before you can create tables and store data, you usually need to create the database itself.

Note: Creating or deleting databases typically requires specific administrative privileges (like CREATE) granted to your MySQL user account. Shared hosting environments might not allow direct database creation via script; you often use their control panel (like cPanel) instead.

Create a MySQL Database Using MySQLi and PDO

The SQL command CREATE DATABASE database_name is used to create a new database in MySQL.

PHP provides two primary extensions for interacting with MySQL: MySQLi (Improved) and PDO (PHP Data Objects). Both are actively maintained and recommended over the old, deprecated mysql_* functions. PDO offers a consistent interface across different database systems.

Connecting Without Selecting a Database:

To execute CREATE DATABASE, you need to connect to the MySQL server first, without specifying a particular database in the initial connection parameters.

Example using MySQLi (Object-Oriented Style):

<?php
// Database credentials (replace with your actual credentials)
$servername = "localhost";
$username = "your_username"; // Usually an admin/root user for CREATE DATABASE
$password = "your_password";
$new_dbname = "myNewDB_mysqli";

// Create connection to the MySQL server (no database specified initially)
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL to create database
$sql = "CREATE DATABASE " . $new_dbname;

if ($conn->query($sql) === TRUE) {
    echo "Database '" . htmlspecialchars($new_dbname) . "' created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}

// Close connection
$conn->close();
?>

	Note: When connecting just to create a database, omit the database name parameter in the mysqli constructor. If you need to specify a port other than the default (3306), you can add it as the fifth parameter: `new mysqli($servername, $username, $password, ”, $port);

Example using MySQLi (Procedural Style):

<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$new_dbname = "myNewDB_procedural";

// Create connection to the MySQL server
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// SQL to create database
$sql = "CREATE DATABASE " . $new_dbname;

if (mysqli_query($conn, $sql)) {
    echo "Database '" . htmlspecialchars($new_dbname) . "' created successfully";
} else {
    echo "Error creating database: " . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>

Example using PDO:

PDO uses Data Source Name (DSN) for connection. To create a database, connect without specifying dbname in the DSN.

<?php
// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$new_dbname = "myNewDB_pdo";

try {
    // Connect to MySQL server (no dbname in DSN)
    $conn = new PDO("mysql:host=$servername", $username, $password);

    // Set the PDO error mode to exception for better error handling
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL to create database
    $sql = "CREATE DATABASE " . $new_dbname;

    // Use exec() because no results are returned from CREATE DATABASE
    $conn->exec($sql);
    echo "Database '" . htmlspecialchars($new_dbname) . "' created successfully";

} catch(PDOException $e) {
    // Catch any errors during connection or execution
    die("Database creation failed: " . $e->getMessage());
}

// Close connection (optional in PDO, happens automatically when script ends)
$conn = null;
?>

Error Handling with PDO:

A significant advantage of PDO is its robust exception handling. By setting PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION, PDO will throw exceptions for database errors, which can be caught in a try...catch block. This leads to cleaner and more predictable error management compared to manually checking for errors after each operation.

Choosing Between MySQLi and PDO:

• MySQLi: Specific to MySQL. Offers both object-oriented and procedural interfaces. Might offer slightly better performance for MySQL-only applications due to its direct nature.
• PDO: Database-agnostic (supports MySQL, PostgreSQL, SQLite, etc.). Provides a consistent object-oriented API across different databases, making code more portable. Generally preferred for new projects aiming for flexibility or potentially supporting multiple database systems.