Sep 05, 2019

PHP MySQL tutorial | create delete and modify tables

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.

Nov 19
cookie consent banner javascript

Accordion to GDPR (General Data Protection Regulation) If we collect or save any kind of website....

Aug 20
Assembly basic tutorial - add two numbers

Welcome guys, today another basic tutorial on assembly coding. Hear I'll explain you how we can add....

Mar 08
How stack works in function call

The stack is an important concept in computer science. If you are planning to learn reverse....

Replying to 's comment Cancel reply
Thilan Danushka Dissanayaka

Thilan Dissanayaka

Hi, I'm Thilan from Srilanka. An undergraduate Engineering student of University of Ruhuna. I love to explorer things about CS, Hacking, Reverse engineering etc.