Creating Database and Tables

PHP and MySQL are the dynamic duo that makes up the backbone of many web applications. PHP is a powerful server-side scripting language, while MySQL is one of the most popular relational database management systems (RDBMS). Together, they can be used to create robust and dynamic websites and web applications.

In this article, we will focus on how to create databases and tables using PHP and MySQL. We'll start with the basics, explaining what databases and tables are, and then move on to the practical aspect of creating them.

What is a Database?

A database is an organized collection of data stored and accessed electronically. Databases are used to store various types of data, such as user information, posts, comments, and much more.

In the context of web applications, databases are used to persist data across different sessions and users.

What is a Table?

A table is the most basic unit of data storage in a relational database. A table is made up of columns (fields) and rows (records). Each column represents a specific attribute of the data, while each row represents a single record.

Creating a Database

Before we can start creating tables, we first need to create a database. The following PHP script demonstrates how to create a database using the MySQLi extension in PHP.

<?php
$servername = "localhost";
$username = "username";
$password = "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 myDatabase";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}

$conn->close();
?>

Creating a Table

Once we have a database, we can then create a table within that database. The following PHP script demonstrates how to create a table using the MySQLi extension in PHP.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

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

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

// Create table
$sql = "CREATE TABLE MyTable (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

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

$conn->close();
?>

In the above code, we first establish a connection to the database using the new mysqli() function. Then, we use the query() method to execute a SQL statement that creates a new table named MyTable with five columns: id, firstname, lastname, email, and reg_date.

That's it! You've just created a database and a table using PHP and MySQL. In the next steps, you can explore how to insert, retrieve, update, and delete data from this table. Remember, practice makes perfect. So, make sure to get your hands dirty with some code!