PhpMyAdmin

Screenshot1

Introduction to database article i had wrote about why we use it databases. Now we need to find easy way manage our database. Before we start it we will connect to our database then we can add and retrieve data. Screenshot1 is the Screenshot of phpmyadmin and its freely available piece of software it does comes with xampp If we go to http://localhost in  You can see it represented with xampp control panel and on that page you can see in left navigation down tools and you can see phpmyadmin so, If you just click on that you can see screen of phpmyadmin like screenshot1. If you don't had xampp you running something else then you can download phpmyadmin copy overview and set you database setting in there so your server in which you working like localhost and then everything will be manage for you.

Screenshot2

First Thing we need to think about is the server. We are connecting to Server so the server name we are connecting to at moment our server name is localhost and this is case format for most mysql host-names for example if i had paid hosting and i was uploading to shared hosting account for example usually i can specify localhost and it will connect straight to that. where ever i can connect to the another database on another server i always had to specify the server name itself.

Screenshot3

Lets, See the Phpmyadmin -> Privileges (Screenshot2) In Privileges you can see the list of users. In Screenshot2 you can see the user root is twice with host 127.0.0.1 and host localhost and Password no because i am running on my computer i had not connected to the internet then there is no need for me to setup password obviously it will be better to edit the password however for now i had not setup the password so, In example you can see usage i am not going to be using password the password will be blank So, Now we had established the username here is root which can be seen in privileges and you can also add new user. So, if you want to add the users (Screenshot3) it is bit more friendly so you can say any username, host better is to keep localhost then password you can also generate password for more security of your password. So, we had done that now obviously we already spoken about password which is blank in this tutorials. So, we had established the server , username and the password.


Server: localhost
username: root
password: NULL

Screenshot4

This all we need to use to connect to our database Now we not going looking at that now we are just seen how you can find this information. Now Lets Go to Screenshot1. left side you can see the databases that i had created Years and months ago just think i have worked in general So, You can see database name for example cart, quotes, Videos, this are databases name and to create a new database we you create new database in Screenshot1. So, i can create new database just called a_database and will click create now you can see screenshot4 a_database had created and you can see in screenshot4.

Screenshot5

Screenshot6

Now In Screenshot5 You can see at home page a_database had listed on left navigation. Now you notice a_database and other database had contain the number in brackets because cart(1), cdcol(1) this numbers are the table inserted to the database in Videos(1) so there will be one table exist on the database called videos you can see in Screenshot6. In database videos it contain 1 table videos. So, in brackets it contain 1. So In a_database there are no table so there is no bracket besides its and when u click on a_database you can see the left navigation contain a_database listed with brackets zero because no table exist we also get message saying No tables found in database. Now Its Fair to have database and inside this we need to store tables we can't directly store data inside into the database it has to contain tables.

Screenshot7

Introduction to database article i had shown you user's table and upload table. So, I am going ahead and create table called user's. Now number of fields is the number of columns which we had in listed in table. Before we create a table think about how many columns or fields like we may need. For Example i am creating a user login databases. the minimum we need id because we need to assign a unique id to be a user , username field, and password field that's only we need to create user login session's. So, Here number of field's in table user's we need 3 field's now i click go Screenshot4. Now what's its going to do its going to transform to this screenshot7. Where i can create each field's inside table. Its look's like lot's of information but you don't had to specify all this box's in Screenshot7. But there are some important part you will need to specify.

For field is the field name So, First one i am going to choose id, username, and last one is password, So, i had specified my field name inside the table. Now i need to specify the type of the data i want to store.  Now storing different type of  data is always like storing the variable with different type of data and You can see we have very extensive list of data like Int reference Integer we will discuss lot different types of data in later article.  Now id remember is the number which will auto-increment will give each row unique value and You can see how it will works in the moment. Now id i will choose type int because it is integer. You can also define TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, are all types of data which are numeric. SO, i am going to choose INT for this You can also choose something like SMALLINT, Medium Int, However if you expect 100 users on your website This is fine however you are taking to Billions of users then you need to stick with INT data types. So Length/values we don't need to worry about it. Now 2 fields we need to focous in id fields are index and Auto_Increment fields.

Now Remember I said the primary key is the field or column the unique column to specify the unique column. This is the unique number column. So, Index field we assign primary key to id field. And we tick to AUTO_INCREMENT and this allows us to every-time we create a record we are increment this value. So, it will start 0 will be default. Then you create record you have value 1 then 2 then 3 and so on.

Now Let's Come to Username and Password Now obviously we don't want to save the username and password in INT data types. We will assign the VARCHAR. Now VARCHAR stands for variable character. So, what we can do here is we must specify the length/values field So, username say no more then 30 character. So, we can specify this is 30. Password will be adjust the same it may be less then 30 Characters. But this is something you going to have specify in your program. Let say you have to create user login system. You have to say you know give max length to text field. And check inside php if the password specified the username specified Word not more then 30 because anything bigger then 30 attempting to be written into this table it will be cut off.

Default values is the values if the user does not inputted and it may be saved default if the field is leaved blank it will saved by default value. If we create stayloggedin column and this may be equal to 1 or 0 depending on  the user logged in. But by default it will be zero. If this make sense. Now we can come down and click on save.

Screenshot8

Now You can see in screenshot8. that we had created a table called users. And we got number 1 with a_database. which shows there is one table inside the a_database. So, You can see we got the information. You can see id underline which means its the primary key. And we got our type so you can see int is a length of 11. So, we can specify 11 digits of length in INT type. Its may be consider to much like i said if you got millions of users. You got VARCHAR 30. and password VARCHAR with 30 character. You can also see extra is defined auto_increment.

Screenshot9

Screenshot10

Now let see how to insert the data into this table because at the moment we got the table. But we don't got the data. Now this data you are going to inserting from the php. The form will check inside the php script of the username and password. However we can do manually from phpmyadmin if we wish to do so. So,i am going to click on insert Screenshot9. And id we can leave blank because remember its auto increment fields. We can specify left blank and it will automatically update. Now You can see the Screenshot10. We can also specify the rows of insertion we can also specify 40 rows for insertion for example. But Now I will Just Insert alex and Password and will click to go.

Screenshot11

Now You can see screenshot11 we have few things return here in Screenshot11. Firstly we see the conformation. We can see 1 row inserted which we have inserted. Inserted row id: 1 which is our auto-increment field. Now You can See In Screenshot11 the query had constructed for insertion of the data.


 INSERT INTO 'a_database'.'users' {
'id',
'username',
'password'
}
VALUES {
NULL, 'alex','password'
};

Earlier i had told you about query How they can insert data Here we got insert into a_database which is the name of database dot users which is table we are accessing a_database table which is users. id, username, password and then with the value Null, for id we are not specifying value for the field because its auto-incremental. alex for the username, and password field value is password. So, Null corresponds to id, alex corresponds to username, and password corresponds to password.

Screenshot12

Screenshot13

Screenshot12 you can see the representation of the data in our database. So let's insert another let's called this time billy  and password pass123. Now we had inserted another row with id 2 remember that's auto increment. We perform query but with different values. You can see in browse tab. Now we are adding another Columns say first name and last name. So, Let see how we can do this. In Structure tab You can see at down add certain amount of fields at the end of the table , at beginning of table and after specified fields in our table. So, in my case i will insert fields at the end of the table. You can see in screenshot13 I will add 2 fields. Then same as earlier i will specify the field , type , length/ values Like firstname, lastname in fields, VARCHAR in type and length values are 40.

Screenshot14

ALTER TABLE 'users' ADD 'firstname' VARCHAR (40) NOT NULL, ADD 'surname' VARCHAR (40) NOT NULL 

Here we are altering the users table with tow more fields firstname with varchar character 40 and not null it means it should contain some data. Surname varchar length 40 not null. So, this is our query to alter the table and add two fields at the end of the table. Now In Browse tab you can see the no data has been inserted to those fields so i will insert the data of the two existing fields in the table. For Updating the table in existing fields. It will Give us the query Screenshot15. So, every-time we perform query in phpmyadmin it usefully gives us the query back so  if your structure is clean to understand the concept of the queries and how to structure them and what they do and how they work Later on then its really good thing to do just play around like insert things, delete things, change things, and just note of the query.

Screenshot15

In this case what we will do we had updated the particular fields in Screenshot15. In this case we updated the billy garrett we had updated which id is 2 So our query is update database name a_database, table name users and we set fields name firstname equals billy and surname equals garrett where table name users id fields name value is 2. It will update the data of fields id where value is 2 it will insert the data into the fields of firstname and the lastname.

Screenshot16

You can see in screenshot16 our data has been updated successfully we had alter the fields and then update the fields. You can perform variety of operations inside phpmyadmin. Its extremely powerful tool. So, may be before you start using queries and thing like that inside php. Have a play around here and just understand how may sql is structured and its datatype. How to do things works. Then go over to the php scripting side of things. So, for now its just the quick instruction about phpmyadmin how to use it in ways we are talking about of tutorials there is also lot more functionality behind it. Its just brief introduction about phpmyadmin.