Create databases and tables for MySQL in PHP

Create databases and tables for MySQL in PHP

The database has one or more tables.

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

Syntax

CREATE DATABASE database_name

For more information about SQL, see our SQL tutorial.
To allow PHP to execute the preceding Declaration, we must use the mysql_query () function. This function is a MySQL connection used to send queries or commands.

For example
The following example creates a database called "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 a table
The create table declaration is used to create a table in MySQL.

Syntax
 
CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name3 data_type,....)
 
For more information about SQL, see our SQL tutorial.

We must add the create table declaration, mysql_query () function to execute the command.

For example
In the following example, a table named "person" has three columns. The column name will be "name", "Last Name", and "time ":
 
<?php$con = mysql_connect("localhost","peter","abc123");if (!$con)  {  die('Could not connect: ' . mysql_error());  }
// Create databaseif (mysql_query("CREATE DATABASE my_db",$con))  {  echo "Database created";  }else  {  echo "Error creating database: " . mysql_error();  }
// Create tablemysql_select_db("my_db", $con);$sql = "CREATE TABLE Persons(FirstName varchar(15),LastName varchar(15),Age int)";
// Execute querymysql_query($sql,$con);
mysql_close($con);?>
 
Note: You can create a table before selecting a table for the database. This database is selected with the mysql_select_db () function.

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

The data type specifies the type of data columns that can be shelved. For MySQL provided by a complete reference of all data types, please give us a complete data type reference.



--------------------------------------------------------------------------------

Primary Key and auto increment Fields
Each table should have a primary key field.

The key is to uniquely identify a column in a table. Each primary key value must be a unique table. In addition, the primary key field cannot be blank because the database engine requires a Value Location record.

The following example sets the personal ID field as the main key field. The key field is often a serial number and the AUTO_INCREMENT setting is often used. AUTO_INCREMENT automatically adds value to field 1 each time a new record is added. To ensure that the primary key field cannot be empty, we must add a non-null field to the field.

For 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);