PHPMYADMIN and SQL Queries

0
6526

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.
  • xampp_control_panel
    fig 1

  • 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:
  • phpmyadmin_page
    fig 2

  • Put the username as root and the password that you might have set while installing xampp server.
  • The page will look like this:
  • phpmyadmin_page_with_unm_&_pwd
    fig 3

  • Now click on Go button.
  • A database interface will appear as shown below:
  • phpmyadmin_interface
    fig 4

  • 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:

  1. Creating a database:
    • To create a new database click on the Databases tab shown in the figure below:
    • phpmyadmin_interface_Databases_tab
      fig 5

    • The following page will open:
    • page_asking_for_database_name
      fig 6

    • 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:
    • empty_company_database
      fig 7

  2. 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:
    • entering_table_name_&_col_no
      fig 8

    • 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.
    • page_to_fill_col_names
      fig 9

    • 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:
    • Name Type Length/Values Index A_I
      id INT 11 PRIMARY tick
      first_name VARCHAR 255
      last_name VARCHAR 255
      department VARCHAR 255
      email VARCHAR 255

      table 2.1

    • 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:
    • filled_column_names_for_employees_table
      fig 10

    • 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:
    • employees_table_structure
      fig 11

    • 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:
    • Name Type Length/Values Index A_I
      id INT 11 PRIMARY tick
      name VARCHAR 255
      category INT 11

      table 2.2

    • 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:
    • Name Type Length/Values Index A_I
      id INT 11 PRIMARY tick
      name VARCHAR 255

      table 2.3

    • 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.
  3. 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:
      • fig_pointing_Insert_tab
        fig 12

      • A page containing column names and empty boxes in front of them will appear.
      • Provide values in the empty boxes as shown below:
      • manually_providing_values_to_table
        fig 13

      • 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:
      • fig_pointing_Browse_tab
        fig 14

      • It will show you the table employees with one record inserted in it as shown below:
      • one_record_from_employees_table
        fig 15

  4. 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.
      • SQL_page
        fig 16

    • Let us write different queries, execute it and see its output:
      1. 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:
        • records_in_employees_table_after_1st_insert_query
          fig 17

      2. 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:
        • six_records_in_employees_table
          fig 18

      3. 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 bobsmith@yahoo.com 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:
        • updated_record_3_of_employees_table
          fig 19

      4. 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:
        • select_all_from_employees_query_output
          fig 20

      5. 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:
        • fistname_lastname_from_employees_output
          fig 21

      6. 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:
        • select_all_Marketing_dept_emp_output
          fig 22

        • 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:
        • select_where_dept_design_or_prog_output
          fig 23

        • 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.
      7. 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:
        • deleted_id_5_from_employees
          fig 24

        • 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:
        • Sr.No. name category
          1 Home Audio System 1
          2 Sony PS3 1
          3 End Table 2
          4 Leather Couch 2

          table 4.1

        • 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:
        • categories_table_contents
          fig 25

        • Then now browse products table and see the content of table as shown below:
        • products_table_contents
          fig 26

      8. 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:
        • output_of_left_join_query
          fig 27

        • 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.

Thus we learned to use PHPmyAdmin tool and the SQL queries from this PHPMYADMIN and SQL Queries tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here