PHP and MySQL Web Development Essentials

Housing and Dining Online by Andrew Gorges

Outline • Overview of PHP • Overview of MySQL • Using PHP • Using MySQL • PHP and MySQL together • Production Application • Role of Verisign

PHP Overview • Easy learning curve • Syntax very similar to C • Large function library • Embedded directly into HTML • Interpreted, no need to compile • Platform Independent • Web Server Independent • Free and Open Source

Simple PHP • PHP code must be surrounded with special tags • Opening tag: <?php Closing tag: ?> • Write text to the browser with the echo command • To write Hello, World! to the broswer, include the following in hello.php • <?php echo “<h2>Hello, World</h2>”; ?>

PHP Form Data • Access to the HTTP POST and GET data is simple in PHP • The global variables $_POST[] and $_GET[] contain the request data <?php if ($_POST["submit"]) echo "<h2>You clicked Submit!</h2>"; else if ($_POST["cancel"]) echo "<h2>You clicked Cancel!</h2>"; ?> <form action="post.php" method="post"> <input type="submit" name="submit" value="Submit"> <input type="submit" name="cancel" value="Cancel"> </form>

PHP Sessions • Sessions store their identifier in a cookie in the client’s browser • Every page that uses session data must be proceeded by the session_start() function • Session variables are then set and retrieved by accessing the global $_SESSION[]<?php session_start(); if (!$_SESSION["count"]) $_SESSION["count"] = 0; if ($_GET["count"] == "yes") $_SESSION["count"] = $_SESSION["count"] + 1; echo "<h1>".$_SESSION["count"]."</h1>"; ?> <a href="session.php?count=yes">Click here to count</a>

MySQL Overview • Fast, free, stable database • Syntax is similar to Oracle • Many of the same features as Oracle • Production version still missing subqueries, stored procedures, and triggers • Frequently used in conjunction with Linux, Apache, and PHP

Creating a Table • Making a new table is rather easy in MySQL • CREATE TABLE books ( idNumber int primary key auto_increment, title varchar(30), author varchar(30));

Inserting Data • The insert statement is straightforward • INSERT INTO books (title,author) VALUES( “Let Freedom Ring”, “Sean Hannity”);

Other Operations • ALTER TABLE books ADD COLUMN subtitle varchar(50) AFTER title; • UPDATE books SET subtitle=“Winning the War of Liberty over Liberalism” WHERE idNumber=1; • SELECT * FROM books; • DELETE FROM books;

MySQL and PHP Together <?php include("/var/db.php"); $dbLink = mysql_connect("localhost", $dbUser, $dbPass); $sql = "SELECT * FROM books"; $res = mysql_db_query("test", $sql, $dbLink); $row = mysql_fetch_assoc($res); $title = $row["title"]; $subtitle = $row["subtitle"]; $author = $row["author"]; ?> <table border="1"><tr> <td><b>Title</b></td><td><b>Sub Title</b></td><td><b>Author</b></td></tr> <tr><?php echo "<td>$title</td><td>$subtitle</td><td>$author</td>";?> </tr></table>

Production Application • Early Room Preference System Online • Heavy use of MySQL database for room maps • Uses Verisign’s PayFlowPro™ • All information passes over Secure Socket Layer

System Architecture MySQL PHP Web Browser VerisignPayFlowPro

Role of Verisign • Provide the Secure Server Certificate • Provide PayFlow Pro™ Interface$transaction = array( 'USER' => 'ksuhousing', • 'PWD' => ‘*********', • 'PARTNER' => 'VeriSign', • 'TRXTYPE' => 'S', • 'TENDER' => 'C', • 'AMT' => 25.00, • 'ACCT' => $number, • 'EXPDATE' => $expDate, 'COMMENT1' => 'App Payment', • 'STREET' => stripslashes($address), • 'ZIP' => $zip • ); • //execute the transaction • $response = pfpro_process($transaction);

Links • http://www.php.net • http://www.mysql.com • http://www.verisign.com

Questions?

PHP and MySQL Web Development Essentials