PHP MySQL tutorial | create delete and modify tables

HacksLand | The computer science playground

Posted by Thilan Dissanayaka on Sep 05, 2019
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 PHP+MySQL example.

$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.

Hi, I'm Thilan. An engineering student from SriLanka. I love to code with Python, JavaScript PHP and C.

Also read

Sep 06
PHP Secure fie uploading

Hear I'm going to share a easy and secure way to upload your images and other files. You have to be....

Aug 12
IP Address Explained

In this tutorial I'm going to cover an important topic in networking. Yes it's internet protocol.....

May 04
C programming strings

In our "Manipulating data with C" article we saw how we can store data in memory. We used the....