PHP MySQL Create Database
After establishing a connection to the MySQL server, the next step is to create a database. A database is a structured container that holds your tables and data.
1. The CREATE DATABASE Statement
In SQL, we use the CREATE DATABASE statement to create a new database. From PHP, we execute this command using the query() method.
2. Create Database using MySQLi (Object-Oriented)
In this example, we connect to the server (without specifying a database name yet) and then execute the SQL command.
<?php
$servername = "localhost";
$username = "root";
$password = "";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
echo "Database created successfully";
} else {
echo "Error creating database: " . $conn->error;
}
$conn->close();
?>
3. Create Database using PDO
PDO is excellent for error handling. If the database creation fails, it will throw an exception which we can catch.
<?php
$servername = "localhost";
$username = "root";
$password = "";
try {
$conn = new PDO("mysql:host=$servername", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE DATABASE myDBPDO";
// use exec() because no results are returned
$conn->exec($sql);
echo "Database created successfully<br>";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
4. Important Considerations
- Database Names: Avoid using spaces in database names. Use underscores (e.g.,
my_first_db).
- Permissions: Ensure your MySQL user has the
CREATE privilege. On a local XAMPP setup, the 'root' user usually has full permissions.
- Existence Check: To avoid errors if the database already exists, you can use
CREATE DATABASE IF NOT EXISTS myDB.
Tip: Once the database is created, you will usually add its name to your connection string in future scripts (e.g., new mysqli($server, $user, $pass, $dbname)) so you don't have to select it every time.