Info Web Tech Course Information Technologies Anselm Spoerri
Info + Web Tech Course Information Technologies Anselm Spoerri Ph. D (MIT) SC&I @ Rutgers University aspoerri@rutgers. edu anselm. spoerri@gmail. com Info + Web Tech Course © Anselm Spoerri
Lecture 11 - Overview PHP, My. SQL and CSS Dynamic Website Exercise 5 Demo – Credentials Management – My. SQL: Key Commands – Recap: Login. php | connect. Database. php – Form with Radio Buttons | $_POST | PHP to Display $_POST – Create My. SQL Table for Data from Form with Radio Buttons – Add Form Data to My. SQL table – Use PHP to Display Data in My. SQL Table – Compute Average Score and Control Display with CSS (use My. SQL Workbench to monitor) Lectures – Week 11 Content http: //comminfo. rutgers. edu/~aspoerri/Teaching/Info. Tech/Lectures. html#week 11 Info + Web Tech Course © Anselm Spoerri
Recap – Credential Management My. SQL Workbench Connect to studentweb. comminfo. rutgers. edu using Net. ID (or studentweb) credentials Connect to My. SQL using My. SQL credentials Can talk to server with My. SQL database from your computer SFTP Connect to studentweb. comminfo. rutgers. edu using Net. ID (or studentweb) credentials Can upload HTML and PHP pages to server with My. SQL database Set permissions PHP ‒ Create login_yourlastname. php file that stores My. SQL credentials so we can access My. SQL from inside PHP code Info + Web Tech Course © Anselm Spoerri
Recap – Set Up Connection to PHP & My. SQL Server Open Filezilla and select Site created for “studentweb” Host = “studentweb. comminfo. rutgers. edu” Select SFTP and Normal User = “your. Net. IDusername” (or your. SCILSNETusername) Password = “your. Net. IDpassword” (or your. SCILSNETpassword) Need to specify path to course folder in “Remote Site” slot in Filezilla: /www/studentweb. comminfo. rutgers. edu/htdocs/Your. Course. Folder/username Example: Your. Course. Folder = class-2012 -1 -17 -610 -550 -90 When moving files to server, remember to set permissions 755 URL to test page in Browser: “http: //studentweb. comminfo. rutgers. edu/Your. Course. Folder/username/filename” Example: http: //studentweb. comminfo. rutgers. edu/class-2012 -1 -17 -610 -550 -90 Info + Web Tech Course © Anselm Spoerri
My. SQL – Key Commands Command ALTER BACKUP CREATE DELETE DESCRIBE DROP INSERT RENAME SHOW UPDATE USE Parameter(s) Meaning DATABASE, TABLE Alter DATABASE or TABLE Back up TABLE DATABASE, TABLE, Create DATABASE or TABLE (expression with TABLE & ROW) Delete ROW from TABLE Describe the TABLE'S columns DATABASE, TABLE Delete DATABASE or TABLE (expression with data) Insert data TABLE Rename TABLE (too many items to list) List item's details (expression with data) Update an existing record database Use database My. SQL Resource: http: //dev. mysql. com/doc/refman/5. 5/en/index. html Info + Web Tech Course © Anselm Spoerri
Recap – My. SQL & PHP: Process Steps The process of using My. SQL with PHP: 1. Connect to My. SQL mysql_connect 2. Select the database to use 3. Build a query string 4. Perform the query mysql_select_db $query = "SELECT * FROM table. Name"; $result = mysql_query($query); 5. Retrieve the results and output it to a web page – $rows = mysql_num_rows($result); – $row = mysql_fetch_row($result); 6. Repeat Steps 3 to 5 until all desired data retrieved. 7. Disconnect from My. SQL (usually done automatically) Info + Web Tech Course © Anselm Spoerri
Class Demos of My. SQL & PHP Steps 0 -4 – – – last lecture login. php Connect to Your My. SQL Database Run Query Display Results (faster) Steps 5 -10 this lecture – Form with Radio Buttons and Validate with Java. Script – Use $_POST and PHP to Display $_POST – Create My. SQL Table for Data from Form with Radio Buttons – Add Form Data to My. SQL table (use My. SQL Workbench to monitor) – Use PHP to Display Data in My. SQL Table – Compute Average Score and Control Display with CSS Info + Web Tech Course © Anselm Spoerri
Recap – My. SQL & PHP: login. php for your database – Step 0 Create login_lastname. php file <? php // login_lastname. php make sure to place in personal folder $db_hostname = 'localhost'; $db_database = 'Your. Course. Folder_Net. IDusername'; $db_username = 'your. My. SQLusername'; $db_password = 'your. My. SQLpassword'; ? > Upload to Server, Set Permissions, 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. Info + Web Tech Course © Anselm Spoerri
Recap – My. SQL & PHP: Connect to Database – Steps 1 -4 <? php // query. php require_once 'login_lastname. php'; $db_server = mysql_connect($db_hostname, $db_username, $db_password); if (!$db_server) die("Unable to connect to My. SQL: ". mysql_error()); mysql_select_db($db_database) or die("Unable to select database: ". mysql_error()); $query = "SELECT * FROM classics"; $result = mysql_query($query); if (!$result) die ("Database access failed: ". mysql_error()); $rows = mysql_num_rows($result); for ($j = 0 ; $j < $rows ; ++$j){ $row = mysql_fetch_row($result); echo 'Author: '. $row[0]. ' '; echo 'Title: '. $row[1]. ' '; echo 'Year: '. $row[3]. ' '; echo 'ISBN: '. $row[5]. ' '; ? > Info + Web Tech Course © Anselm Spoerri
Form & Java. Script – Step 5 Create Form with sets of Radio Buttons – Want to collect feedback about tools covered in class – Need to assign name to each set of radio buttons – Name used as attribute in My. SQL database – Use “tool 1”, “tool 2”, … to have flexibility Validate Form with Java. Script – Want to make sure radio buttons selected … what to use? – Use Java. Script function “radio. Button. Selected” – Create Java. Script function “validate” Info + Web Tech Course © Anselm Spoerri
Form & HTML 5 Validation – Step 5 Create Form with sets of Radio Buttons – Want to collect feedback about tools covered in class – Need to assign name to each set of radio buttons – Name used as attribute in My. SQL database – Use “tool 1”, “tool 2”, … to have flexibility Validate Form using HTML 5 – Need to use HTML 5 doctype and encoding – Want to make sure radio buttons selected … how to leverage HTML 5? – Include required in each input of type="radio" <input type="radio" name="tool 1" value="1" Info + Web Tech Course required /> © Anselm Spoerri
$_POST = All User Input – Web server bundles up all user input and puts it into an array named $_POST is an associative array – Each field has an element in the array named after that field. <form action="show. Results. php" method="post"> <input type="radio" name="tool 1"> </form> in show. Results. php $tool 1 = $_POST['tool 1']; Info + Web Tech Course © Anselm Spoerri
$_POST and PHP – Step 6 Create PHP page to Display $_POST Simple, but hand-coded way to display $_POST – echo ("Info Tech Tool 1 = ". $_POST['tool 1']); Flexible way to display $_POST – Create PHP function to display $_POST contents foreach ( $postarray as $tool => $score) { echo "$tool". " = ". "$score<br/>"; } Remember to use: form. Test_step 6. html and show. Results_step 6. php as well as to update action in form and update reference for login_username. php Info + Web Tech Course © Anselm Spoerri
Create “tools” SQL Table – Step 7 Database = yourusername has been created for you USE Database. To. Use; // tells SQL which database to use Note: if Your. Course. Folder contains – hyphens then will trigger SQL error You can use the grave mark ` to escape names that contain reserved lexical symbols such as Example: USE `class-2012 -1 -17 -610 -550 -90_student. Username`; Workaround in My. SQL Workbench: Double-click icon of database to use in left-hand panel below “Schemas” to tell SQL which database to use. CREATE TABLE tools ( tool 1 INT UNSIGNED, tool 2 INT UNSIGNED, id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY) ENGINE My. ISAM; DESCRIBE tools; // Delete table if want to start fresh DROP TABLE tools; Info + Web Tech Course © Anselm Spoerri
Connect to My. SQL Database and Add Form Data – Step 8 Connect to My. SQL See Step 1 make sure to link to your login file Make Sure Form Data Specified isset($_POST['tool 1']) Sanitize Form Data function mysql_fix_string($string) { if (get_magic_quotes_gpc()) $string = stripslashes($string); return mysql_real_escape_string($string); } Add Form Data to Table = tools test in My. SQL Workbench $query = "INSERT INTO tools (tool 1, tool 2) VALUES". "('$tool 1', '$tool 2')"; Remember to use: form. Test_step 8. html and show. Results_step 8. php as well as to update action in form and update reference for login_username. php Info + Web Tech Course © Anselm Spoerri
Display Table Contents – Step 9 Display Contents of Table = “tools” See Steps 2 -4 Make sure to link to your login file Make sure to specify to use tools table $query = "SELECT * FROM tools"; $result = mysql_query($query, $db_server); if (!$result) die ("Database access failed: ". mysql_error()); $rows = mysql_num_rows($result); for ($j = 0 ; $j < $rows ; ++$j){ $row = mysql_fetch_row($result); // need to consult table to identify correct index for field echo ' Tool 1: '. $row[0]. ' '; echo ' Tool 2: '. $row[1]. ' <hr>'; } Remember to use: form. Test_step 9. html and show. Results_step 9. php as well as to update action in form and update reference for login_username. php Info + Web Tech Course © Anselm Spoerri
Display SUM and Average Scores using CSS – Step 10 Retrieve SUM data $query = "SELECT SUM(tool 1), SUM(tool 2) FROM tools"; Display SUM and Average $firstrow = mysql_fetch_row($result); echo ' SUM for Info Tool 1: '. $firstrow[0]. ' and AVE = '. number_format($firstrow[0] / $rows, 2). ' '; CSS class controls display of SUM and Average Define CSS class in style tag inside of head tag Need to escape the quotation marks echo '<div class='result. Style'>'; Remember to have closing tag </div> Remember to use: form. Test_step 10. html and show. Results_step 10. php as well as to update action in form and update reference for login_username. php http: //classes. comminfo. rutgers. edu/2010 -1 -550/aspoerri/form. Test. html Info + Web Tech Course © Anselm Spoerri
Exercise 5 – Next Steps Once step 10 works, need to prepare to be able to track 10 tools ‒ “tools” table needs fields for tool 1, …, tool 10 ‒ Drop existing “tools” table ‒ Create new “tools” table with the needed fields (modify SQL query in step 7) ‒ Form needs to be modified to collect data for 10 tools ‒ validate function needs to be modified so that fields for 10 tools are checked ‒ PHP code needs to be modified to examine and record, retrieve & display data from My. SQL table for 10 tools Info + Web Tech Course © Anselm Spoerri
Reminders Watch out ‒ Spelling Errors ‒ Filename Errors login. php action=“file. php” ‒ Missing Closing " or ) or } ‒ Missing ; ‒ Missing Permissions ‒ Saved in Wrong Location on Server Check Easy Things First Info + Web Tech Course © Anselm Spoerri
- Slides: 19