Week 9 - Introducing SQL with PHP

Introduction 

Big websites now a days always have some type of user accounts and log in. These need somewhere where they store collected clients information like for example a database which is stored on the server. A database allows for manipulation and uploading of data. 

SQL

Structured Query Language is used to communicate with a database. These statments perform tasks like updating, adding and deleting data from a database. Oracle, Sybase, Microsoft SQL Server, Access and more are some common relational DBMS’s that use SQL. The most common SQL commands are "Select", "Insert", "Update", "Delete", "Create", and "Drop" these can be used to accomplish almost everything that one needs to do with a database.

SQL Queries

Queries help search through the database for what information is needed. This is done using the "SELECT" statement and can include other clauses like "FROM" "WHERE" and "ORDER BY".

SQL Examples

Create Table -Create a new table within the existing database

CREATE TABLE person(Id VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32), address VARCHAR(70));

Delte Table - Delete rows which you don't need from a table

DELETE FROM person WHERE WHERE firstname = 'Billy' AND lastname = 'Boo';

Insert Statement - Add new rows to an existing table

INSERT INTO person(id, firstname, lastname, address) VALUES('01234', 'Trisha', 'Brooke', 'United Kingdom');

Update Statement - Update something which is already there in a table

UPDATE phonebook SET address = 'North America', phone = '+1 123 456 7890' WHERE firstname = 'John' AND lastname = 'Doe';

Drop Statement - Delete a full table.

DELETE TABLE person;

Alter Statement - Lets a user modify anything within the table.

ALTER TABLE person RENAME TO people;

This weeks task

1.  Log into MySQL using command line, and perform some commands such as listing the databases.
2.  Attempt to connect to MySQL by using phpMyAdmin
3.  Create a database that stores usernames and passwords
4.  Modify your PHP program from the previous lab session to connect to the database to authenticate the user.

First of was accessing MYSQL through command line. When installing XAMPP you need to locate where this installation directory is by default it is “C:\XAMPP\. The directory in my case was left default therefore the location is “C:\XAMPP\mysql\bin\”. Back to command prompt I typed in CD C:\xampp\mysql\bin which took us to the location then typed in m ysql.exe –h localhost –u root –p as I typed it up it asked me for the password. By default the root password is blank and that makes security an issue. Therefore I decided to set a password, to do so I opened XAMPP on the browser through localhost went on the security page. This showed as unsecure since no password was set. I created a password and it was successfully changed. 

Password successful

After this I went back to the command line to continue with logging into MYSQL. For the password change to take effect i restarted command prompt and MYSQL and logged in agin in MYSQL Terminal as shown in the figure below.

In MYSQL Terminal

Show databases; command

After logging in some commands where executed like show database and show tables. The show databases command lists all the databases found. Then the show tables command was executed and an error occurred because I didn't select which database I wanted it to extract tables from. To fix it I used the "USE db_name" command and it worked.

Phpmyadmin

This is an open and free source software,it is used to handle the administration and management of MYSQL databases through a graphic interface. As also one can see in the name it this is written in PHP and is a very popular web-based MYSQL management tool. Users privileges can be managed, one can also make use of the import function, importing SQL or CSV. On the contrary of import one can also make use of the export function where your database can be exported in CSV, SQL and other formats.

Phpmyadmin was downloaded, from localhost I navigated to phpmyadmin. If this doesn't work one can check the config.inc.php file and check out some firewall settings. Below is an image showing that phpmyadmin was located 

Creating the database
A database was created called users using the create database statment, then a table was also created where in it there are the id which is a primary key, a not null and also it is auto incremented. Then the username field and the password where the users details will be stored as seen in the diagram below.

Create database and table

After the database was created, data was inserted  using the "insert into" statement. 

Insert into

After that I checked if the database was visible from phpmyadmin and it was. 

Connecting the database with php

In the previous exercise an array was created, for this week we will use the database which I just created and connect it to the login using php.Two variables were created $myServer and $myDb. The first one shows the details of the database and the second database shows which database to use.

The select command selects the database to work with, username and password that the user entered and is stored as a variable $result.

Next another class was created, first the $strsql is a variable that stores the sql commands. The username and password that the user entered are then stored in the $result variable. 

Conclusion
I personally think that PHP is a vast and good language to learn interacting SQL with PHP is a very useful feature. Coding these bits weren't difficult some research was made online to find some help, this is also another advantage because one can find tones of help online.


References: 

                http://www.intechgrity.com/how-to-connect-to-xamppmysql-using/

http://www.ntchosting.com/databases/structured-query-language.html

http://www.sqlcourse.com/intro.html