Week 9 : Relational Databases

Introduction

Following up on the previous week's post, here is some more advanced php stuff to learn from. In this post we will cover forms, session, cookies, useful variables & more.

MySQL

MySQL is an open source DBMS which is widely used in php applications for its various advantages. PHP comes with in-built functions to use a mysql connection which is both fast & reliant. Here are some of MySql commands which one has to use in order to Retrieve, Manipulate and Delete data in a MySql Table.

Insert
Inserts a new row into a specified value with specified values
Example:

INSERT INTO table_name (column1,column2,column3) VALUES (value1,value2,value3);

Select / From
Retrieves data from a table in the database.
Example:

SELECT * FROM table_name; //Retrieves all the columns from a table
SELECT col1,col2,col3 FROM table_name; //Retrieves the specified columns from the table.


Delete
Deletes data from a table in the database.
Example:

DELETE FROM table_name; //Deletes all the columns from a tableSELECT col1,col2,col3 DELETE FROM table_name WHERE col1= val; //Deletes all rows where the column is equals to val(can use other WHERE statements aswell)

Drop Table
Removes a table from the database.
Example:

DROP  TABLE table_name; //Deletes all the columns from a tableSELECT col1,col2,col3 DELETE DROP TABLE table_name WHERE col1= val; //Deletes all rows where the column is equals to val(can use other WHERE statements aswell)

Create Table

Creates a new table in the database.

Example:

CREATE TABLE table_name (

id NOT NULL AUTO_INCRIMENT PRIMARY KEY,

col_name VARCHAR(50) NOT NULL

)

Order By

Orders a resultset by  the specified column & parameters

Example:

SELECT * FROM table_name ORDER BY col_name DESC;

Where

Used to filter a resultset, similar to an if statement

Example:

SELECT * FROM table_name WHERE col_name > 10;

Like
Is used to filter a resultset, similar to an if statement. It is mostly used to search for particular values in a column. This can be done by using the % operators with the LIKE keyword.
Example:

SELECT * FROM table_name WHERE col_name  LIKE '%search_string%';

Connecting to a MySql Server using PHP

Step1: 
The first thing that we should do in order to use a MySql Database is to connect to the server where the database is connected. In order to do this in PHP, we have to make us of the mysql_connect() functions which is presented as an example below.

mysql_connect("localhost","username","password");

Step2:
Since a Database server can contain more than one database/schema, we need to specify which database/schema we are going to be using. We do this by calling the mysql_select_d() function. Here's an example.

mysql_select_db("database_name");

Step3:
It's time to actually send a command to the MySql database and start having fun with the data. We can send all sort of mysql queries using the mysql_query() command. This command returns a result which contains, if any, the data requested. Follow the example below in order to send a query to the server.

$resultset = mysql_query("SELECT * FROM table_name")

Step4:
The next step is to check whether the query returned any results. To do this use mysql_num_rows() function like the example below:

$count = mysql_num_rows($resultset);

Step5: 
Now that we have the resultset & we know that it has results, we need to iterate each result in order to do our logic. We can do this by using mysql_fetch_array() which gives us an array filled with the resulted rows as arrays or else using mysql_fetch_object() which turns the resultset into an object. 


while($row = mysql_fetch_array($resultset)){
//do something with $row
}


// while($row = mysql_fetch_object($resultset){
//Do something with $row
}

Week 8 : Advanced PHP

Introduction

Following up on the previous week's post, here is some more advanced php stuff to learn from. In this post we will cover forms, session, cookies, useful variables & more.

Forms

User input is a vital part of the web, without this function, most of the scripts on the internet wouldn't work because most of them require some form of user input. Forms make it possible for a web developer to gather input from a user. Here is a sample HTML form that requests input from a user & its equivalent PHP script

<!-- HTML FORM -->

<form action = "request_form.php" method = "POST">

<p>

<label for = "username">Username</label>

<input name = "username" id = "username" type = "text" />

</p>

<p>

<label for = "password">Password</label>

<input name = "password" id = "password" type = "password" />

</p>

<p>

<button>Login</button>

</p>

</form>

<?php 

//PHP script

//Var that specifies if the user is logged in

$is_user = false;

//if there was a post request

if(isset($username = $_POST['username']) && isset($password = $_POST['password'])

$is_user = check_user($username,$password); // set the user as logged in

}

/*

  Function that checks if a user is valid

  @param1 username - string username to check

  @param2 password - string password to check

*/

function check_user($username,$password)

{

//Var that specifies if the user is logged in

$is_user = false;

//Array that defines a username and password

$creds = array("username"=>"demo"

               "password"=>"password");

}

if($username == $creds['username'] && $password == $creds['password'])

{

$is_user = true;

}

return $is_user;

?>

Forms can use two methods of sending information to the web server which are the Post & Get methods and the difference between them is that when using the GET the data posted will be posted through the url using a Query String but the Post method will send the data through headers so these will be invisible to the user. In PHP to gather this input you can use $_POST['var_name'] for posts & $_GET['var_name'] for get. $_REQUEST['var_name']  gives you the option to get variables posted through both GET,Post & also contains the data accessible via the $_COOKIE method.

Useful Variables 
There are various in built methods and variables in php's core. Here are some of the useful variables a web developer may come in use:

$_GLOBALS //Refers to all the variables in the global scope
$_SERVER // Contains Server & environment information
        $_SERVER['PHP_SELF']// Current executing file name
        $_SERVER['SERVER_ADDR']// The ip address of the server
        $_SERVER['SERVER_NAME']// The name of the server host
        $_SERVER['DOCUMENT_ROOT']// The document root directory
        $_SERVER['HTTP_REFERRER']// The previous page the user was on before visiting this page
        $_SERVER['HTTP_USER_AGENT']// The user agent of the visitor
        $_SERVER['REMOTE_ADDR']// The ip address of the user
$_GET // Associative array containing variables posted via a get form
$_POST // Associative array containing variables posted via a post form
$_SESSION // Associative array containing variables stored in the Session
$_COOKIE // An Associative array containing variables stored in the Cookies





Mail
Sending emails through web application has grown over the past few years and php has taken care of that. In its core, php has an in built function that send email, here's an example code to send an email.

mail($to,$subject,$message) //Refers to all the variables in the global scope

Cookies
Sometimes a web developer would need to store persistent data into a client's pc for example user credentials so the user won't have to login every time he visits a new page. Here is some example code one can use to store, access and delete a cookie.


setcookie('username', 'demo', time() + 4800); /sets a cookie



$_COOKIE['username']; //Read a cookie


setcookie ('userName', '', time() - 4800); // Deletes the cookie




SESSIONS

In today's advanced websites, information needs to be consistently stored. Variables is a way to store data but after the page refreshes, that variable is lost forever unless it is stored in a file, cookie or a database. In order to keep variables between different pages there's a special variable which is called a Session cookie which enables someone to store data throughout the pages and is cleared out when the session expires. This type of variable is generally used for login information including purposes such as storing of personal data & user type.

session_start(); //starts a session




$_SESSION['username']; //Reads a Session




unset($_SESSION['username']);//Deletes only the username variable from the session




session_destroy()// Deletes the Session





Lab Session

1. Create a login for the user

<!-- HTML FORM -->

<form action = "request_form.php" method = "POST">

<p>

<label for = "username">Username</label>

<input name = "username" id = "username" type = "text" />

</p>

<p>

<label for = "password">Password</label>

<input name = "password" id = "password" type = "password" />

</p>

<p>

<button>Login</button>

</p>










</form>




<?php 

//PHP script




//Var that specifies if the user is logged in

$is_user = false;







//if there was a post request

if(isset($username = $_POST['username']) && isset($password = $_POST['password'])




$is_user = check_user($username,$password); // set the user as logged in




}




/*

  Function that checks if a user is valid

  @param1 username - string username to check

  @param2 password - string password to check

*/

function check_user($username,$password)

{

//Var that specifies if the user is logged in

$is_user = false;




//Array that defines a username and password

$creds = array("username"=>"demo"

               "password"=>"password");




}




if($username == $creds['username'] && $password == $creds['password'])

{

$is_user = true;

}




return $is_user;




?>










2. Store a cookie with a remember me option

//added in the html part

<p>

<label for = "remember_me">Remember me</label>

<input type = "checkbox" name = "remember_me" id = "remember_me" />

</p>

<?php 

//PHP script

//Added to the php script

function set_cookie($key,$username){

setcookie($key, $username', time() + 4800); /sets a cookie

}

function get_cookie($var){

return $_COOKIE[$var]; //Read a cookie

}

?>

3. Replace the cookie with a Session

<?php 

//PHP script

session_start(); //Added this to the beginning of the page

//Changed cookie function to this

function set_session($key,$username){

$_SESSION[$key] = $username

}

function get_cookie($key){

return $_SESSION[$key]; //Read a Session variable

}

?>

Week 7: Introduction to PHP

Introduction

Being only a web language, this lightweight & open source development language is a powerful one which is widely used throughout the world to develop web sites. Apart from being well documented, it's popularity gives the community a lot of helpful forums which practically help all the developers solve their nightmares.

Basics

PHP is really simple to develop and has a really short learning curve. The thing that you have to keep in mind is that PHP and HTML have to be integrated in order to show the the work done by a php script in the browser. So in a php file, you would have the relevant php script and if you would like to output some html elements in the browser, one would also have the equivalent html elements. In order to tell the server that a PHP script needs to be executed, the <?php & ?> tags need to be used.

Syntax & Variables

All the things that are going to be mentioned in this post can be looked out for in more detail on PHP's documentation which can be found on : http://www.php.net/manual/. Here is some of the syntax outlined.

Variables
Like any other language, PHP has different ways that one can use to create variables including Global, Normal variables & static variables. Here are examples of these mentioned methods.

Global: DEFINE("name","value");
Normal Variable: $var_name = "Hello World!";
static variable: static $static_var = 1234;


As you can see from the code above, PHP does not define any variable types and a variable can change it's type multiple types throughout its lifetime. There are limited types a variables can hold though and these are:

• Boolean
• Integers
• Floating Point Numbers
• Strings
• Arrays
• Objects
• Resources
• null

Array Declaration

//Simple Declaration
$array = array();

//Declare & Fill
$array = array("one"=>1,"two"=>2);

/*Push into array - iterates the current index and gives the new index to this new variable */
array_push($array,"value");

//Insert a value into an array with a custom key
$array['key'] = "value";


IF statements & loops
Like any other language, PHP has different ways one can create variables including Global, Normal variables & static variables. Here are examples of these mentioned methods.

//IF statement

if($boolean == true){ 
//do something
}


/*
While loop - loops until $boolean remains true- breaks when $boolean becomes false
*/


while($boolean == true)
{
//do something
}


//FOR loop - loops until $i becomes equal to 10


for($i=0;$i<=10;$i++;){
//do something
}


/*
FOREACH loop - loops until $array values are all iterated and in foreach statement separates the array into a key and value pair.
*/


foreach($array as $key=>$value)
{
//do something
}



Printing Functions
There are various printing functions one can use in his application, here is an outline of what they are and what they do.

echo "hello world"; /* Outputs "hello world" - the most widely used to print something simple on a webpage */



print "Hello World"; /* Outputs "Hello World" - same as echo but works a little bit slower */



print_r(array("one","two")); /* Outputs "Array ( [0] => one [1] => two )" - mostly used for debugging purposes and shows the structure of a variable if it is an array or object. Otherwise it would print as the previous two functions do. */



var_dump("Hello World"); /* Outputs "string(11) "Hello World" " - Same as above, this is used for debugging purposes, the difference from the print_r() function is that this function also prints the datatypes of the printed variables. */

String functions and syntax

Here are a couple of things you can do with strings in PHP.

String Concatenation: "Hello " . "World" /* a dot(.) is the concatenation operator in PHP. */



Substring: substr($string,$start,$length);



String length: strlen($string);



String search: strpos($string,$search);



String Replace: str_replace($search,$replace_with,$string);



String Trim: trim($string);



Lab Session

1. Verifying that the local web server is working through a browser:
Steps: Open a browser and enter: http://localhost/ in the url bar, if it responds it works but if yes it will probably automatically redirect you to http://localhost/xampp.





2. Show an assosiative array of usernames and passwords in a table:

Code:

<?php


$users = array(

"user1"=>"password1",

"user2"=>"password2",

"user3"=>"password3",

"user4"=>"password4",

"user5"=>"password5",

);




?>

<table>

<tr><th>Username</th><th>Password</th></tr>

<?php foreach($users as $user => $password){?>

<tr><td><?php echo $user;?></td><td><?php echo $password;?></td></tr>

<?php }?>

</table>



3. Difference between echo and print:
The main difference between the echo & print function is mainly that print works a little bit slower than the echo function. The mostly used is echo but it comes down to only a matter of preference.