PHP & MySQL Data Visualization: Course Overview & Assignment Samples

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

2. Lecture 10 – Overview • Short Assignment 2 • MySQL & PHP code Sample files have been uploaded to Sakai > Resources > PHP Files • Browse Viewto access • Gigapans(practice in Short Assignment 2) • Photosynths • Videos • Attach Hyperlink to Thumbnail to Link to Individual Item page • Individual Item based on supplied id • Gigapan • Photosynth • Video

3. Short Assignment 2 • Files to consult and modify • Info about MySQL Tables in whereRUin Sakai > Resources • databaseConnect_table_gigapan_lastname.php • Shows you how to control number of Gigapans to embed • Shows you how to create table with Gigapan thumbnails • Example:databaseConnect_table_gigapan_spoerri.php • Change MySQL query so that you retrieve the most recent Gigapans for specific campus • Customize code so that only one embedded Gigapan • Customize code so that only two rows of 5 Gigapan thumbnails are shown • Example:databaseConnect_table_gigapan_SA2a_spoerri.php • Customize & Create CSS code to achieve desired look • Redo above steps for next campus • Example:databaseConnect_table_gigapan_SA2bs_spoerri.php

4. MySQL – Queries • "SELECT something FROM tablename"; "SELECT id, title, campus FROM $db_table"; COUNT "SELECT COUNT(*) FROM $db_table"; // number or rows in table WHERE used to narrow results based true expression "SELECT * FROM $db_table WHERE field='value'"; LIKE qualifier allows searches on parts of strings"SELECT * FROM $db_table WHERE field LIKE "%NB%""; LIMIT choose how many rows to return and where to start"SELECT * FROM $db_table LIMIT 3,2"; // skip first 3 rows and return 2 rows ORDER BY sorts results by one or more columns"SELECT * FROM $db_table ORDER BY campus, date_created DESC"; GROUP BY "SELECT * FROM $db_table GROUP BY campus";

5. SQL Queries • Possible queries:$query = "SELECT * FROM $db_table"; • $query = "SELECT * FROM $db_table WHEREcampus='Newark'"; • $query = "SELECT * FROM $db_table ORDER BY date_created DESC"; • $query = "SELECT * FROM $db_table WHERE campus='Newark' ORDER BY date_created DESC";

6. Access Gigapans • Database Table: • $db_table = 'whereru_gigapan'; // description in Sakai Resources • Example:databaseConnect_table_gigapan_spoerri.php • Embed Gigapan: • <object type="application/x-shockwave-flash" style="width:$width"."px; height:$height"."px;" data="$row[$embed]" ><param name="movie" value="$row[$embed]" /></object> • Thumbnail Images: • t= 1 (for gigapan) w and h specify width and height of thumbnail • <imgsrc="http://whereru.rutgers.edu/thumb.php?id=$row[$id]&t=1&w=84&h=42" /> • Remember: you have to escape \ the quotation marks if used inside an echo statement

7. Access Photosynths • Database Table: • $db_table = 'whereru_photosynth'; // description in Sakai Resources • Example: databaseConnect_table_photosynth_spoerri.php • Embed Photosynth: • <iframesrc=“$row[$embed]" frameborder="0" width="$width" height="$height" scrolling="no" marginheight="0" marginwidth="0"></iframe> • Remember: you have to escape \ the quotation marks if used inside an echo statement • Thumbnail Images: • t= 2 (for photosynth) w and h specify width and height of thumbnail • <imgsrc="http://whereru.rutgers.edu/thumb.php?id=$row[$id]&t=2&w=84&h=42" /> • Remember: you have to escape \ the quotation marks if used inside an echo statement

8. Access Videos • Database Table: • $db_table = 'whereru_video'; // description in Sakai Resources • Example: databaseConnect_table_video_spoerri.php • Embed Video: • <object width="$width" height="$height"><param name="movie" value="$row[$embed]"></param><param name="allowFullScreen" value="true"></param><param name="allowscriptaccess" value="always"></param><embed src="$row[$embed]" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="$width" height="$height"></embed></object> • Remember: you have to escape \ the quotation marks if used inside an echo statement • Thumbnail Images: • t= 3 (for video) w and h specify width and height of thumbnail • <imgsrc="http://whereru.rutgers.edu/thumb.php?id=$row[$id]&t=3&w=84&h=42" /> • Remember: you have to escape \ the quotation marks if used inside an echo statement

9. Access Gigapan with Specific ID • Database Table: • $db_table = 'whereru_gigapan'; // description in Sakai Resources • SQL Query to access record for specific id: • $query = "SELECT * FROM $db_table WHERE id=$id";

10. Attach Hyperlink to Individual Item • Attach hyperlink to thumbnail so that click loads individual viewSet border of image to zero so that blue border does not show because of hyperlink • URL: databaseConnect_individual_gigapan_links_spoerri.php • <a href="individualGigapan.php?id=$row[$id]"> <imgsrc="http://whereru.rutgers.edu/thumb.php?id=$row[$id]&t=1&w=84&h=42" border="0" /></a> • To be able to supply variables via URL need start with ? for first variableand use & for subsequent variables

11. View Individual Item • To display Gigapan with a specific id passed in via URL URL: databaseConnect_individual_gigapan_spoerri.php?id=50 • Use extract($_GET) to assign id to a php variable • So that extracted variables don't conflict with ones that you have already defineduse parameter in extract function, like this: • extract($_GET, EXTR_PREFIX_ALL, 'fromget'); • You can access the passed-in id via the $fromget_id variable • SQL query to access record for specific id • $query = "SELECT * FROM $db_table WHERE id=$fromget_id";