A PHP/MySQL Example

[article] [edit page] [discussion] [history]

From Humanitarian-FOSS Project Development Site

This page describes a simple exercise that uses PHP and MySQL to create a form.


Contents

Prerequisites

This exercise assumes you have installed the following software:

  • PHP
  • MySQL
  • PhpMyadmin

Problem Statement

This problem will extend the PHP program we wrote in a previous exercise by using a MySQL table to store the light bulb jokes. 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.

Write a PHP program that will display a titled web page that presents the jokes in an HTML table:

Light Bulb Jokes

(Generated from Workshop Database by jokestable.php)

SubjectPunchline
AcademicsNone. That's what students are for.
AccountantsWhat answer did you have in mind?
Computer ProgrammersTwo. One always leaves in the middle of the project.

Setting up the Database: Hints and Help

  • Create the MySQL database.
    • Go to the PhpMyadmin page and use the Create database textfield. Let's name the DB workshop.
  • Create the jokes table.
    • In PhpMyadmin, select the workshop DB by clicking on it. This should bring up a Create table screen.
    • Fill in the table's name (jokes) and number of fields (2) and click Go. This will bring a page that allows you to fill in the names of the fields (subject, and punchline) and their attributes (all VARCHAR length 50).
    • Click the Save button to create the table.
  • Use PhpMyadmin to populate the table with sample data.
    • Click on the table you wish to populate.
    • Then click on "Insert". This will bring up an input form.
    • Click on Go to save the data.
  • NOTE: If time does not permit creating the database interactively, copy the following Workshop_DB_Script and paste it into PhpMyadmin's SQL query window. Then click Go. This will create a database named workshop.

Writing the PHP Script: Hints and Help

 <?php
 /* $Id: members.php  */
 /**
  *  Initial PHP Doc comment block describing the program, authors, license, etc.
 */
 ?>
 <HTML>
 ... HTML Code to display page title and table header
  <?
  // PHP Code to generate the table data
  >?
 ... Rest of HTML code
 </HTML>
  
  • A function to report MySQL errors:
  function showerror()
  {
     die("Error " . mysql_errno() . " : " . mysql_error());
  }
  • Open a connection to the MySQL server (you need to provide the correct root password)
  if (!($connection = @ mysql_connect("localhost", "root", "test1234")))
     die("Could not connect");
  • Select the database you created with PhpMyadmin:
  if (!(@ mysql_select_db("workshop", $connection)))
     showerror();
  • Query the DB to get a list of workshop members. This query will return a result that contains an array of rows:
 if (!($result = @ mysql_query ("SELECT * FROM jokes", $connection)))
     showerror();
  • Write a loop to display the light bulb jokes. Each iteration fetches one row of the result array:
 while ($row = @ mysql_fetch_array($result, MYSQL_NUM))
  {
    $subject =  $row[0];
    $punchline = $row[1];
    print "<tr><td>{$subject}</td><td>{$punchline}</td></tr>";
  }

Solution

Click here to reveal a complete Solution.

Personal tools