PHP MySQL Create Database and Tables

The CREATE DATABASE and CREATE TABLE statement is used to create MySQL database and table respectively.

Creating Database and Tables Using PHP and MySQL

Now that you've understood how to open a connection to the MySQL database server. In this tutorial you will learn how to execute SQL query to create a database and tables.

Creating the MySQL Database

Since all tables are stored in a database, so first we have to create a database before creating tables. The CREATE DATABASE statement is used to create a database in MySQL.

Let's make a SQL query using the CREATE DATABASE statement, after that we will execute this SQL query through passing it to the mysqli_query() function to finally create our database. The following example creates a database named "demo".

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $link = mysqli_connect("localhost", "root", "");
  •  
  • // Check connection
  • if($link === false){
  •     die("ERROR: Could not connect. " . mysqli_connect_error());
  • }
  •  
  • // Attempt create database query execution
  • $sql = "CREATE DATABASE demo";
  • if(mysqli_query($link, $sql)){
  •     echo "Database demo created successfully";
  • } else{
  •     echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  • }
  •  
  • // Close connection
  • mysqli_close($link);
  • ?>

Adding Tables to MySQL Database

Since our database is created now it's time to add some tables to it. The CREATE TABLE statement is used to create a table in MySQL database.

So let's make a SQL query using the CREATE TABLE statement, after that we will execute this SQL query through passing it to the mysqli_query() function to finally create our table.

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $link = mysqli_connect("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($link === false){
  •     die("ERROR: Could not connect. " . mysqli_connect_error());
  • }
  •  
  • // Attempt create table query execution
  • $sql = "CREATE TABLE persons(person_id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name CHAR(30) NOT NULL, last_name CHAR(30) NOT NULL, email_address VARCHAR(50))";
  • if (mysqli_query($link, $sql)){
  •     echo "Table persons created successfully";
  • } else {
  •     echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  • }
  •  
  • // Close connection
  • mysqli_close($link);
  • ?>

The PHP code in the above example creates a table named persons which has four fields 'person_id', 'first_name', 'last_name' and 'email_address'. Notice that each field name is followed by a data type declaration; this declaration identifies what type of data the field can hold, whether string, numeric, temporal, or Boolean. MySQL supports a number of different data types, the most important ones are summarized below.

Field Type Description
INT A numeric type that can accept values in the range of -2147483648 to 2147483647
DECIMAL A numeric type with support for floating-point or decimal numbers
CHAR A string type with a maximum size of 255 characters and a fixed length
VARCHAR A string type with a maximum size of 255 characters and a variable length
TEXT A string type with a maximum size of 65535 characters
DATE A date field in the YYYY-MM-DD format
TIME A time field in the HH:MM:SS format
DATETIME A combined date/time type in the YYYY-MM-DD HH:MM:SS format

Not Null, Primary Key and Auto Increment Fields

There are a few additional modifiers that are specified after the fields in the preceding SQL statement like: NOT NULL, PRIMARY KEY, AUTO_INCREMENT. It has the following meaning

  • The NOT NULL modifier definition specifies that the field cannot accept a NULL value.
  • The PRIMARY KEY modifier marks the corresponding field as the table's primary key which is used to uniquely identify the rows in a table. Each table in a relational database should have a primary key field.
  • The AUTO_INCREMENT modifier tells MySQL to automatically generate a value for this field every time a new record is inserted into the table, by incrementing the previous value by 1. Only available for numeric fields.

In the upcoming chapters you will learn how to insert new records as well as how to update, delete and view the existing records of persons table inside the demo database.

 
Close

Your Feedback:

 

We would love to hear from you! Please say something.