A PHP/MySQL Example with HTML Forms

<!DOCTYPE HTML PUBLIC
                 "-//W3C//DTD HTML 4.01 Transitional//EN"
                 "http://www.w3.org/TR/html401/loose.dtd">
<?php
/* $Id: jokestable.php  */
/**
 *
 * This program  displays a table of light bulb jokes, generated from  a MySQL database.
 *
 * This file gives a solution to the following exercise:
 * Create a MySQL database that contains one table. The jokes table stores the light
 * bulb jokes. For each joke, the table stores the joke's subject (e.g., professors,
 * computer scientists) and the joke's punchline. Populate the table with sample data.
 *
 * This version adds two forms to the script, one to filter the jokes
 *  by category (e.g., philosopher jokes) and the other to insert new
 *  jokes into the database.
 *
 * Subject  Punchline
 *
 * @author     R. Morelli <ralph.morelli@trincoll.edu>
 * @version    1.0
 * @package    default
 * @license    http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License (LGPL)
 *
 */
?>
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  <title>Light Bulb Jokes</title>
</head>
<body>
<center>
<h2>Light Bulb Jokes</h2>
<h3>(Generated from Workshop Database by jokestable.php)</h3>
<table border=1>
<tr><th>Subject</th><th>Punchline</th></tr>
<?php

/**
 * Display's an error message after a faulty DB operation
 *
 * @access public
 * @return void
 */
   function showerror()
   {
      die("Error " . mysql_errno() . " : " . mysql_error());
   }

// Here starts the main program.

   // (1) Open the database connection
   if (!($connection = @ mysql_connect("localhost", "root", "test1234")))
      die("Could not connect");

   // (2) Select the workshop database
   if (!(@ mysql_select_db("workshop", $connection)))
      showerror();

   // (3) Run a query through the connection getting all rows of the  jokes table

   $newsubject= htmlentities($_GET["subject"]);
   $punchline = htmlentities($_GET["punchline"]);

   if ($newsubject != '' && $punchline != ''){
   	$query = "INSERT INTO jokes (subject,punchline) VALUES ('$newsubject', '$punchline')";
    if (!($result = @ mysql_query ($query, $connection)))
      showerror();
   }

   $category = htmlentities($_GET["category"]);
   if($category === '') {
   	 $query = "SELECT * FROM jokes";
   } else {
   	 $query = "SELECT * FROM jokes WHERE subject='$category'";
   }

   if (!($result = @ mysql_query ($query, $connection)))
      showerror();

   // (4) While there are still rows in the result set, fetch the current row into the array $row
   while ($row = @ mysql_fetch_array($result, MYSQL_NUM))
   {
     // (5) Get the data from the fields of the jokes table, the first field is $row[0], etc.
     $subject =  $row[0];
     $punchline = $row[1];

     // (6) Display one row of the table
     print "<tr><td>{$subject}</td><td>{$punchline}</td></tr>";
   }
?>
</table>
<center>

<?php
$self = htmlentities($_SERVER['PHP_SELF']);
    echo ('<BR><h4>Jokes Filter Form</h4>
    <FORM ACTION="'.$self.'"  METHOD="GET">
    <LABEL>
        Joke category: <INPUT TYPE="TEXT" NAME="category" />
    </LABEL>
    <INPUT TYPE="Submit" VALUE="Go!" />
    </FORM>');

        echo ('<BR><h4>Input Joke Form</h4>
    <FORM ACTION="'.$self.'"  METHOD="GET">
    <LABEL>
        Subject: <INPUT TYPE="TEXT" NAME="subject" />
    </LABEL>
    <LABEL>
        Punchline: <INPUT TYPE="TEXT" NAME="punchline" />
    </LABEL>
    <INPUT TYPE="Submit" VALUE="Go!" />
    </FORM>');

?>

</body>
</html>

<!-- Below is a mysql script to generate the database and associated table
-- phpMyAdmin SQL Dump
-- version 2.11.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 17, 2008 at 05:42 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `workshop`
--
CREATE DATABASE `workshop` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `workshop`;

-- --------------------------------------------------------

--
-- Table structure for table `jokes`
--

CREATE TABLE IF NOT EXISTS `jokes` (
  `subject` varchar(50) NOT NULL,
  `punchline` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `jokes`
--

INSERT INTO `jokes` (`subject`, `punchline`) VALUES
('Academics', 'None. Thats what students are for.'),
('Accountants', 'What answer did you have in mind?'),
('Computer Programmers', 'Two. One always leaves in the middle of the project.');
Copy above this line  -->