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.

Feb 15
Introduction to Assembly language

Assembly is a low level programming language. You already know that low level programming languages....

Aug 12
Format strings | Python programming

Format strings are used to format the printing output. We can print a string with variables in it.....

Jul 02
GDB reverse engineering tutorial

Today I selected an interesting topic to discuss. Here we are going to disassemble a binary file....

Replying to 's comment Cancel reply