Introduction to Web Database Development in PHP
by K. Yue
1. Introduction
2. PHP
PHP Language Basics
Examples:
Dumping available variables (predefined variables, since there are no user defined variables.
<?php
var_dump(get_defined_vars());
?>
Notes:
<table>
<?php
foreach(get_defined_vars() as $arrVar => $arrValue) {
foreach ($arrValue as $var => $value) {
echo "<tr><td>$arrVar::$var</td><td>$value</td></tr>\n";
}
}
?>
</table>
Note:
Server2.php
<table border="1">
<tr><td>Server variables</td><td>Values</ed></tr>
<?php
foreach($_SERVER as $var => $value) {
echo "<tr><td>$var</td><td>$value</td></tr>\n";
}
?>
</table>
Note:
Predefined Variables
Getting input from Web pages
Examples:
get1.php:
<table border="1">
<tr><td>GET Parameter</td><td>Values</ed></tr>
<?php
foreach($_GET as $var => $value) {
echo "<tr><td>$var</td><td>$value</td></tr>\n";
}
?>
</table>
Note:
get2.php:
<?php
echo "Dear " . $_GET['name'] . ", with ID " . $_GET['id'] . ". Welcome, your grade is " . $_GET['grade'] . ".\n";
?>
Examples:
post1.php:
<?php
if (! array_key_exists('name', $_POST)) {
echo <<<__FORM
<form action="post1.php" method="post">
<p>Your name: <input type="text" name="name" /></p>
<p>id: <input type="text" name="id" /></p>
<p>grade: <input type="text" name="grade" /></p>
<p><input type="submit" /></p>
</form>
__FORM;
}
else {
$name = $_POST['name'];
$id = $_POST['id'];
$grade = $_POST['grade'];
echo <<<__WELCOME
Dear $name, with ID $id. Welcome, your grade is $grade.
__WELCOME;
}
?>
Note:
3. PHP Database Programming
4. MySQLi
Example:
<?php
/* Connect to a MySQL server
Should be put in a separate configuration file.
*/
$conn = mysqli_connect(
'localhost', /* MySQL server host */
'username', /* Username */
'password, /* password */
'database'); /* The default database schema */
if (!$conn) {
printf("Can't connect to MySQL Server. Errorcode: %s\n", mysqli_connect_error());
exit;
}
/* Using a MySQL like procedureal style. */
/* Submit a query */
if ($result = mysqli_query($conn, 'SELECT SNUM, SNAME, SCITY, STATUS FROM Supplier')) {
echo "<table border='1'>\n";
echo "<tr><td>SNum</td><td>Supplier Name</td><td>City</ed><td>Status</td></tr>\n";
/* Fetch results */
while ( $row = mysqli_fetch_assoc($result) ){
echo "<tr><td> " . $row['SNUM'] . "</td><td>" . $row['SNAME'] . "</td><td>" . $row['SCITY'] . "</td><td>" .
$row['STATUS'] . "</td></tr>\n";
}
echo "</table>\n";
/* Destroy the result set and free the memory used for it */
mysqli_free_result($result);
}
mysqli_close($conn);
?>
Note:
Example:
<?php include('dbconfig.php') ?>
<?php
/* Using a MySQL like procedureal style. */
/* Submit a query */
if ($result = mysqli_query($conn, 'SELECT SNUM, SNAME, SCITY, STATUS FROM Supplier')) {
echo "<table border='1'>\n";
echo "<tr><td>Supplier Num</td><td>Supplier Name</td><td>City</ed><td>Status</td></tr>\n";
/* Fetch results */
while ( $row = mysqli_fetch_assoc($result) ){
echo "<tr><td> " . $row['SNUM'] . "</td><td>" . $row['SNAME'] . "</td><td>" . $row['SCITY'] . "</td><td>" .
$row['STATUS'] . "</td></tr>\n";
}
echo "</table>\n";
/* Destroy the result set and free the memory used for it */
mysqli_free_result($result);
}
mysqli_close($conn);
?>
Using classes
Example:
<?php
/* Connect to a MySQL server
Should be put in a separate configuration file.
*/
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if (mysqli_connect_errno()) {
printf("Can't open MySQL connection. Error code: %s\n", mysqli_connect_error());
exit;
}
/* Using an OO style. */
/* Submit a query */
if ($result = $mysqli->query('SELECT SNUM, SNAME, SCITY, STATUS FROM Supplier')) {
echo "<table border='1'>\n";
echo "<tr><td>SNum</td><td>Supplier Name</td><td>City</ed><td>Status</td></tr>\n";
/* Fetch results */
while ( $row = $result->fetch_assoc() ){
echo "<tr><td> " . $row['SNUM'] . "</td><td>" . $row['SNAME'] . "</td><td>" . $row['SCITY'] . "</td><td>" .
$row['STATUS'] . "</td></tr>\n";
}
echo "</table>\n";
/* Destroy the result set and free the memory used for it */
$result->close();
}
$mysqli->close();
?>
Note:
Using Prepared Statements
Example:
<?php include('dbconfig.php') ?>
<?php
/* Primitive program: Get HTTP parameters */
if (!array_key_exists('city', $_GET)) {
echo "Please specify the city name with the URL parameter city.";
exit;
}
$icity = $_GET['city'];
if (!array_key_exists('status', $_GET)) {
echo "Please specify the minimum status with the URL parameter status.";
exit;
}
$istatus = $_GET['status'];
if ($stmt = $mysqli->prepare("SELECT SNUM, SNAME, SCITY, STATUS FROM Supplier WHERE SCITY = ? and Status >= ?")) {
$stmt->bind_param('ss', $icity, $istatus);
/* execute prepared statement */
$stmt->execute();
$stmt->bind_result($snum, $sname, $city, $status);
$stmt->store_result();
if ($stmt->num_rows > 0) {
echo"<p>Result: Suppliers in $icity with status > $istatus:</p>\n";
echo "<table border='1'>\n";
echo "<tr><td>Supplier Num</td><td>Supplier Name</td><td>City</td><td>Status</td></tr>\n";
/* fetch values */
while ($stmt->fetch()) {
echo "<tr><td> " . $snum . "</td><td>" . $sname . "</td><td>" . $city .
"</td><td>" . $status . "</td></tr>\n";
}
echo "</table>\n";
/* Destroy the result set and free the memory used for it */
$stmt->free_result();
}
}
else {
echo "No result for $icity with status > $istatus.\n";
}
$mysqli->close();
?>
Notes:
Some advantages of prepared statements:
Some disadvantages of prepared statements:
Conclusions: