PHP : Create a MySQL Database

To create a MySQL database using PHP, you typically don't create the database directly in your PHP script. Instead, you use a database management tool such as phpMyAdmin or MySQL Workbench to create the database. However, you can write PHP code to run SQL queries that create tables within an existing database. Here's an example:

Connecting to MySQL:

First, you need to establish a connection to your MySQL server. You can use either mysqli or PDO for this purpose. Here's an example using mysqli :

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

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

Creating a Table:

Now, let's create a table within the database. This example will create a table named users with three columns: id, name, and email. The id column will be an auto-increment primary key.

Using mysqli :

// SQL query to create a table
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
)";

// Execute the query
if ($conn->query($sql) === TRUE) {
    echo "Table 'users' created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}
 

Using PDO :

// SQL query to create a table
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
)";

// Prepare and execute the query
try {
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    echo "Table 'users' created successfully";
} catch (PDOException $e) {
    echo "Error creating table: " . $e->getMessage();
}
 

Complete Example:

Here's a complete example that connects to MySQL and creates the users table:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

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

// SQL query to create a table
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
)";

// Execute the query
if ($conn->query($sql) === TRUE) {
    echo "Table 'users' created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

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

Important Notes:

• Replace "your_username", "your_password", and "your_database" with your actual database credentials.
• This script will create the users table if it does not already exist. The IF NOT EXISTS clause ensures that the table is created only if it doesn't already exist.
• The id column is defined as an auto-increment primary key, which means it will automatically assign a unique value to each row.
• Thename and email columns are defined as VARCHAR with a maximum length of 50 characters and cannot be NULL.
• Always sanitize user input and use prepared statements to prevent SQL injection attacks.

Running this script will create the `users` table in your specified database. If the table already exists, it will not be recreated, ensuring that existing data is preserved.