CSCI 305 Introduction to Database Systems

1. <bookstore> <book category="COOKING"> <title lang="en">Everyday Italian</title> <author>Giada De Laurentiis</author> <year>2005</year> <price>30.00</price> </book> <book category="FICTION"> <title lang="en">Harry Potter</title> <author>J K. Rowling</author> <year>2005</year> <price>29.99</price> </book> <book category="WEB"> <title lang="en">Learning XML</title> <author>Erik T. Ray</author> <year>2003</year> <price>39.95</price> </book> </bookstore> XML SELECT * FROM Students WHERE Grade >= 90 CSCI 305Introduction to Database Systems Professor Brian R. King Bucknell University – Computer Science Dept. PHP

2. Objective • This is designed to teach you some bare essentials about PHP, and using it to connect to your DB

3. PHP Basics • PHP – Hypertext Preprocessor • Server-side scripting language • Scripts reside between reserved PHP tags • Programmer can embed PHP in the HTML pages • <?php…?> • Structurally similar to C/C++/Java syntax

4. PHP Basics • Comments: • // • # • /* */ • Variables: • All variables begin with $ • Case sensitive • Type: • PHP is loosely typed. You do not need to declare variables before you add a value to it. PHP determines the type, depending on its initial value

5. PHP echo command • echo is used to output the parameters passed to it • Typical usage – send data to the client's web-browser

6. Echo example • Notice how echo ‘5x5=$foo’ outputs $foo rather than replacing it with 25 • Strings in single quotes (‘ ’) are not interpreted or evaluated by PHP • This is true for both variables and character escape-sequences (such as “\n” or “\\”) <?php $foo = 25; // Numerical variable$bar = “Hello”; // String variable echo $bar; // Outputs Hello echo $foo,$bar; // Outputs 25Hello echo “5x5=”,$foo; // Outputs 5x5=25 echo “5x5=$foo”; // Outputs 5x5=25echo ‘5x5=$foo’; // Outputs 5x5=$foo ?>

7. Arithmetic Operations • $a - $b // subtraction • $a * $b // multiplication • $a / $b // division • $a += 5 // $a = $a+5 Also works for *= and /= <?php $a=15; $b=30; $total=$a+$b; echo $total; echo “<p><h1>$total</h1>”; // total is 45 ?>

8. Concatenation • Use a period to join strings into one. <?php $string1=“Hello”; $string2=“PHP”; $string3=$string1 . “ ” . $string2; echo $string3; ?> Hello PHP

9. Escaping the Character • If the string has a set of double quotation marks that must remain visible, use the \ [backslash] before the quotation marks to ignore and display them. <?php $heading="\"Computer Science\""; echo $heading; ?> “Computer Science”

10. Control Structures • if / else if / else • while / do..while / for • foreachexists, but syntax is different • switch / case / break / default • boolean expressions • All of the above are formed just like Java (same comparison and logical operators)

11. Arrays • Three types: • Numeric array – An array with a numeric index • Associative array – Each ID key is associated with a value • Multidimensional array • Dealing with arrays is easy. However, the library of functions for arrays is large.

12. Functions • Functions MUST be defined before then can be called • Function headers are of the format • Note that no return type is specified • Unlike variables, function names are not case sensitive (foo(…) == Foo(…) == FoO(…)) function functionName($arg_1, $arg_2, …, $arg_n)

13. Functions example <?php // This is a function function foo($arg_1, $arg_2) { $arg_2 = $arg_1 * $arg_2;   return $arg_2;} $result_1 = foo(12, 3); // Store the function echo $result_1; // Outputs 36 echo foo(12, 3); // Outputs 36 ?>

14. Include Files • include "opendb.php"; • include "closedb.php"; • The code in files will be inserted into current code. • Nice for modularizing and reusability

15. PHP Forms and User Input • PHP $_GET and $_POST variables are used to retrieve information from forms, like user input • NOTE: Any form element in an HTML page will automatically be available to your PHP scripts

16. Example form welcome.php

17. form.php • <?php • if ($_POST["submit"]) • echo "<h2>You clicked Submit!</h2>"; • else if ($_POST["cancel"]) • echo "<h2>You clicked Cancel!</h2>"; • ?> • <form action="form.php" method="post"> • <input type="submit" name="submit" value="Submit"> • <input type="submit" name="cancel" value="Cancel"> • </form> What you see on your browser after clicking Submit

18. MySQL connection • Best way is to just show by example…

19. connect.php <?php $user = "brk009"; $password = "……"; $database = "brk009_WWIIShipsDB"; $conn = mysql_connect("db.eg.bucknell.edu",$user,$password); if (!$conn) die("Cound not connect to database"); mysql_select_db($database) or die("Unable to select database"); $result = mysql_query("SHOW TABLES"); if (!$result) die("Query to show tables failed");

20. connect.php (cont.) $num_row = mysql_num_rows($result); echo "<h1>Choose one table:</h1>"; echo "<form action=\"showtable.php\" method=\"POST\">"; echo "<select name=\"table\" size=\"1\" Font size=\"2\">"; for($i = 0; $i < $num_row; $i++) { $tablename = mysql_fetch_row($result); echo "<option value = \"{$tablename[0]}\" >{$tablename[0]}</option>"; } echo "</select>"; echo "<div><input type=\"submit\" value=\"submit\"></div>"; echo "</form>"; mysql_free_result($result); mysql_close($conn); ?>

21. showtable.php <?php $user = "brk009"; $password = "……"; $database = "brk009_WWIIShipsDB"; $table = $_POST["table"]; $conn = mysql_connect("db.eg.bucknell.edu",$user,$password); if (!$conn) die("Cound not connect to database"); mysql_select_db($database) or die("Unable to select database"); $result = mysql_query("SELECT * FROM {$table}"); if (!$result) die("Query to show tuples from table failed!" . mysql_error());

22. showtable.php (cont.) $fields_num = mysql_num_fields($result); echo "<h1>Table: {$table}</h1>"; echo "<table border='1'><tr>"; // Print headers for($i = 0; $i < $fields_num; $i++) { $field = mysql_fetch_field($result); echo "<th>{$field->name}</th>"; } echo "</tr>\n"; // Print data while($row = mysql_fetch_row($result)) { echo "<tr>"; foreach($row as $cell) echo "<td>$cell</td>"; echo "</tr>\n"; } mysql_free_result($result); mysql_close($conn); ?>