PHP MySQL to create databases and tables

The database holds one or more tables.

Create a database

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

Syntax CREATE DATABASE database_name

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

example

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

if (! $ con) {die ('Could not connect:'. mysql_error ());} if (mysql_query ("CREATE DATABASE mysql_error ();} mysql_close ($ con);?> Create table ("my_db", $ con)) {echo "Database created";} else {echo "Error creating database:".

CREATE TABLE 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, .......)

In order to execute this command, I have to add a CREATE TABLE statement to the mysql_query () function.

example

The following example shows how to create a table named "person" with three columns. 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 person (FirstName varchar (15), LastName varchar (15), Age int)"; mysql_query ($ sql, $ con); mysql_close ($ con)

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

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

MySQL data type

The following MySQL data types are available:

Numeric Type Description int (size) smallint (size) tinyint (size) mediumint (size) bigint (size) Only integer support. Specify the maximum number in the size parameter. decimal (size, d) double (size, d) float (size, d)

Support for numbers with decimals.

Specify the maximum number in the size parameter. The d parameter specifies the maximum number of digits to the right of the decimal point.

Text Data Type Description char (size)

Support for fixed-length strings. (Can contain letters, numbers and special symbols).

Specify the fixed length in the size parameter.

varchar (size)

Support for variable-length strings. (Can contain letters, numbers and special symbols).

Specify the maximum size in the size parameter.

tinytext supports variable-length strings, with a maximum length of 255 characters. Text blobs support variable-length strings, with a maximum length of 65535 characters. mediumtext mediumblob Supports variable-length strings up to a maximum 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) Supported Date or Time Miscellaneous Data Type Description enum (value1, value2 , ect) ENUM is an abbreviation for the ENUMERATED list. You can store up to 65535 values ​​in parentheses. set SET Similar to ENUM. However, SET can have up to 64 list items and can hold 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 row in the table. Each primary key value must be unique in the table. In addition, the primary key field can not be empty, because the database engine needs a value to locate the record.

The primary key field will 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 for that key.

The following example sets the personID field as the primary key field. The primary key field is usually the ID number and is usually set using the AUTO_INCREMENT. AUTO_INCREMENT will increment the value of this field as new records are 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 person (personID int NOT NULL AUTO_INCREMENT, PRIMARY KEY (personID), FirstName varchar (15), LastName varchar (15), Age int)"; mysql_query ($ sql, $ con);