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.
CREATE TABLE [table_name_goes_hear] ([column_name] [data_type], [column_name] [data_type] );
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 `users` (`user_name` varchar(100),`user_age` int(10); )
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) NOT NULL,`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` (`id` int(10) AUTO_INCREMENT PRIMARY KEY,`user_name` varchar(100),`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),`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP);
Now you know how we can make tables with SQL. Let's take an actual
$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);
Hear we used the PDO database object we created in a previous tutorial.