How to Create a MySQL Table Using PHP

In this tutorial, we show how to create a MySQL table using PHP.

PHP, as you probably know, can work very well in conjunction with MySQL.

We use PHP to write MySQL queries to the database.

MySQL is the language of communicating and accessing databases.

PHP, in conjunction with it, can write MySQL queries so that we can create a table.

A table can be created in MySQL using the following code shown below.

<?php $user = "user"; $password = "password"; $host = "host"; $dbase = "database"; // Connection to DBase mysql_connect($host,$user,$password); @mysql_select_db($dbase) or die("Unable to select database"); $connection= mysql_connect($host,$user,$password); $createtable= "CREATE TABLE customers ( ". "ID INT NOT NULL AUTO_INCREMENT, ". "firstname VARCHAR(50) NOT NULL, ". "lastname VARCHAR(50) NOT NULL, ". "state CHAR(2) NOT NULL, ". "PRIMARY KEY (ID) ); "; $create= mysql_query($createtable, $connection); if ($create) { echo "Table created successfully"; } else { die ("Table unsuccessfully created"); } ?>

So the following code shown above creates a table named customers.

First, we must connect to a MySQL database. In order to do so, we need a user name, password, hosting name, and database name.

We then use the mysql_connect() function to connect to the database server.

Using the @mysql_select_db() function, we can specify which database on the server we want to connect to.

We then create a $connection variable, which we'll use below to create a table that we want on the database we selected.

We then create a variable called $createtable. This variable creates a table named customers.

As most MySQL tables have (should have) is a column called ID. This ID serves as the primary key for the table and it is set to auto increment, because we want each value to be unique and we want sequential order.

Next, we create a variable named firstname, which is meant to store people's first name. I declared this to be of VARCHAR(50) type, which means it can be a string of variable length up to 50 characters. Usually people's first or last name doesn't exist 50 characters, but if you feel it would, you can make this longer. If you feel 50 is excessive, you can make it shorter. The value of making it shorter is that less memory is used. But for the average user, memory is probably not a big deal.

Next, we create a variable named lastname, which is meant to store people's last name. This also is of type VARCHAR(50).

We then create a variable called state, which is meant to store the acronymn of the state that the user lives in, such as NY (for New York), NJ (for New Jersey), PA (for Pennsylvania), etc. Because the abbreviation for each state is 2 characters, we can declare it to be of type CHAR(2), which is a string with a fixed length, in this case of 2 characters.

In the next line, we declare the ID column to be the primary column. It is desired to make a column primary when creating a table.

So we've created the query to create the following table which we have. But it isn't executed until later in the code. So, thus far, we've set up the framework to create the table, but haven't actually executed the table creation yet.

We then create a variable named $create, which creates the query to create the table. The mysql_query() function takes the $createtable variable and the $connection variable, which specifies the chosen database, and creates the table.

And with the following code, we now execute the code.

The if statement, if ($create), echos out the "Table created successfully" if it has been. Or "Table unsuccessfully created" if it hasn't been.

The value held in the $create variable from the mysql_query() function will either hold true or false. If true, the table has been created. If false, it has not been.

And this is all that is required to create a MySQL table using PHP.

Related Resources

How to Select a Table from a MySQL Database

How to Check if a MySQL Table Exists Using PHP

How to Show All Tables of a MySQL Database Using PHP

How to Delete a MySQL Table Using PHP

How to Rename a MySQL Table Using PHP

How to Add a Column to a MySQL Table Using PHP

How to Add a Primary Key to a Column of a MySQL Table Using PHP

How to Make a Column of a MySQL Table Unique Using PHP

How to Rename a Column of a MySQL Table Using PHP

How to Delete a Column of a MySQL Table Using PHP

How to Set the Default Value of a Column of a MySQL Table Using PHP

How to Drop the Default Value of a Column Of a MySQL Table Using PHP

How to Modify the Defintion or Data Type of a Column of a MySQL Table Using PHP

How to Insert Data into a MySQL Table Using PHP

How to Delete a Row of a MySQL Table Using PHP

How to Delete All Rows of a MySQL Table Using PHP

How to Insert Data Into a Table of a MySQL Database From an HTML Form Using PHP

How to Update a Value in a MySQL Table Using PHP