PHP Introduction - Lecture #15

INTRODUCTION TO DATABASE Contd.

Hello All... welcome every one. I'm going to start PHP lectures for Very Beginners, who wants to learn PHP from very beginning. No matter if you do not have any basic programming concept. If you follow my tutorials I hope you will surely get some how good knowledge of PHP.

Today our Topic is

CREATING AND ALTERING DATABASE

Till now whatever we have we have performed operation were on the built-in databases. Today we will create our own database and perform operations on our own made database. To create database we have a command CREATE DATABASE after that write name of database. Just simple here is an example:

CREATE DATABASE school;

This will create database named school. Further to create tables and place columns in this table first write CREATE TABLE command followed by name of table start opening braces inside these braces write the column name along with its datatype. As written below here is the query to create tables.

CREATE TABLE students(

student_id INT NOT NULL);

NOT NULL is an optional. If you want that a particular column should not be left empty then you are required to write NOT NULL. This query will create a table namely students, with column student_id. Now if you want to see structure of this table you can write desc query.

desc students;

You will find the structure of table students. And if you want to add multiple columns you can use `ALTER` command. Let’s have look:

ALTER TABLE students

ADD student_name VARCHAR (255);

By writing this query in MySQL console and hit enter means you are going to add a column namely student_name that is of varchar type and its length is 255 characters. And again if you `desc students` the same table you will find an extra column that would surely be added in that table. And if you want to add multiple columns at a time just place comma ` , ` before the end of braces and start another column. here is an example.

CREATE TABLE students(

student_id INT NOT NULL,

student_name VARCHAR (255) NOT NULL,

student_address VARCHAR (255) NOT NULL,

student_contact VARCHAR (255) NOT NULL);

If you want to add another table in the same database you follow the same steps of creating a table.

And if you want to delete any of those columns here is the query

ALTER TABLE students

DROP COLUMN address;

This will drop / delete the column address.

So if you want to drop the table instead of single column use DROP TABLE and table_name. Eg. DROP TABLE students

It will delete the whole table all the data/columns inside that table.

To delete the whole database similarly you can use DROP command followed by database name. Eg. DROP DATABASE school

This will delete the database, inside that database whole tables, inside that tables the whole columns and all the record inside that columns. Try to avoid of using the DROP DATABASE command because it can surely cause some serious issue if you have any critical database. If you want to use it then best approach is to create another database where you can apply all your operations for your testing purpose.

Apply Primary key constraint to the column. Here is an example

ALTER TABLE students

ADD PRIMARY KEY (student_id);

Through this query Primary key constraint will be applied to the student_id column. Remember to apply PK constraint only to that column that cannot be the same just like id, cnic number, passport number etc.. No two columns can be Primary Key from a single table and no two records of Primary Key column can be same.

Now I’m going to insert records in our table.

INSERT INTO students(student_id, student_name) VALUES (01, “John Smith”);

Description of above query:

`INSERT INTO` is a keyword and compulsory for inserting any record to any table after that give table name start braces and start writing column names. It’s compulsory to write as it the spelling and sequence of columns of table as described in table. After that write `VALUES` keyword means these are the values that to be stored in table. Same here the values should be according to the sequence, means first column is student_id and that is INT means in first column only numeric value will be stored, and so on.

And if you want to insert multiple values at a time just little extension in query.

INSERT INTO students(student_id, student_name) VALUES (02, “Catherine Mills”), (03, “Kate Williamsons”); 

and hit enter you will find two records have been inserted at a time, if more than two records instead of semi colon put comma and start braces and in last put semi colon.

Further learning about MySQL. If you mistakenly filled the wrong information you can update that record even after it’s saved in database. Here is the syntax of query:

UPDATE table_name

SET column_name = “VALUE”

WHERE column_name = condition

UPDATE students

SET student_name = “Jack Smith”

WHERE student_id = 1;

It’s very important to apply where condition in update query, applying where condition means pointing out a particular record from set of records. This query says that UPDATE students table and SET student_name to “Jack Smith” whose student_id is 1.

Caution: If you don’t use where condition student_name of all records will be saved to “Jack Smith” and once a query is executed it will never be undo again so it’s mandatory to apply where condition while updating a record.

Thanks Guys. Any thing missing or any question from this topic you can comment below, I will be trying to solve and answer your question.

Our next topic will be `PHP-Forms`

Good Luck J