In this section we are going to learn about a PHPmyAdmin tool. It is used to deal with MYSQL database. We can create databases, tables, etc. using this tool.
- If you have installed xampp server, you already have it in your PC. Since our ultimate target is to use MYSQL database, just go to Xampp Control Panel in C:\xampp folder and see if MYSQL is running. If it is running, you will see a button with caption Stop in front of MYSQL as shown in the figure below.
- If it is not running you just have to click on the Start button and it will start running.
- If MYSQL is running, go to the browser and type localhost/phpmyadmin in the address bar. The following page will appear:
- Put the username as root and the password that you might have set while installing xampp server.
- The page will look like this:
- Now click on Go button.
- A database interface will appear as shown below:
- All the databases are listed at the left hand side of the page.
Now let us learn to create a database and tables in it.
Steps are as follows:
- Creating a database:
- To create a new database click on the Databases tab shown in the figure below:
- The following page will open:
- Put the database name company in the box placed below Create database text and then click on Create button.
- Now your database has been created successfully and has been added to the left side list of databases.
- Just click on company at the left side and your empty database will open. Meaning of empty database is that, yet you don’t have any tables in it.
- The empty database will look like this:
- Creating tables in company database:
- Write table name employees in the box in front of text Name and provide 5 number of columns as shown below:
- Now click on Go button.
- The following page will occur in which we have to enter the column name, datatype that it will store and length/value.
- You have been given space to enter 5 column names as been specified before.
- While assigning column names to a table, most of the times you should have id as the first column of any database with datatype INT. This id should be unique and it’s better if it is auto-incremented.
- Auto-increment means a user don’t have to give an id to a new record, it automatically gets the next id.
- The properties that we have assigned to the columns of table employees are shown in the table below:
- In the above table Name indicates the column name, Type indicates the datatype value the column will store in it, Length/Values indicate the no. of characters the column can store (Length is not mandatory for INT type).
- As we know that id should be unique select PRIMARY for the Index property and select the checkbox for the A_I property so that auto-increment is applied to id (in the above table tick indicates selecting the checkbox).
- You need to scroll horizontally to give index and A_I properties.
- The datatype INT will store only the integer numbers and the datatype VARCHAR is capable of storing any characters, numbers and symbols .
- The page with the column details is shown below:
- Now after providing everything click on Save button.
- You will see your employees table created.
- Click on employees and then click on Structure, you will see all the columns created i.e. in short you will see the whole structure of the table as shown below:
- So now let us create 2 more tables on the same line.
- First create products table with the same procedure.
- Number of columns for products table is 3.
- The properties assigned to the columns of table products are shown in the table below:
- After providing the details with the same procedure stated for employees table click on Save button below.
- Now let us create categories table with the same procedure.
- Number of columns for categories table is 2.
- The properties assigned to the columns of table categories are shown in the table below:
- After providing the details with the same procedure stated for employees table click on Save button below.
- Thus we finished creating our 3 tables viz. employees, products and categories.
- Populate the tables with data:
- We can add data to tables using different ways such as manually adding data to the tables using PHPmyAdmin itself, through PHP page, etc.
- In this tutorial we will learn to add data manually using PHPmyAdmin.
- So, follow the steps:
- Click on the table in which you want to add data. for eg employees table.
- Now click on the Insert tab as shown below:
- A page containing column names and empty boxes in front of them will appear.
- Provide values in the empty boxes as shown below:
- You can see in the figure that we have not provided value for id. Since it is auto-incremented, no need to provide id.
- Now click on Go button. The values are inserted successfully and a message saying 1 row inserted appears.
- To view the table click on the Browse tab shown in figure below:
- It will show you the table employees with one record inserted in it as shown below:
- Running SQL queries in PHPmyAdmin tool:
- Inserting manually through PHPmyAdmin is very easy but we would not want to everytime login in PHPmyAdmin and insert data manually.
- Solution to this is inserting data using SQL queries through PHP.
- So let us learn some SQL queries.
- We can write and run SQL queries in PHPmyAdmin tool also.
- Steps are very simple:
- Click on company database placed at the top.
- Then click on SQL.
- The following page will open in which you can write and execute SQL queries.
- Let us write different queries, execute it and see its output:
- INSERT query:
- Write the following query in the white space:
- Then click on Go button.
- A message saying 1 row inserted will appear.
- Since in the above INSERT query we are not providing value for id column, it is compulsory to specify the names of the columns in which we want to insert the values.
- If we want to view the table just click on the company database placed at the top, then on the employees table and now click on the Browse tab. The employees table with 2 records will be shown as in the figure below:
- Inserting multiple records at a time:
- You can insert number of records through a single insert query.
- Write the following SQL query in the white space for SQL queries.
- Now click on Go button.
- Now just Browse the employees table as told above and see the records added to it.
- Each record in the query is separated by comma.
- It is as shown in the following figure:
- Changing some record values in the table:
- To change some value in a row after the record has been added we use UPDATE query.
- The record is updated by first searching it in the table using some unique value and then with respect to this unique value the record is updated.
- In the employees table this unique value is id of the employee.
- So click on the company database above and then on the SQL.
- We will try to change the department of Bob from Programming to Marketing and his email-id from bob @yahoo.com to [email protected] in the employees table.
- Write the following update query in the white space that appears:
- Now click on Go button.
- Now browse the employees table, you will find the record changed.
- It is shown in the following figure, a red colour arrow is pointing the updated record:
- Displaying table data:
- To display the data stored in the table we use SELECT query.
- Let us display all the records from employees table.
- So click on company database above and then on SQL.
- Write the following query in the white space that appears:
- Now click on Go button.
- All the records from employees table will be displayed.
- The asterisk (*) in the query indicates all.
- The output is shown below:
- Displaying data based on some criterion:
- We can select and display anything we want from the table based on some criterion.
- Let us select only first and last names from employees table.
- So click on company above and the on SQL.
- Write the following query in the white space.
- Now click on GO button.
- The first names and last names of all the employees are displayed.
- The first_name and last_name in the select query are the column names of the employees table, so both the columns are selected and displayed.
- The output is shown below:
- Using WHERE clause in SELECT query:
- WHERE clause is used to select all the rows that contain the specified value for a particular column.
- Let us select all employees from Marketing department.
- So click on company above and the on SQL.
- Write the following query in the white space.
- Now click on Go button.
- This will display all the employees those are from ‘Marketing’ department.
- The output is shown below:
- We can also use operators like AND (&&) and OR (||) in the query.
- To demonstrate this let us display employee records that are from Design or Programming department.
- So click on company above and the on SQL.
- Write the following query in the white space.
- Now click on Go button.
- This will display all the employees those are from ‘Design’ and ‘Programming’ departments.
- The output is shown below:
- In the same way you can use AND (&&) also.
- The difference between AND (&&) and OR (||) with respect to above query is that AND will display those employee records that contain both Design and Programming departments and OR will display those records that contain either Design or Programming departments.
- Deleting a record from the table:
- To delete a record from the table we use DELETE query.
- Imagine that the employee Tim Silver is getting fired. So now he no more an employee and hence we want to delete his record from our employees table.
- First browse the employees table and see the id of Tim Silver, it is 5.
- So now click on company above and the on SQL.
- Write the following query in the white space.
- Now click on Go button.
- This will delete the record with id=5 from the employees table.
- Delete query also require a unique value like update query to perform a task.
- Browse the employees table, you will find record with id 5 deleted.
- It is shown below:
- You can delete all the records at a time by using the following query:
- We actually don’t have more records in products and categories tables.
- So let us insert some records in categories table.
- We will insert values in the categories table manually.
- No need to put category id because it is auto-number.
- So insert categories Electronics and Furniture in the categories table.
- Now let us insert some records in products table.
- You can insert manually or through query.
- Let us insert a record in products table using insert query.
- So now click on company above and the on SQL.
- Write the following query in the white space.
- Now click on Go button.
- The record will be inserted into products table.
- Browse and see products table.
- Now insert some more values manually.
- No need to insert category id because it is auto-number.
- The records to be inserted are listed below:
- We have given category as 1 and 2 because in categories table Electronics has id 1 and Furniture has id 2.
- Now just browse categories table and see the content of table as shown below:
- Then now browse products table and see the content of table as shown below:
- Demonstration of LEFT JOIN:
- We want to display all products from Electronics category.
- We need output to be displayed in the form of product name and its category ‘Electronics’ next to it.
- But we know that we can get product name from products table and category name from categories table.
- Both the tables are related to each other through category id because the primary key of categories table i.e. id is a foreign key in the products table.
- So to take required data/columns from both the tables that have some relation in them, we can use JOIN.
- So now click on company above and the on SQL.
- Write the following query in the white space.
- Now click on Go button.
- You will see the product name column and category name column in the output as shown below:
- In the query above the keyword LEFT JOIN allows the control to fetch all records from table on its left side (first table) i.e. products and fetch only the matched records from the table on its right side (second table) i.e. categories.
Name | Type | Length/Values | Index | A_I |
---|---|---|---|---|
id | INT | 11 | PRIMARY | tick |
first_name | VARCHAR | 255 | ||
last_name | VARCHAR | 255 | ||
department | VARCHAR | 255 | ||
VARCHAR | 255 |
table 2.1
Name | Type | Length/Values | Index | A_I |
---|---|---|---|---|
id | INT | 11 | PRIMARY | tick |
name | VARCHAR | 255 | ||
category | INT | 11 |
table 2.2
Name | Type | Length/Values | Index | A_I |
---|---|---|---|---|
id | INT | 11 | PRIMARY | tick |
name | VARCHAR | 255 |
table 2.3
insert into employees(first_name,last_name,department,email) values (‘Fred’,’Thompson’,’Design’,’[email protected]’)
insert into employees(first_name,last_name,department,email) values (‘Bob’,’Smith’,’Programming’,’[email protected]’), (‘Sarah’,’White’,Marketting’,’[email protected]’), (‘Tim’,’Silver’,’Programming’,’[email protected]’), (‘Leah’,’Thompson’,’ Marketting’,’[email protected]’)
update employees set department=’Marketing’,email=’[email protected]’ where id=3
select * from employees
select first_name,last_name from employees
select * from employees where department=’Marketing’
select * from employees where department =’Design’ || department =’Programming’
delete from employees where id=5
delete * from employees
insert into products(name,category) values (‘iPod’,1)
Sr.No. | name | category |
---|---|---|
1 | Home Audio System | 1 |
2 | Sony PS3 | 1 |
3 | End Table | 2 |
4 | Leather Couch | 2 |
table 4.1
SELECT products.name,categories.name AS ‘category’ from products LEFT JOIN categories ON products.category=category.id
Thus we learned to use PHPmyAdmin tool and the SQL queries from this PHPMYADMIN and SQL Queries tutorial.