PHP Introduction Lecture #12

INTRODUCTION TO DATABASE

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

DATABASE

First of all let’s have some introduction of database after that we’ll be doing PHP-Database examples.

Database is the computerized collection of records stored in the form of rows and columns. Why computerized because in terms of manual file management system a computerized database is more easier in searching, managing, updating the records. The main purpose of database is not only to collect and organize data but allows the advanced retrieval of data and analysis.

To retrieve, update or insert the records from bunch of information from we use Database Query.

MySQL (Structured Query Language) is very popular, free, open source database that handles very large amount of databases. With very fast performance, easy to use with PHP and its Shell Prompt for creating and updating the records. This comes with your server whatever you have wamp/xampp. Here is how you can open the Shell Prompt of wamp server.

You just click on the green icon of wamp server, hover mouse at the MySQL option and click on MySQL console as shown in figure.

When you click on MySQL Console option another window will appear that will be asking for password, just leave it empty and press Enter. After pressing the Enter you will find another window like that

Now we are connected to MySQL. Here we can write our queries. Some of the short and practice based queries are listed below you must try this for your easiness in understanding and make command on coming up next queries.

i-                   SELECT VERSION(): Will show the latest version of your MySQL.

ii-                 SELECT CURRENT_DATE; Will show the current date with month and year.

iii-               SELECT NOW(): will show the current date and time.

You can also write mathematical expressions in MySQL console.

iv-               SELECT PI(): will return value of pi.

v-                 SELECT 5*4+2: will return the answer of expression

And so on…

Now come to our Database related MySQL queries. When you install any of server wamp/xampp there will be some of the built-in databases to list out those databases there is simple query that is `show databases` followed by semicolon and hit enter you will find all of your databases in your server. Remember to give semicolon after each query you enter otherwise the query will not work and MySQL will be forcing you to enter even more because terminator will be missing. When you see list of your databases and if you want to access any one of them you will write query `use database_name`. Let suppose you have a database named test then you should write `use test`. After writing then command you will be using test database. And if you want to see the tables in that database simple you write `show tables` you will be shown all the tables within that database. And not yet if you want to see columns and other information of any table you just write `desc table_name`. Let suppose in test database there is table named `user` then you will write `desc user` you will see all the columns and other information about that table. In last if you want to see the records of that tables you will write that query.

SELECT * FROM table_name

* means all. Above query says that display all the records from a table.

Let’s see pictorially what have been told in description.

First of all `show databases` command. These are all my databases that I have created are shown in figure, even some are missing will be shown in next picture.

After that `use database`, `show tables` and `desc table` commands are here shown in this image.

Field means Column. id, name, password, role_id are the columns of user table. Type means which kind of information is in this column or which type of information it can store. int means only integer type of data/numeric data. varchar means integer as well string information both can be stored and the number within curly braces tells the length of value. Null Yes/No says that value of that column could be null or not. If there is NO means value could not be Null or empty. Where there is Yes means value of that column can be Null or left empty. Remaining about Key and, default and Extra you will be told later, for now you should have basic knowledge of basic queries and their work.

And if we want to see the full record of any table as query is told above, here is the graphical representation of how record is displayed along with their values.

And if you want any particular record from this table not the whole record you write any column names separated by commas instead of * sign. Let’s have a query.

SELECT name, password FROM user;

Will display just name and password column values not the whole.

For today’s lecture I think this is enough for you, hope you got the concept about database, MySQL console and database queries. You must practice yourself for further your clearance.

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 topic continues to `Database`

Good Luck J