Information Visualization Course

Information Visualization Course Web Design Prof. Anselm Spoerri aspoerri@scils.rutgers.edu

Lecture 9– Overview • Dynamic Web • Programming Concepts • PHP • Introduction | Basic Operations & Coding • MySQL • Introduction | Simple Queries • MySQL & PHP • Set Up PHP Testing Server in Dreamweaver • login.php • Connect to whereru Database • Run Query & Process Results • Source: "Learning PHP, MySQL, and JavaScript" by Robin Nixon.

Web Pages Created So Far • Static HTML Pages • Same each time • Want Interactive or Dynamic Web Pages • Depending on browser, time of day or user behavior or … different appearance • Processing user inputs data via HTML forms • Need some sort of Web programming (JavaScript, PHP)

Basic Client/Server Request/Response Sequence

Dynamic Client/Server Request/Response Sequence $query $result

Dynamic Web • JavaScript • Client-sidescripting access to elements of HTML document • Perform data handling and web server requests in background • PHP • Server-sidescripting • Open source, simplicity and built-in links to MySQL database • MySQL • Open-source Relational Databases that supports structured queries and is free to use and install on web servers • Apache Server • Open source server software that serves up HTML, media files etc • PHP handles main work on web server, MySQL manages data, and JavaScript looks after web page presentation. JavaScript can also talk with your PHP code on the web server whenever it needs to update something (either on the server or on the web page).

Key Programming Concepts • Key Programming Concepts data types numbers, text, boolean (true, false) variable = container of data array = collection of data organized in a sequence statement = combination of variables, operators and data  group of statements  function = collection of statements to perform specific task perform test: true or false if (test) then for loop: where do we start? when are done? how to continue?

Programming Concepts – Overview • Data Types: such as numbers, text, boolean (true, false) • Integer | Boolean | Character | String | Floating-Point • Array: [5, 4, 7] (use FOR loop to examine each array item) • Variable: is way to symbolically to refer to data content • scores = [5, 4, 7]; • Operators: can be used to perform built-in operations • 5 +5; (where + is addition operator) • Statement: combination of variables, operators and data • total = 12 * monthly_average;

Programming Concepts – Overview • Function: collection of statements to perform specific task • function add_scores (score1, score2){ return (score1 + score2);} • total = add_scores (3, 4); • Conditional Programming used so that operations are performed only if certain condition is met: • IF test (only do something if certain condition is true) • FOR loop (keep doing something only if certain condition is true) • WHILE loop (keep doing something until certain condition not true anymore) • Debugging code is essential part of programming ... chasing typos, missing parentheses :)

PHP – Overview • http://en.wikipedia.org/wiki/PHP • Open-sourcehttp://www.php.net : documentation • Server-side scripting • Insert content of one file (or the output of a system call) into another file dynamically • Create images and other files for you, either on the fly or in advance to serve up later. • PHP program responsible for passing back clean file suitable for display in web browser. • Simplicity & Flexibility • Built-in links to MySQL database • .php file extension • <?php echo "Hello World. Today is ".date("l").".";?>

PHP– Overview (cont.) • <?php// This is a comment or /* multiline comments */ ?> • $count = 10; • Must Place $ in front of all Variables • Common Error = Forget Semicolon • $username = "Fred Smith"; // quotation marks for string • echo $username; // echo makes variable visible in Browser

PHP – Code Construction • XHTML tags included inside of PHP code(thus have DOCTYPE declaration and basic XHTML structure) • Some programmers open tag at start of document and close it at the end, outputting any HTML directly from PHP commands. <?php echo "Hello World. Today is ".date("l")."."; ?> • Others choose to insert only smallest possible fragments of PHP within these tags wherever dynamic scripting is required, leaving the rest of the document in standard HTML. Hello World. Today is <?php echo date("l"); ?>. • Latter style may produce faster code, while the former has less complexity having to drop in and out of PHP many times in a single document.

PHP - Variable Naming Rules • Starts with$ • Variable Naming Rules • When creating PHP variables, you must follow these four rules: • Must start with a letter of alphabet or _ (underscore) • Can contain only the characters: a-z, A-Z, 0-9, and _ • May not contain spaces. (e.g., $user_name). • Variable names are case-sensitive. The variable $High_Score is not the same as the variable $high_score. • In PHP, & symbol tells parser to pass reference to variable's value, not the value itself. Be careful when passing values by reference. If you need to keep the original values, make copies of your variables and then pass the copies by reference.

PHP - Strings PHP supports two types of strings: "hello" or 'hello' • Literal string (preserve exact contents), use apostrophe: $info = 'Preface variables with a $ like this: $variable'; • Include value of variable (evaluate string): use quotation marks $count = 44; echo "There have been $count presidents of the US"; • Escape Character : \ • $text = 'My friend\'s car is a Chevy'; • $text = "My mother always said \"Eat your greens\"."; • Important to use when HTML code and variables used inside echo statement

PHP – Array Operations • $team = array('Bill', 'Joe', 'Mike', 'Chris'); // apostrophes • echo $team[3]; // Displays the name Chris •  array indexes start at zero • $twoDim = array(array('a', '', 'c'), array('', 'b', ''),); • echo $twoDim[1][1]; • count($a); // number of array items Array union: $a + $b • Associative Array • $a['firstname'] = "John";

PHP - Operators • Operators • ++$j or --$j // Increment or Decrement • "FirstName" . " " . "LastName" // String concatenation • Boolean AND: && AND OR: || OR $firstname && $lastname $firstname || $lastname • Equality Operator is == Not Equal is != • Identity Operator is === (prevents type conversion) • Operators precedence rules  use parentheses to control what gets computed first and in which order

PHP – Constants, Global and Function • Define Constant define("ROOT_LOCATION", "/usr/local/www/"); • Can not be prefaced with $ sign and need to use define function • Use UPPERCASE • global $username; • Define Function function function_name ([parameter [, ...]]) { // Statements } function squared ($count) { return $count * $count;} Scope of Variable • Local variables are accessible just from part of code where defined: Defined inside of function, only accessibleinside of function. Defined outside of function, only accessibleoutside of function. • Global variables are accessible from all parts of your code. Forgetting Scope of Variable is Common Error.

PHP – IF test • if (test) {do1;} else {do2;} • <?php$a = "1000";$b = "+1000";if ($a == $b) {echo "equal";}else { echo "not equal";}if ($a === $b) {echo "identical";}else { echo "not identical";} ?> • test ? doifTRUE : doifFALSE; • if (test1) {do1;} elseif (test2) {do2;} else {do3;}

PHP – FOR Loop AND FOREACH Loop • for loop • for (init variable ; test variable ; increment variable) { do something;} • for ($count = 1 ; $count <= 12 ; ++$count) { echo "$count times 12 is " . $count * 12 . " ";} • foreach loop used for arrays • $paper = array("Copier", "Inkjet", "Laser", "Photo");$j = 0; foreach ($paper as $item) { • echo "$j: $item "; • ++$j; • }

PHP – Include Code from Other Files • include_once "filename"; • Tell PHP to fetch a particular file and load all its contents • To avoid error of trying to define same constant or function multiple times • require_once "filename"; • To make sure that file is found

PHP – Questions • What tag is used to cause PHP to start interpreting program code? • What are the two types of comment tags? • Which character must be placed at the end of every PHP statement? • Which symbol is used to preface all PHP variables? • What can a variable store? • What is the difference between $variable = 1 and $variable == 1? • Why do you suppose that an underscore is allowed in variable names ($current_user) whereas hyphens are not ($current-user) ? • Are variable names case-sensitive? • Can you use spaces in variable names? • How do you convert one variable type to another (say, a string to a number)? • Are the operators && and and interchangeable? • Can you redefine a constant? • How do you escape a quotation mark? • How can you make a variable accessible to all parts of a PHP program? • What is the result of combining a string with a number?

SQL – Querying Relational Database • SQL = Structured Query Language http://en.wikipedia.org/wiki/SQL Database computer language designed forManaging & Accessing Data in Relational Database • Queries • Ask question about data • Receive answer back by returning subset of table data. • Use JOIN, SELECT, WHERE • Need semicolon ; at the end of query • SQL commands and keywords are case-insensitive

MySQL– Overview • Open-source http://www.mysql.com/ • Fast & Powerful Yet Easy-to-use Database • One or more tables that contain your data • Database • The overall container for a collection of MySQL data. • Table • A subcontainer within a database that stores the actual data. • Row • A single record within a table, which may contain several fields. • Column • The name of a field within a row. • SQL commands and keywords are case-insensitive

MySQL – Overview (cont.) • Field • The name of each field or column within a table. • Type • The type of data being stored in the field. • Null • Whether a field is allowed to contain a value of NULL. • Key • MySQL supports keys or indexes, which are quick ways to look up and search for data. The Key heading shows what type of key (if any) has been applied. • Default • The default value that will be assigned to the field if no value is specified when a new row is created.

Set Up PHP Testing Server • If not already done to access DW via SoftwareAnywhere: • login to http://account.comminfo.rutgers.eduto provision credentials • Site > New Site • Select “Site” Site > Site Name = “PHP Testing Server” Site > Local Site Folder = “PHPfiles” in “My Documents” • Select “Servers" Category in Site Dialog Click on + (Add new server) Specify Server Name Select “SFTP" in pull-down “Connect using” menu SFTP Host = “cactuar.scilsnet.rutgers.edu” Root Directory = “/www/whereru.scils.rutgers.edu/htdocs/iti320semesteryear/yourteam” where for example semesteryear = su13 or fa13 Login = “yourNetIDusername” Password = “yourNetIDpassword” Web URL =“http://whereru.rutgers.edu/iti320semesteryear/yourteam” Test Advanced Tab: Select “PHP MySQL” in pull-down Server Model menu Save Make sure “Remote” and “Testing” checkboxes are selected

Testing PHP Page • Preview in Browser • Update Copy on Testing Server? Select “Yes” • Put Dependent Files ? Select “No“ • Permissions Folder / Files Permission Automatically set to 770 … you don’t have do anything • Folder for Each Team Member Need to update: Root Directory = “…/iti320semesteryear/yourteam/username” where for example semesteryear = su13 or fa13 for example: “…/iti320fa13/yourteam/username” Web URL =“http://whereru.rutgers.edu/iti320semesteryear/yourteam/username” for example: “http://whereru.rutgers.edu/iti320fa13/yourteam/username”

Site Definition for PHP Testing Server • Local Folder = YOUR files and Upload Your Files to Your Server Folder Local Folder = contains YOUR PHP files Root Directory = “<code not included>/iti320fa13/yourteam/username” Web URL = http://whereru.rutgers.edu/iti320fa13/yourteam/username • Local Folder = Contains Folder for YOUR files and Upload Your Files to Your Server Folder Local Folder = contains a folder that contains YOUR PHP files and this folder needs to have same name as YOUR folder on the server. Root Directory = “<code not included>/iti320fa13/yourteam/” Web URL =“http://whereru.rutgers.edu/iti320fa13/yourteam/ •  Number of Local Folder Hierarchy Levels needs to match Number of Server Folder Hierarchy Levels If the local folder of your Site Definition contains just your PHP files, then your Site Definition needs to include the name of YOUR folder on the server so that your PHP files get automatically uploaded to YOUR folder on the server. If the local folder of your Site Definition contains a folder that contains your PHP files, then your Site Definition does not need to include the name of YOUR folder on the server so that your PHP files get automatically uploaded to YOUR folder on the server.

MySQL & PHP – Process Steps • The process of using MySQL with PHP: • Connect to MySQL. • Select the database to use. • Build a query string. • Perform the query. • Retrieve the results and output it to a web page. • Repeat Steps 3 to 5 until all desired data retrieved. • Disconnect from MySQL(usually done automatically)

MySQL & PHP – login.php for your database • Create login.php file make sure to place in personal folder • <?php // login.php make sure to place in personal folder • $db_hostname = 'localhost'; • $db_database = 'whereru'; • $db_username = 'yourMySQLusername'; • $db_password = 'yourMySQLpassword'; • ?> • Upload to Server, View Source in Browser • Now that you have login.php file saved, you can include it in any PHP files that will need to access the database by using the require_once statement.

MySQL & PHP – Connect to Database – Step 1 • <?php // connectDatabase.php • require_once 'login.php'; • $db_server = mysql_connect($db_hostname, $db_username, $db_password); • if (!$db_server) die("Unable to connect to MySQL: " . mysql_error()); • mysql_select_db($db_database) or die("Unable to select database: " . mysql_error()); • ?>

MySQL & PHP – Query Database – Step 2 • Add to connectDatabase.php • $db_table = 'whereru_gigapan'; // description in Sakai Resources • $query = "SELECT*FROM$db_table"; • $result = mysql_query($query); • if (!$result) die ("Database access failed: " . mysql_error());

MySQL & PHP – Display Query Result – Step 3 • Add to connectDatabase.php • $rows = mysql_num_rows($result); • for ($j = 0 ; $j < $rows ; ++$j){ • echo 'ID: ' . mysql_result($result,$j,'id') . ' '; • echo 'Title: ' . mysql_result($result,$j,'title') . ' '; • echo 'Campus: ' . mysql_result($result,$j,'campus') . ' '; • } • This code is inefficient and slow, because $rows * 3 calls are made to the function mysql_result in order to retrieve all the data.

MySQL & PHP – Display Query Result (improved) – Step 4 • $id = 0;// assign index needed to access field in $row array … see data info in Sakai • $title = 2; • $campus = 3; • for ($j = 0 ; $j < $rows ; ++$j) { • $row = mysql_fetch_row($result);// returns array • echo 'ID: ' . $row[$id] . ' '; • echo 'Title: ' . $row[$title] . ' '; • echo 'Campus: ' . $row[$campus] . ' ' . ' '; • }

MySQL & PHP – Refined Query • $db_table = 'whereru_gigapan';$query = "SELECT * FROM $db_table WHERE campus='Newark'";$result = mysql_query($query); if (!$result) die ("Database access failed: " . mysql_error()); $rows = mysql_num_rows($result); $id = 0; // assign index needed to access field in $row array$title = 2;$campus = 3;for ($j = 0 ; $j < $rows ; ++$j) { $row = mysql_fetch_row($result); // returns array echo 'ID: ' . $row[$id] . ' '; echo 'Title: ' . $row[$title] . ' '; echo 'Campus: ' . $row[$campus] . ' ' . ' '; • }

Information Visualization Course