PHP Create MySQL Database

A database contains one or more tables.

Create Database

The CREATE DATABASE statement is used to create a database in MySQL.

Syntax

CREATE DATABASE database_name

To make PHP execute the above statement, we must use the mysql_query() function. This function is used to send queries or commands to a MySQL connection.

Example

In the following example, we create a database named "my_db":

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }
mysql_close($con);
?>

Create Table

CREATE TABLE is used to create a database table in MySQL.

Syntax

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
.......
)

To execute this command, I must add the CREATE TABLE statement to the mysql_query() function.

Example

The following example demonstrates how to create a table named "Persons" with three columns. The column names are "FirstName", "LastName", and "Age":

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
// Create database
if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }
// Create table in my_db database
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE Persons"　
(
FirstName varchar(15),
LastName varchar(15),
Age int
);
mysql_query($sql,$con);
mysql_close($con);
?>

Important note:Before creating a table, you must first select the database. Use the mysql_select_db() function to select the database.

Note:When you create a varchar type database field, you must specify the maximum length of the field, for example: varchar(15).

MySQL data types

The following available MySQL data types:

Numeric type Description
  • int(size)
  • smallint(size)
  • tinyint(size)
  • mediumint(size)
  • bigint(size)
 Supports integers only. Specify the maximum value of the number in the size parameter.
  • decimal(size,d)
  • double(size,d)
  • float(size,d)

Supports numbers with decimals.

Specify the maximum value of the number in the size parameter. Specify the maximum number of digits to the right of the decimal point in the d parameter.
Text data type Description
char(size)

Supports fixed-length strings. (Can include letters, numbers, and special symbols).

Specify a fixed length in the size parameter.
varchar(size)

Supports variable-length strings. (Can include letters, numbers, and special symbols).

Specify the maximum length in the size parameter.
tinytext Supports variable-length strings, with a maximum length of 255 characters.
  • text
  • blob
 Supports variable-length strings, with a maximum length of 65535 characters.
  • mediumtext
  • mediumblob
 Supports variable-length strings, with a maximum length of 16777215 characters.
  • longtext
  • longblob
 Supports variable-length strings, with a maximum length of 4294967295 characters.
Date data type Description
  • date(yyyy-mm-dd)
  • datetime(yyyy-mm-dd hh:mm:ss)
  • timestamp(yyyymmddhhmmss)
  • time(hh:mm:ss)
 Supports date or time
Miscellaneous Data Types Description
enum(value1,value2,ect) ENUM is the abbreviation for ENUMERATED list. Up to 65535 values can be placed in parentheses.
set SET is similar to ENUM. However, SET can have up to 64 list items and can store more than one choice

Primary Key and Auto Increment Fields

Each table should have a primary key field.

The primary key is used to uniquely identify the rows in the table. Each primary key value must be unique in the table. In addition, the primary key field cannot be empty because the database engine needs a value to locate the record.

The primary key field must always be indexed. There are no exceptions to this rule. You must index the primary key field so that the database engine can quickly locate the row with the given key value.

The following example sets the personID field as the primary key field. The primary key field is usually an ID number and is usually set with AUTO_INCREMENT. AUTO_INCREMENT will sequentially increase the value of this field when a new record is added. To ensure that the primary key field is not empty, we must add the NOT NULL setting to this field.

Example

$sql = "CREATE TABLE Persons"　
(
personID int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(personID),
FirstName varchar(15),
LastName varchar(15),
Age int
);
mysql_query($sql,$con);