XHTML,
PHP & MySQL:
Putting it all together
 
documentation XHTML tags

PHP Documentation

MySQL Documentation

PHP Documentation regarding MySQL

Putting it all
together

How Web Database Architecture Works:

characters:
 csci304zimmer.cs.edinboro.edu - web server
  form.htm - web (HTML) form (file containing form)
  processForm.php - php file to process submitted data
                                from form.htm

An end-user's web browser issues an HTTP request to a web server ("csci304zimmer.cs.edinboro.edu") for a particular web page ("form.htm").

The web server ("csci304zimmer.cs.edinboro.edu") sends the web page ("form.htm") to the end-user's web browser and the browser interprets the HTML codes to display the page in the browser window.

The end-user fills in the form fields and submits the form issuing an HTTP request to a web server ("csci304zimmer.cs.edinboro.edu") to run a CGI script ("processForm.php").

The web server ("csci304zimmer.cs.edinboro.edu") starts the CGI script ("processForm.php").  

The CGI script ("processForm.php") opens a connection with the MySQL server and sends the MySQL commands.

The MySQL server receives the MySQL commands, processes them, and sends results back to the CGI script ("processForm.php").

The CGI script ("processForm.php") interprets the results and creates an HTML document to submit to the web server ("csci304zimmer.cs.edinboro.edu").

The web server ("csci304zimmer.cs.edinboro.edu") sends the CGI generated web page to the end-user's web browser and the browser interprets the HTML codes to display the response page in the browser window.

accessing a db from
a CGI script
  1. Retrieve the data from the end-user form
  2. Check and filter the data coming from the end-user form
  3. Set up the database queries to execute
  4. Connect to the database server
  5. Connect to the appropriate database
  6. Execute a database query
  7. Retrieve the results
  8. Prepare retrieved data for XHTML processing
  9. Present the results back to the end-user as an XHTML web page
  10. Repeat steps 6-9 until all form processing is complete
Create a login file

Prepare login file

  • Login file creates the values for the variables to connect to the MySQL server and to the database.
  • The file should be located outside the public_html if possible and have proper security inplace.
  • The file should be named something inconspicuous.

<?    // login.php
$db_hostname ='      ';
$db_database ='      ';
$db_username ='      ';
$db_password ='      ';
?>

 

retrieve the data

Retrieve the data sent from the end-user form:

  • Must know the names of each of the fields in the end-user form
  • Must know the values or types of values sent by the form
  • Should use the $_POST array (an associative array that is indexed using the field names from the form) to retrieve the values sent from the form.
  • Each form value can be copied into a php variable for further processing

$name = $_POST['name'];
$bdate = $_POST['birthdate'];
$subject = $_POST['subj'];
$gender = $_POST['gender'];
$ext = $_POST['phone_extension'];

Check and Filter data
 

Prepare submitted data for the database:

mysql_real_escape_string($variable) - (value returning) strips out characters that hackers may insert to hack into the system. **the database server must be connected for this to work **

trim($variable) - (value returning) removes all whitespace in front and after value contained in $variable.

get_magic_quotes_gpc( ) - returns true if magic quotes are active which means that some characters have been "escaped" prior to retrieval into the program.  This is deprecated - but something you should know.  If this is true then you want to remove all those inserted slashes using:

stripslashes($variable) - (value returning) removes slashes in $variable

htmlentities($variable) - (value returning) strips out the html code so it does no damage

if (!$variable1  || !$variable2 ...) - checks whether the variables have a value

addslashes($variable) - (value returning)"escapes" any special characters for MySQL (ex: " (double quote) becomes \" )

 

Prepare data retrieved from the database to be put into XHTML:

$variable = stripslashes($variable) - removes slashes used by MySQL for display in HTML ( \" becomes " )

$variable = htmlspecialcharacters($variable) - "encodes" special characters for use in HTML ( < becomes &lt )
 

connecting to mysql
from php

$db_server = mysql_connect( "<host>", "<uname>", "<passwd>");  
               //connects to MySQL

if (!$db_server)
  die (" ERROR connecting to db.  Please try again later. ");

mysql_select_db(<database name>);      // connects to a db

mysql_close( $db_server);

 

Example:

require_once 'login.php';
$db_server = mysql_connect($db_hostname,
                                          $db_username,
                                          $db_password);
if (!db_server)
      die("unable to connect to MySQL:"
                                  . mysql_error());

// Connect to the DB
mysql_select_db($db_database)
          or die("Unable to connect to database: "
                                  . mysql_error());

mysql_close( $db_server); // close connection

executing a
mysql command
from php

$query = <valid MySQL query>;

$results = mysql_query($query);  
              // queries the open database

        or

$results = mysql_db_query($db_database, $query, $db_server);  
              // don't need to select the db from above
 

Example:

$query ="SELECT * FROM courses";
$results = mysql_query($query);

or

$query = "INSERT INTO courses VALUES ('csci125', 2,'Bennett', 3)";
$results = mysql_query($query);
 

Process a simple
db query

$results = mysql_query($query);  
              // queries the open database

$num_results = mysql_num_rows($results);

    for ($i=0; $i<$num_results; $i++)
    {
        // process the results

        $row = mysql_fetch_array($results); 
                 //assigns a row to $row

        print ("<br> $row["<fieldname>"]...<br>");
    }
 

Example:

$query ="SELECT * FROM courses";
$results = mysql_query($query);
$num_results = mysql_num_rows($results);
for ($i=0; $i<$num_results; $i++)
{
    $row = mysql_fetch_array($results); 
    print ("<br> $row[num] $row[instr]...<br>");
}
 

Process results from
adding to a db table

$results = mysql_query($query);  
              // queries the open database

$num_results = mysql_affected_rows(); 

    if ($results)
    {
        print("<br>$num_results record was added.<br>\n");
     }

 

Example:

$query = "INSERT INTO courses VALUES ('csci125', 2,'Bennett', 3)";
$results = mysql_query($query);
$num_results = mysql_affected_rows( );
if ($results)
{
    print("<br>$num_results rows added.<br>\n");
}
 

 

Try This:   Example 1

Create a PHP script (faculty_ex1.php) that requests the data from the faculty table and displays it.

answer -> click here

 

Try This:  Example 2

Create an XHTML form (faculty_ex2.htm) that requests data for the faculty table (have an entry for each field- use the correct field element).  Submit the data to a php file named faculty_ex2.php.  Here is what the table looks like and some sample data:

name

bdate

subj

gender

ext

Hillman

1950-01-13

CS

F

2760

Tucker

1965-06-30

CS

M

2550

Sylvester

1962-03-04

MATH

M

1187

answer -> click here

Write a PHP script (faculty_ex2.php) that  updates the faculty table with the submitted data from the form.

answer -> click here

Once you have added a few new records, use the faculty_ex1.php to display the entire faculty table

 

add some Javascript

Try This:  Example 3

Create a new form (faculty_ex2_js.htm) that adds some validation javaScript to the form from the last example(faculty_ex2.htm).  Require the fields: facultyName, facultyBdate, facultyExt.  Insist the facultyExt contains only numbers and the facultyName contains no numbers.

 answer -> click here