Creating Database Table In PHP

In this article, we will learn to create a database table in a MySQL database through PHP script. Following are the steps that we will follow to create a database table:

1. Establishing connection with MySQL server
2. Creating a database (if it does not exists). If database already exists then skip this step
3. Selecting the database (in which table is to be created).
4. Writing SQL statement for creating a table
5. Executing the SQL statement
To understand above steps let us look at the following program that creates a table by name: products in shopping database. The products table that we are going to create will have five fields (columns): id, item_code, item_name, quantity and price. The fields: id, item_code and quantity are considered to be of int (integer) data type, item_name of character data type and price of float data type.
Coding for “Creating Database Table In PHP”
createtable_products.php
<?php
$user=”username”;
$password=”password”;
$connect = mysql_connect(“localhost”, $user, $password) or die (“Please check your server connection.”);
//create the database if it doesn’t already exist
$create = mysql_query(“CREATE DATABASE IF NOT EXISTS shopping”) or die(mysql_error());
mysql_select_db(“shopping”);
$sql = “CREATE TABLE products(
id int(6) NOT NULL auto_increment,
item_code int(4),
item_name varchar(50),
quantity int(4),
price float)”;
$res = mysql_query($sql) or die (mysql_error());
echo “products table successfully created!”;
?>
Above program has all the five steps of creating a table. In the first two lines, two variables $user and $password are initialized to the username and password of the MySQL server respectively. From the 3rd line onwards, we see that the five steps for creating a table are executed. Let us see look at each statement step by step:
1. Establishing connection with MySQL server
Before proceeding with creation of a database or a table, the first thing we need to connect with MySQL server. The connection is established with the help of a valid user name and password.
$connect = mysql_connect(“localhost”, $user, $password) or die (“Please check your server connection.”);
In above command localhost signifies that MySQL server is installed on the local machine. The string localhost will be replaced by the IP address of the server or server name (Example : sqlserver.com or 216.237.120.79 ) in case we try to connect with the remote server. The $user and $password variables contain the valid userid and password supplied by the administrator. The keyword die is for displaying error messages if any information supplied is wrong.
2. Creating a database
After we are connected to the MySQL server, we must then create the database (if it does not exists) followed by selecting it i.e. making it active in memory. The command for creating database is :
$create = mysql_query(“CREATE DATABASE IF NOT EXISTS shopping”) or die(mysql_error());
Above statement is creating a database by name “shopping” (in case it doesn’t already exists). If the database is already there, this command won’t do anything
3. Selecting the database
Selecting the database means loading the database in memory (i.e. making it active). The command for selecting a given database is :
mysql_select_db(“shopping”);
Above command is selecting the database: shopping. Now all SQL statements that are executed will be applied to the tables belonging to this (shopping) active database.
4. Writing SQL statement for creating the table
The below given SQL statement creates a table by name products that has five fields out of which id field is set to auto_increment i.e. its value  will automatically increase by 1 with every record inserted
$sql = “CREATE TABLE products(
id int(6) NOT NULL auto_increment,
item_code int(4),
item_name varchar(50),
quantity int(4),
price float)”;
The rest of the fields (columns) in the table are item_code that will store an integer values of almost 4 digits, item_name field will store the name of the item of up to 50 characters long and quantity field can store an integer value of up to 4 digits and price field can store a fractional value (value with decimal points)
5. Executing the SQL statement
Below given statement executes the above SQL statement of creating the products table
$res = mysql_query($sql) or die (mysql_error());
The $res variable is for storing the result of the execution of the SQL statement
For more information, refer my book: “Developing Web Applications in PHP and AJAX” available at: http://www.amazon.com/Developing-Web-Applications-PHP-AJAX/dp/0070707103/ref=ntt_at_ep_dpt_4

One thought on “Creating Database Table In PHP

  1. Hello my name is Sabrina Warren and I just wanted to send you a quick message here instead of calling you. I came to your Herstellen der Datenbank-Tabelle in PHP – bmharwani.com page and noticed you could have a lot more visitors. I have found that the key to running a successful website is making sure the visitors you are getting are interested in your website topic. There is a company that you can get keyword targeted traffic from and they let you try their service for free for 7 days. I managed to get over 300 targeted visitors to day to my website. http://ganaar.link/j

Leave a Reply

Your email address will not be published. Required fields are marked *