Two Useful Tools You Can’t Live Without by Jenny Mitchell SJSU CS157B Section 1 09-21-04

1. PHP and MySQL Two Useful Tools You Can’t Live Without by Jenny Mitchell SJSU CS157B Section 1 09-21-04 1

2. Putting Our Knowledge to Use • We are learning about databases, now what can we do with that knowledge? • Oracle • Large download and installation • Most of us used Oracle 9 in 157A • MySQL • Small download or use from web server • Same SQL syntax, run from command line or other application 2

3. About MySQL • GNU Free, open-source, multi-platform • Implements the standard SQL language • Relational DBMS • Written in C and C++ • Uses MyISAM B-tree disk tables • Uses in-memory hash tables for temporary tables 3

4. MySQL.com • Developer Zone: Downloads and Documentation • Available for Linux, Windows, Solaris, FreeBSD, and more 4

5. MySQL Installation • Download 4.0 binary packages • Check installation documents to see what is automatically installed where • Add appropriate aliases and users • First start up in safe mode and test default tables • Run from shell or application 5

6. MySQL Terminal Example 6

7. MySQL Terminal Example In-Class Demo Insertion 7

8. Building On MySQL • What languages can we use for building an application on MySQL? • ODBC Connector • JDBC Connector • .NET Connector • Web scripting languages • PHP, ASP, JSP 8

9. PHP • PHP: Hypertext Processor • Open source scripting language for web developers to write dynamically generated webpages • Can download and install source and binaries from www.php.net • Need your computer to act as a server • Apache, Tomcat are free downloads 9

10. Basic PHP Scripting • Syntax similar to C and JavaScript • Not a compiled language, interpreted • You don’t know where an error is until you try to run it, and even then the line number doesn’t always tell you where the actual error is • Same logical structure - if/else, for, while • Variables have no type declaration 10

11. Hello, World! <?php $world = true; if ($world) echo(“Hello World!”); else echo(“Hello Nobody”); ?> 11

12. Hello Nobody <?php $world = false; if ($world) echo(“Hello World!”); else echo(“Hello Nobody”); ?> 12

13. Uses of PHP and MySQL • Anything in a MySQL database can be viewed on a webpage • Any information which can be captured from a website can be stored into a database FORMS 13

14. Connecting to MySQL from PHP /* Connection */ $username = ‘user’; // username $password = ’password'; // password $webhost = 'localhost'; // host (localhost or something on a web server) $db = ’test'; // the database $dbc = mysql_connect($webhost, $username, $password) or die("<p>Could not connect: <i>" . mysql_error()); @mysql_select_db($db) or die("<p>Could not find database - $db - <i>" . mysql_error() . "</i>"); 14

15. Queries and Results /* Perform SQL query and catch data */ $query = "SELECT * from t1 WHERE email != ‘’ ORDER BY name ASC"; $result = mysql_query($query) or die("<p>Query failed: <i>" . mysql_error() . "</i>"); 15

16. Displaying Results $num = (int)@mysql_num_rows($result); echo “<table border=2 cellspacing=3>”; echo "<tr><td><b>ID</b></td><td><b>Name</b></td><td><b>Email</b></td></tr>"; for ($i = 0; $i < $num; $i++) { $line = mysql_fetch_array($result); echo "<tr>"; echo "<td>" . $line[id] . "</td>"; echo "<td>" . $line[name] . "</td>"; echo "<td>" . $line[email] . "</td>"; echo "</tr>"; } echo "</table>"; /* Housekeeping */ mysql_free_result($result); mysql_close($dbc); 16

17. Selection and Variables • SQL Query statements can be built over time • PHP variables can be used in SQL Query statements • Variables can be generated based on input form values 17

18. Selection and Variables Code HTML FORM CODE <form method=post action=testinputquery.php> <p>Enter a column name by which to sort: <input type=text size=8 maxlength=8 name=column> <p><input type=Submit value=Submit name=Submit><input type=Reset value=Reset> </form> SQL PROCESSING CODE /* Gather data from form variable */ $column = $_POST['column']; /* Perform SQL query to catch data */ $query = "SELECT * from t1 WHERE email != '' ORDER BY $column ASC"; $result = mysql_query($query) or die("<p>Query failed: <i>" . mysql_error() . "</i>"); 18

19. Selection and Variables In-Class Demo Data Mining from Forms 19

20. Insertion with Forms • Create form with all input variables you want, submit action to someform.php • PHP script grabs all “post”ed variables in the form $var = $_POST[‘var’]; • Note that empty variables are the empty string and not the null value 20

21. Insertion with Forms Code HTML FORM CODE <form method=post action=testinsertion.php> <p>Insert some data into this database</p> <p><b>Name:</b> <input type=text name=name size=15 maxlength=20> <p><b>Email:</b> <input type=text name=email size=15 maxlength=25> <p><input type=Submit value=Submit name=Submit> <input type=Reset value=Reset name=Reset> </form> SQL PROCESSING CODE /* Gather data from form variable */ $name = $_POST['name']; $email = $_POST['email']; /* Perform SQL query to catch data */ $query = "INSERT INTO t1(name, email) values(\"$name\", \"$email\")"; mysql_query($query) or die("<p>Query failed: <i>" . mysql_error() . "</i>"); $num = mysql_affected_rows(); if ($num == '1') echo "<p>Data was inserted successfully"; 21

22. Insertion with Forms In-Class Demo Inserting Data 22

23. In Conclusion… • MySQL - relational database package which is free, small, easy to install • PHP - dynamic scripting language which is free, small, easy to install, and automatically works with MySQL • MySQL + PHP = affordable, portable, easily accessible database backend & application frontend 23

24. What Are You Waiting For? • http://www.mysql.com • http://www.php.net GO BUILD! 24