This is the second tutorial of our PHP+MySQL tutorial serious.In last tutorial we saw that how we can make a database connection to a SQL server. In this one we are going to see how we can make a new table with SQL Also we are going to learn to modify and delete a exiting table. So why we're waiting. Let's start it.Following is the pure SQL for make a table.
Hear I demonstrated to make a two column table. The command we use is CREATE TABLE . It expect column information inside the brackets. So what information about columns do you need to supply? . Let's imaging you need to make a table named users with two columns to hold user name and the age of the user. Then the user name column need to hold a text string and age column will hold an integer. If the column names are user_name and user_age we can use following SQL statement to make a table.
CREATE TABLE [table_name_goes_hear] ([column_name] [data_type], [column_name] [data_type] );
gHear we have used two data types called varchar and int. Yes you're correct. Varchar stands for a character string and int will hold a integer. In brackets like 'int(10)' We have put a number. It indicate how many space we want. For an example we say we need 10 integer space by writing it as int(10). In most times we use data types like varchar, text , int, datetime, boolean etc. We can see them in next examples.(Also in above example I warped our SQL statement to many lines [Just for clear presenting] . It's OK to write SQL like that way.)Now I want to introduce some other special column modifying arguments to you.If you think a column shouldn't have a NULL value you can use a special argument called 'NOT NULL'.
CREATE TABLE `users` (`user_name` varchar(100),`user_age` int(10); )
If you add an 'id' column to your table in most times it is unique for every row. We call it as a primary key. Also it's easy to maintain a table if the id column is auto incremented. So when every time we insert a new row id column will auto updated. Following SQL code will show how we can do it.(If you make a column auto increment you shouldn't insert data to it with PHP code.)
CREATE TABLE `users` (`user_name` varchar(100) NOT NULL,`user_age` int(10); );
Also you can set a default value for each column. For an example if you want to automatically insert current time for a column when we insert a new row you may use following statement.
CREATE TABLE `users` (`id` int(10) AUTO_INCREMENT PRIMARY KEY,`user_name` varchar(100),`user_age` int(10) );
Now you know how we can make tables with SQL. Let's take an actual PHP+MySQL example.
CREATE TABLE `users` (`id` int(10) AUTO_INCREMENT PRIMARY KEY,`user_name` varchar(100),`user_age` int(10),`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP);
Hear we used the PDO database object we created in a previous tutorial.
<?php $sql = "CREATE TABLE users (id INT(6) AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(100) NOT NULL, user_age INT(10) NOT NULL)"; $query = $db -> prepare($sql); $query->execute(); ?>