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

Week 8 - More PHP

Introduction

This week I will blog more in depth about PHP. This lecture was about functions, form handling, cookies and sessions mostly. 

Functions

We already saw functions in Javascript. Functions are use to seperate a section of code that can be used frequently which can be applied to different data. Functions can also be created by the user. This can be placed anywhere as long as it contains the php starting and ending tag and can also be called internally.

Cookies

Cookies also known as web cookie, http cookie and browser cookie, is a piece of text stored on users computer as a test file by their web browser.Cookies keep track of information about your activity on the site. 
 This can be used for several things like authentication, storing site preferences. This stored information can be opened by a simple editor and sensitive data and passwords should be encrypted. These are used to identify a visitor, for example when a visitor logs in they can be greeted by their name. 
When using IE as a browser the stored cookies are kept in a seperate file in a folder named "Cookies" which is usually found in the "Documents and Settings" folder. Users using Mozilla can find their cookies in a text file named cookies in a folder which is ofter called "Firefox/Profiles". Reasons why website use cookies are: Customization, Distribution, Privacy and Security.
Cookies are done using the setCookie() function, if this is correct it will return true, on the otherhand if it comes to a fail it will return a false. They need several parameters only the name is required the others are optional. Example: setCookie(name, value,expire);. To delete a cookie instead of doing the "+" you use the "-".

Sessions
These are another way how one can hold persistence data between pages. A file is created when PHP starts a session. This file contains variables and values and this information stored is available throughout the entire visit of the Web site. 

Task
This weeks task was to created a login page where the username enters its username and password and logs in. Then a remember me button is to be made using cookies and then replacing is with sessions.

HTML login

On the left on can find the code for the form. The form's method is "post". The method "post" involves  anything like storing or updating data, or ordering a product, or sending E-mail. "Post" method is more secure then get since in "get" method data is added to the URL.The action attribute shows to where the form-data will be sent to when the form is submitted. A check box was used for the remember me button.

PHP

PHP was used to do the validating part. Two variables where created  $user and $password, these are compared against the array to check if the login is valid or not. Then there is declared the array  $detail that holds the data. Function isset() is used to check if the variable exists or not while the $_POST which is another function will collect the data. It first checks if the combinations that the user entered are valid, if they are valid it will log in while if they are invalid the page directs to invalid.php where the user will be prompt with an Error alert.

Below are some screen shots of good combinations and a bad combination

Invalid Login

This was done by displaying an alert in the echo if the usernames and passwords weren't found in the array

Good Log in 

While if the password and username entered are correct the user is logged in and is greeted with his username and can also choose to log out again.

Remember me check box

This check box is an option which the user can opt to tick and his details will be saved for the specified amount of days. In our case these details will be saved for 60 days. The setCookie() function is used to create a cookie. Three cookies where created one for the username another for the password and also for the data when visited. These cookies expire in 60 days. For the password MD5 was used to ensure more safety, with MD5 the password isn't saved as plain text but it will get encrypted. The last visited date will show the user on the screen when he logs in, the last time he was on his account. This can be of a good use, since if the date shows a date where he didn't log out he will be able to know someone else is entering his account.

I had a problem figuring out how to check if these are being stored or not and then found out about the cookie options from the internet options.

Cookies being saved

After using cookies, than we had to switch cookies with sessions. A session starts using session_start(); First a session was done to store variables, for password and username, this was done as shown below. First the variables are stored in the sessions and then displayed using the echo.

To then destroy a session one can use from two options either the unset ($_SESSION["username"] or else the session_destroy() function. This was used for the user to be able to log out of his account.

Conclusion

PHP is a rather interesting language. One can do many things that the client might want and in a not so complex way. There are tones of help on internet which if you get stuck or anything one can find a solution easily. I don't thing that php is such a hard language if you put some mind to it one can learn it easily.  

Week 7 - The first shot with PHP

Introduction 

This weeks lesson was about some basic PHP. I did some deep research before starting to help understand more what was explained in class and started this weeks task.

What is PHP?

This is a server side and an HTML-embedded scripting language for creating dynamic Web Pages,it contains certain syntax which was borrowed from other languages like C, Java and Perl together with is unique features. PHP allows web developers to write pages quickly that are dynamic. PHP is an open source and cross platform it runs on Windows NT and many Unix versions and it can also be built as an Apache module.

How does it work?

When the PHP webpage is visited by someone,  the server processed the PHP code and checks out which part needs to be shown to the visitor, like the content and also the pictures. The other parts like file operation and some math calculations are hidden, it then translates PHP into HTM and sends the webpage to the visitors web browser. 

Some PHP Advantages and Usage

     ·         Reduces time when creating a large website

     ·         Open up thousands of possibilities for online tools.

     ·         Allow creation of shopping carts for e-commerce websites.

     ·         Allows the addition of connecting to databases within a website

     ·         Send HTTP headers and set cookies and redirect users

     ·         Integration with various libraries that let you generate PDF document and also parse XML

When coding PHP there is no need for separate files or anything as this goes directly in your HTML document this is done by making use of the PHP tags <?php……<?> the PHP engine processes whatever is between those tags. One though can do a separate file and then can include it using the include command ( <?php include(“calc.php”); ?> )  Also like other languages it is good to keep code clean and readable by making use of whitespaces and also comments. Like some other languages in PHP you don’t have to declare variables prior to using them. This language feature is called being ‘loosely typed’. As in other programing languages in PHP one can also find loops, if..else, while, switch case and for loop. Like any other language PHP also handles arrays. In PHP one can find 3 different kind of arrays which are the following: 

• Numeric Array - Array with numeric index

$person[0] = "Billy";

$person[1] = "Lucy";
echo  $person [0] . "and" . $person[1]. " are my best mates.";

In this type of array you make use of the key and value array. The keys are the numbers used to specify in the array while the values are the names of the person. Each key represents a value. To display the result then one uses the echo, you get the value of key 1 and key 2 and are displayed. 

• Associative Array - Array where each ID key is associated with a value

In the previous example we had an integer as a key but one can also have a string as a key.  In this type of array the key is associated with a value. 

$age = array("Billy"=>28, "Janne"=>85, "Joe"=>82, "Lucy"=>40, "Trish"=>36);;

• Multidimensional Array - Array having one or more arrays

In this type of array each elements in the main array can also be an array and even element in the sub-arrays can be an array. Therefore each type of element can be an array.

This weeks task

For this weeks task we had to:

• Using your browser, verify that PHP is working on your web server.
• Create an associative array of user names and passwords and list the entire array in a table.
• Explain in your blog the difference between the echo() and print() functions.

Explanation of tasks

To make sure that PHP works on my browser I simple created a plain page and placed in some PHP code. 

This code here shows some variables that were

PHP Code for test

declared and assigned some values to them. Values are of different type some of string while others are numbers and one is a date. Then they are printed it out on the screen. Then I saved and check if it works from localhost and the result is as shown below.

PHP result for test

Next the array was created, it is made out of two columns one for the user's username and the other for the user's password. This was created using associative array type. Lines 9-14 show the table being created for the array to be placed within it. A <th> tag was used for the headers of each column. The array was created by first showing that now we will put php code with HTML using <?php...?>. The array was then declared as seen in line 16. A loop was needed to iterate through the array so they will be displayed.

Code for Array

Array Created in a table

 Next in line was to explain the difference between echo() and print(). Both echo() and print() are used to display the output of the user but when do you use echo() and when do you use print(). One main difference between echo() and print() is that echo() is abit faster. I found some code on a site where it tested the time taken for each of them. Echo() was executed in: 0.057446947097778 while print() took: 0.072533121109009. The reason behind this is because print function returns a statues where it determines if it was successful or not while echo() just prints the output.

<?php

$t1 = microtime(true);

system('C:/php5/php.exe C:/www/echo.php');

$t2 = microtime(true);

$r = $t2 - $t1;

echo 'echo: '. $r;

$t1 = microtime(true);

system('C:/php5/php.exe C:/www/print.php');

$t2 = microtime(true);

$r = $t2 - $t1;

echo 'print: '. $r;

?>

Print can then operate as a function  which can be used for more complex operations. If you try using the echo() as a function you will get an error as follows: Parse error: syntax error, unexpected T_ECHO in /www/testPage.php on line 4.

Also another difference is that echo()  can handle multiple parameters.

Conclusion

Looking back this task PHP is a reall useful language that helps interact with the user and allows the use of databases. I personally think that PHP is a good language and even the fact that it is loosely typed it is an advantage for the programmer and makes it easier. Also it isn't that hard to learn it just like other languages one needs to do hands on to learn.

References: 

http://www.techrepublic.com/article/what-is-php/5074693

http://www.tizag.com/phpT/include.php
http://www.w3schools.com/php/php_arrays.asp

http://triggsolutions.com/php-snippets/php-echo-vs-print/219