Script School Classroom

School Map:  Home / Enroll $ Student Records; Class  
$ News @ LIVE Tech Radio * Support/FAQ | Store | FORUMS


Script School - MySQL 101 - Week #4

Review and Preview So far we have created our database, established our Tables, and developed forms and scripts to put data into the Websites and Sponsors Tables.  In this weeks class, we will examine SELECTING data from our database, displaying it, and using it to populate a form to put data into our Website_Sponsor Table - the Table that ties our project together. We'll also take a look at UPDATING and DELETING data. We'll also learn how to put data into ORDER, LIMIT how many records we pull, GROUP by shared properties, and see how the terms HAVING, WHERE, AND, OR, and LIKE, are used. And a few other terms and functions.  This is the week where the real power of databases will become apparent.  Buckle in for a wild ride!

SELECT The query that allows us to get data from our database is called SELECT.
A simplified syntax for it is:

Select Syntax

SELECT field(s)
FROM table(s)
[ WHERE conditional ] 
[ GROUP BY property ]
[ HAVING criteria ]
[ ORDER BY qualifier ]
[ LIMIT arguments ];

The terms in square brackets are optional. 

An asterisk * can be used for fields as a wildcard meaning ALL.
This will produce all the data in all the fields in every Record in the Table: 

$query = "SELECT * FROM `Sponsors`;";

You might recall from last week that backticks ` ` are used within queries to avoid name conflicts with reserved MySQL words.  The inner semicolon ; in query strings within PHP are not required, but is often included for compatibility with other database systems.

field(s) may be designated by the table they are on with dot syntax, as in: Websites.w_id  This may be necessary when fields on two different tables have the same name.  Dot syntax is unnecessary when there is no ambiguity as far as which table the field is on.

FROM table(s) implies multiple tables may be listed. When more than one table is listed, it is known as a JOIN.  Table names may be separated with commas like field(s) are, or with special key words.  We'll discuss JOINS in greater detail later in this weeks class.

WHERE enables the use of conditionals which compare two values using one of the following operators:

Operator Definition
= Equal
!= or <> Not Equal
> Greater Than
< Less Than
>= Greater Than Or Equal
<= Less Than Or Equal
IS NULL Returns Values that are Null
IS NOT NULL Returns Values that are not Null
BETWEEN Returns 1 (true) if Value is between two Values
IN

Return Values that are in a set,
subqueries after version 4.1

NOT IN Returns Values not in a set,
subqueries after version 4.1
LIKE Basic string matching, allows wildcard %
NOT LIKE Not in a basic match
REGEXP Uses regular expression syntax for matching

Conditionals using comparison and logical operators return: 1 (true) or 0 (false) or NULL.
True results allow the query to return the selected Fields. String operators return strings.

Examples:

SELECT w_id, w_name WHERE w_id = ws_w_id;
SELECT s_id, s_name WHERE s_id = ws_s_id;
Note: ws_w_id and ws_s_id would be a literal values, in this case, numbers. They are shown in these examples to indicate the relationships.

Here are several other examples of the WHERE clause:

SELECT ...

WHERE field < 100;
WHERE field IS NULL
WHERE field IS NOT NULL
WHERE 'b' BETWEEN 'a' AND 'c'; returns 1
WHERE 5 BETWEEN 2 AND 4; returns 0
WHERE 2 IN (0,3,5,'wefwf'); returns 0
WHERE 2 IN (0,3,5,'wefwf', NULL); returns NULL
WHERE w_name LIKE ("A%"); returns names like: Alice, Ann, Amy, Andy, Allen. If in Table.

You may notice the use of AND in the BETWEEN syntax above.  AND is also used to 'join' multiple WHERE conditions in a query, it will be used this way in script examples.  OR can also be applied between WHERE conditionals.  Parentheses ( ) can be used to enforce the order of operations.

GROUP BY Enables grouping of Records where they share common values in a Field. This allows the following common Group By functions to be applied to the resulting groups:
(see link for additional functions)

Function Description
COUNT()

When COUNT(field) count of non-null values per set
When COUNT(*) count of all values per set
When COUNT(DISTINCT field) count of unique non-null values per set
AVG() Average value of each resulting set
MIN() Minimum value in each resulting set
MAX() Maximum value of each resulting set
SUM() Sum of the values of each resulting set
STD() or STDDEV() Standard Deviation of each resulting set

These functions are actually used in the SELECT query where fields would normally be.  In effect the results obtained become virtual fields. 

Examples:

SELECT city, MAX(sales), AVG(sales), SUM(sales) FROM sales_table GROUP BY city;

SELECT route, time, COUNT(riders), COUNT(stops) FROM bus_routes GROUP BY route, time;

Notice in the second example above that there are two fields that are being grouped.  The results are gathered by the first field, and then the second field is gathered within each of the first fields. This hierarchical pattern applies to any number of fields.  In this example, each gathering of results would show route, time, rider count and number of stops.  The grouping is formed on rows that contain the same values in the GROUP BY fields.

A special case exists for COUNT(*) when no WHERE or GROUP BY is used which gives a very fast count of all the rows in the table:

SELECT COUNT(*) FROM mytable;

HAVING Allows You to apply further filtering to results obtained in GROUP BY:

SELECT route, time, riders FROM bus_routes GROUP BY route HAVING AVG(riders > 20);

ORDER BY Lets You sort by alphabet or number according to field(s).  Ascending is the default, which can be specified with ASC.  If You want the results in reverse or descending order DESC can be used:

SELECT price FROM items ORDER BY price DESC;

SELECT age, name FROM patients ORDER BY age, name;

The second example would first sort by age and then within each age, sort by name.

LIMIT Enables You to restrict the number of rows returned by the query.  If used with a single number it returns that number of rows.  If two arguments are given the first determines the start point, the second the number of rows to return.  Row count starts at zero.

SELECT items FROM table LIMIT 5; 
returns a maximum of 5 rows from table

SELECT items FROM table LIMIT 4, 10; 
returns a maximum of 10 rows starting at the fifth row (row count starts at zero)

Using PHP to SELECT from the database:

When PHP is used to SELECT from MySQL, the data is usually obtained from the result a Record or Row at a time, with all of the Fields of each Row placed into an Array or Object.  It is also possible to get individual Fields, although this is slow and generally not recommended.  In the following script, we connect to the MySQL server and the database, create and run SELECT queries, extract and display the result a row at a time.  We'll be using these queries again to populate the option lists in the form used to INSERT into the Website_Sponsor Table.  Note that the HTML <pre> tag uses plain text formatting: \n new line, \r return, \t tab.  We're using <pre> to simplify this example. 

This script produces results similar to this example. 

<?php
// Connect to Your MySQL server (use YOUR parameters):
// Note: @ suppresses standard error reporting
$db = @ mysql_connect("host","username", "password")      
  or die("Could not connect:". mysql_error()); 
echo "Connected Successfully to MySQL Server.\n\r";
          
// Connect to database: 
@ mysql_select_db("ssMySQLclass",$db)      
  or die("Could not Connect to database:". mysql_error()); 
echo "Connected to Database.\n\r\n\r";
 
// Begin basic text Formatting:
echo "<pre>";
 
// Show Field Titles:  
echo "\t  Websites ID | Websites Name \n\r";  

// Create Websites query:         
$query_Websites = "SELECT w_id, w_name 
           FROM `Websites`;";
   
// Do query, obtain $result:         
$result = mysql_query($query_Websites, $db);

// Get and display data one row at a time:  
while($row = mysql_fetch_array($result)){
   echo "\t\t".$row['w_id']."\t ".$row['w_name']."\n\r";
} 
echo "\n\r"; 
  
// Create Sponsors query:         
$query_Sponsors = "SELECT s_id, s_name 
           FROM `Sponsors`;"; 
  
// Do query, obtain $result:         
$result = mysql_query($query_Sponsors, $db);
     
// Show Field Titles:  
echo "\t  Sponsors ID | Sponsors Name \n\r";
 
// Get and display data one row at a time:  
while($row = mysql_fetch_array($result)){
   echo "\t\t".$row['s_id']."\t ".$row['s_name']."\n\r";
}

// End Formatting: 
echo "</pre>";
        
?>
Save from <?php to ?> inclusive as dataview.php

Using PHP to INSERT into the database

Insert Syntax

INSERT INTO table
SET field = value,
field = value
;

Our goal is to produce a form to INSERT data into the Website_Sponsor Table to relate Websites to Sponsors. The form which will consist of two option lists composed of names and associated id's, from the Websites and Sponsors Tables.  When the user selects a Website and a Sponsor and submits the form, a new relationship is created in the database.  We'll do an intentional check to see if the relationship already exists to avoid duplication. Then we'll SELECT our data from the Websites and Sponsors Tables, and INSERT the data into the Website_Sponsor Table, instead of merely displaying it as we did in the script above. We could have automatically disallowed a duplicate insert simply by making both fields in the Website_Sponsor Table into a Unique or Primary Index, but when an INSERT did not occur we would not have been sure why it did not occur - it could have been due to a different problem.  By doing an intentional check, we can be sure that an unwanted duplication was avoided.

Note: This Form is shown as an example ONLY - Do NOT copy it - use full code below.

Add Website_Sponsor Relationships
Websites Sponsors
The following script creates the Add Relationships interface: 
<?php
// functions:

// sel() selects list option
function sel($row_id,$frm){
  // see which form
  if($frm == 'w'){
    // see if id matches posted value
    if($row_id == $_POST['Website_list']){
      echo 'selected';
    }
    // see which form
  }else if($frm =='s'){
    // see if id matches posted value
    if($row_id == $_POST['Sponsor_list']){
      echo 'selected';
    }
  }
}//End function sel()
function report_status($status){
  switch($status){
    case 'qry_ins' : 
      echo '<font color=red>Error Inserting into Database';
      break;
    case 'qry_ck' : 
      echo '<font color=red>Error Checking for Relationship';
      break;
    case 'rel_ex' : 
      echo '<font color=red>This Relationship Already Exists';
      break;
    case 'rel_add' :
      echo '<font color=blue>Added Relationship to Database';
      break;
    case 'empty_w' :
      echo '<font color=red>No Website Selected'; 
      break;
    case '_empty_s' :
      echo '<font color=red>No Sponsor Selected';
      break;
    // from adding '_empty_s' to $status string 
    case 'empty_w_empty_s' :
      echo '<font color=red>No Website OR Sponsor Selected';
      break;
  }//End switch
  if(isset($status)){
    echo '<br><br></font>';
  }
}//End function report_status()

// Logic Sequence:

// Connect to Your MySQL server (use YOUR parameters):
// Note: @ suppresses standard error reporting
$db = @ mysql_connect("host","username", "password")
  or die("Could not connect:". mysql_error()); 
// Connect to database: 
@ mysql_select_db("ssMySQLclass",$db) 
  or die("Could not Connect to database:". mysql_error());
 
// Determine if Form has been submitted: 
if(isset($_POST['Submit_WS'])){
  // Check if values exist from both lists:
  if(!empty($_POST['Website_list']) && !empty($_POST['Sponsor_list'])){
    // Write query, determine if Duplicating Relationship:
    $query_check = "SELECT * FROM Website_Sponsor
      WHERE ws_w_id = '".$_POST['Website_list']."' 
      AND ws_s_id = '".$_POST['Sponsor_list']."'
      ;";
    // Do Duplicate query:
    if($result = @mysql_query($query_check, $db)){
      // If Number of Rows is one or more...
      // Note that mysql_num_rows is used for SELECT queries
      if(mysql_num_rows($result) >= 1){
        // This Relationship already exists in the database:
        $status = 'rel_ex';
      }else{ // OK to Insert data...
        // Write Insert query:
        $query_insert = "INSERT INTO `Website_Sponsor`
          SET ws_w_id = '".$_POST['Website_list']."',
          ws_s_id = '".$_POST['Sponsor_list']."'
          ";
        // Do query, check if one row affected:
        // Note that mysql_affected_rows is used for INSERT queries
        if($result = @mysql_query($query_insert,$db)
                    && @mysql_affected_rows() == 1){ 
          // Report success:
          $status = 'rel_add';
        }else{
          // Error Inserting Result into database
          $status = 'qry_ins';
        }//End Do Insert query /else Error
      }//End else OK to Insert data 
    }else{//Duplicate query 
      // Error in getting result from query
      $status = 'qry_ck';
    }//End else Duplicate query 
  }else{//End check for both list values
    // One or Both Lists NOT selected:
    if(empty($_POST['Website_list'])){
      $status = 'empty_w';
    }
    if(empty($_POST['Sponsor_list'])){
      // Note use of the .= to add string to $status variable
      $status .= '_empty_s';
    }
  }//End else check for both list values 
}//End if Form Submitted 
?>
<html>
<head>
<title>Website_Sponsor Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form name="Website_Sponsor" method="post" 
      action="<?php echo $_SERVER['PHP_SELF']; ?>">
<table width="497" border="1" align="center" 
        cellpadding="2" cellspacing="1" bordercolor="#0000FF"
        bgcolor="#99CCFF" rules="none">
  <caption>
    <strong>Add Website_Sponsor Relationships</strong> 
  </caption>
  <tr align="center" valign="middle"> 
    <td width="240"><strong>Websites</strong></td>
    <td width="240"><strong>Sponsors</strong></td>
  </tr>
  <tr align="center" valign="middle"> 
    <td>
    <select name="Website_list" size="10" id="Website_list">
    <?php 
    // Create Websites query: 
    $query_Websites = "SELECT w_id, w_name FROM `Websites`;";
    // Do query, obtain $result: 
    $result = mysql_query($query_Websites, $db);
    // Get and display data one row at a time: 
    while($row = mysql_fetch_array($result)){
      // Create option, use sel() to show if selected: 
      echo '<option value="'.$row['w_id'].'"'; sel($row['w_id'],'w'); 
      echo'>'.$row['w_name'].'</option>';
    } 
    ?>
    </select>
    </td>
    <td>
    <select name="Sponsor_list" size="10" id="Sponsor_list">
    <?php
    // Create Sponsors query: 
    $query_Sponsors = "SELECT s_id, s_name FROM `Sponsors`;"; 
    // Do query, obtain $result: 
    $result = mysql_query($query_Sponsors, $db);
    // Get and display data one row at a time: 
    while($row = mysql_fetch_array($result)){
      // Create option, use sel() to show if selected:
      echo '<option value="'.$row['s_id'].'"'; sel($row['s_id'],'s'); 
      echo'>'.$row['s_name'].'</option>'; 
    } 
    ?> 
    </select>
    </td>
  </tr>
  <tr align="center" valign="middle"> 
    <td colspan="2">
    <?php 
    report_status($status);
    ?> 
      <input type="submit" name="Submit_WS" 
             value="&laquo; Add New Relationship &raquo;">
    </td>
  </tr>
</table>
</form>
</body>
</html>
Save the above page as insert_ws.php

Getting data from multiple Tables - JOINS

Joins allow You to get data from more than one table.  Tables are joined by their key fields which allow records to be associated by sharing a common id.  WHERE clauses allow You to filter results - if You don't have any WHERE clauses, You will wind up with what is usually a problematic result - every record on every table in the Join is associated with every record on every other table... normally quite useless.  Earlier we took a look at the following example:

SELECT w_id, w_name WHERE w_id = ws_w_id;

By providing a literal (in this case numerical) value for the ws_w_id, we could find the record(s) in the Websites Table that have that number in their w_id field.  Although in the context of our database design, we would only be returning one record from this query, in other circumstances, such as the Website_Sponsor Table, we could be finding potentially many records from one value.  A One to One or a One to Many relationship.

We do not need literal values, if we alter the query slightly:

SELECT w_id, w_name FROM `Websites`,`Website_Sponsor` WHERE w_id = ws_w_id;

Now the join will be made from the two Tables we included.  It will return the listed fields, w_id and w_name, in the records that match the WHERE condition. Only those records that have matching id's in both of the Tables are retrieved.  Data will only be retrieved if there are records in the Website_Sponsor Table that have ws_w_id values that match w_id values in the Websites Table.

Lets consider the three Table join we will be using for our Delete interface for the Website_Sponsor Table:

$query = "SELECT `ws_w_id`,`ws_s_id`,`w_name`,`s_name`
                FROM `Sponsors`, `Websites`, `Website_Sponsor`
                WHERE `s_id` = `ws_s_id` AND `w_id` = `ws_w_id`
                ORDER BY `w_name`,`s_name`
               ";

We get the id's and the names FROM the Website_Sponsor Table and the Websites and Sponsors Tables respectively. The WHERE clause is applied to match the s_id to the ws_s_id AND the w_id to the ws_w_id,  We order the results alphabetically first by w_name then by s_name within each w_name category. When we provide a choice to sort on Sponsor Names instead, we consider them first. (see the Delete Relationships Interface script below)

LEFT JOIN  All of the Joins we have been discussing so far, when used with a WHERE clause are known as Inner or Equi Joins. Without the WHERE clause they are known as Cartesian or Full or Cross Joins - generally to be avoided.  Another type of Join is known as the Left and Right Join.  It is used to match Tables when key values in the conditional clause are absent from some of the records in one of the Tables.  We will use this type of Join to find Websites and Sponsors that don't have any relationships, in our final script of this week's class, our Display Script. It has two alternative syntaxes as follows:

Left and Right Join Syntax

syntax type 1:

SELECT fields
FROM table1 LEFT JOIN table2
ON table1.id = table2.id

SELECT fields
FROM table1 RIGHT JOIN table2
ON table1.id = table2.id

syntax type 2:

SELECT fields
FROM table1 LEFT JOIN table2
USING id
WHERE table2.id IS NULL

SELECT fields
FROM table1 RIGHT JOIN table2
USING id
WHERE table1.id IS NULL

The Table with the 'complete' records are on the LEFT or RIGHT respectively. And the other one has it's 'missing' fields filled in with NULL.  We can then find the NULL values to determine the appropriate records.  Notice the use of ON instead of WHERE in syntax type 1 and USING in syntax type 2.  In syntax type 2 the names of the id fields MUST be the same in both Tables, and You'll use the dot notation to designate the Tables.  For clarity in this database schema, we have avoided using the same field name on different Tables.  We'll be using the following LEFT JOIN query in the Display script:

$query_list = "SELECT DISTINCT s_name AS name, s_id AS id FROM `Sponsors`
LEFT JOIN `Website_Sponsor` ON  s_id = ws_s_id
WHERE ws_s_id IS NULL AND s_status = 'Active'  ";

AS  We are using another tool, the AS alias to generalize the name and id for use in the Display script.  Before we get to that, lets see how to use DELETE to remove relationships from the Website_Sponsor Table:

Using PHP to DELETE data from the database

Delete Syntax

DELETE FROM table_name
[WHERE conditional]
[ORDER BY qualifier]
[LIMIT arguments];

Basic DELETE syntax is shown above. In MySQL versions 4.0 and above deletes from multiple Tables are possible, see link to the manual page for details. If You leave out the WHERE clause, You will delete ALL of the records from that Table!!!   Be careful with this! ORDER BY is generally used with LIMIT, for example, to delete records organized by a timestamp.

We'll take a look at deleting relationships from the Website_Sponsor Table. Obviously, before permanently deleting records from the Websites or Sponsors Tables we would first have to be sure that none of the websites or sponsors (records) to be deleted were still active within relationships.  Perhaps You'll want to consider how to do this as an advanced assignment - all of the tools to do it are in this course.  Later we'll take a look at using our status field to pull various categories of relationships... and use our 'defunct' category as a means of removing records from active consideration, while leaving them intact for future use.

The following screen shot shows what our Delete Relationships interface looks like after a relationship has been deleted:

Delete Relationships Screen Shot

The following script creates the Delete Relationships interface: 
	
<?php
// Connect to Your MySQL server (use YOUR parameters):
// Note: @ suppresses standard error reporting
$db = @ mysql_connect("host","username", "password")
   or die("Could not connect:". mysql_error());
// Connect to database:
@ mysql_select_db("ssMySQLclass",$db)
   or die("Could not Connect to database:". mysql_error());
   
// Check to see if a delete link has been clicked, sending variables by GET:
if(isset($_GET['del_w']) && isset($_GET['del_s'])){
// Set up query:
  $query_delete = "DELETE FROM `Website_Sponsor`
                   WHERE ws_w_id = '".$_GET['del_w']."'
                   AND ws_s_id = '".$_GET['del_s']."'
                  ";
  // Send query, check affected rows:
  if(@ mysql_query($query_delete,$db) && @ mysql_affected_rows() == 1){
    // query sent, set display status:
    $status = '<font color="#009900">Relationship Deleted</font><br>';
  } else { // Problem sending query, set display status: 
    $status = '<font color="#FF0000">Problem Deleting Relationship</font><br>';
  }//End if(@ mysql_query
}//End if(isset
   
// Check if link has sent sorting choice by GET: 
// Note ternary conditional operator: A?B:C If A true do B else do C 
isset($_GET['sort']) ? $sort = $_GET['sort'] : $sort = 'w';
// set join queries depending on sorting choice:	
if($sort == 'w'){
  $query = "SELECT `ws_w_id`,`ws_s_id`,`w_name`,`s_name` 
            FROM `Sponsors`, `Websites`, `Website_Sponsor` 
            WHERE `s_id` = `ws_s_id` AND `w_id` = `ws_w_id` 
            ORDER BY `w_name`,`s_name`
           ";
}else if($sort == 's'){
  $query = "SELECT `ws_w_id`,`ws_s_id`,`w_name`,`s_name` 
            FROM `Sponsors`, `Websites`, `Website_Sponsor` 
            WHERE `s_id` = `ws_s_id` 
            AND `w_id` = `ws_w_id` 
            ORDER BY `s_name`,`w_name`
           ";
} 
?>
<html>
<head>
  <title>Script School MySQL101 Script3</title>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
  <table width="360" height="170" border="1" 
        align="center" cellpadding="0" cellspacing="0" 
        bordercolor="#000000" bgcolor="#FFFFFF">
    <caption>
      <strong>Delete Website_Sponsor Relationships</strong>
    </caption>
  <tr>
    <td align="left" valign="top">
    <div id="Layer1" style="position:relative; width:360px; 
        height:130px; z-index:1; overflow: auto;        
        background-color: #DDEEFF; layer-background-color: #DDEEFF;">
    <blockquote>
    <?php
    // send query for data:
    $result = mysql_query($query, $db);    
    // loop through data result
    while($row = mysql_fetch_array($result)){
      // create delete links to send id's through GET:
      echo '<a href='.$_SERVER['PHP_SELF'].'?    
  	del_w='.$row['ws_w_id'].'&del_s='.$row['ws_s_id'].'>'.$row['w_name'].
  	' &middot; '.$row['s_name'].'</a><br>';
    }
    ?>
    </blockquote>
    </div>
    </td>
  </tr>
  <tr> 
    <td height="40" align="center" valign="middle">
    <?php //display status: 
        echo $status; 
    ?> 
    Sort By:&nbsp;&nbsp;
    <!-- Create links to sort data -->
    <a href="<?php echo ''.$_SERVER['PHP_SELF'].'?sort=w' ?>">Websites</a> 
      &nbsp;&nbsp;&nbsp;
    <a href="<?php echo ''.$_SERVER['PHP_SELF'].'?sort=s' ?>">Sponsors</a> 
    </td>
  </tr>
</table>
</body>
</html>

Displaying Results

The following 50% 'thumbnails' show what our Display Interface will look like:

Display Interface Thumbnail
After using the Filter

Display Interface Thumbnail
After choosing MySite2 and clicking Display

Display Interface Thumbnail 3
After Filtering for Sponsors and clicking Display

FULLTEXT SEARCHES The last query type we will discuss before diving into our script is the Search query type.  It has the following syntax, items in square brackets are optional: 

Search Syntax

SELECT field [AS alias][, field AS alias]
FROM `Table` WHERE MATCH(field [,field])
AGAINST(search [IN BOOLEAN MODE])

Searches are best performed against a FULLTEXT Index, which we already discussed a bit when we were setting up our tables.  FULLTEXT Indexes can be applied to CHAR, VARCHAR and TEXT field types.  IN BOOLEAN MODE is available from MySQL version 4.0.1 It allows You to add the additional parameters within the search term: + Must be present, - Must not be present, < decrease relevance, > increase relevance, () to group terms, ~ negates relevance but still includes word, * wildcard, and quotes "  " allow an entire phrase to be searched for.  The Display script below does not include the IN BOOLEAN MODE command, You can add it if You are using MySQL version 4.0.1 or newer.  For detailed information about using this feature see this page in the MySQL manual.

Display Script 

Note: To copy this entire script easily,
 hold down the Shift key on Your keyboard while highlighting.


<?php
// set variables
$status = '<font size=1>&nbsp;</font>'; 

// functions
// checks radio buttons so Filter form shows query
// after page returns from server  
function check_radio($field, $val, $default){
  if($_POST[$field] == $val){
    echo ' checked ';
  }elseif(empty($_POST[$field]) && $default == $val){
    echo ' checked ';
  }
}
// Note: @ suppresses standard error reporting
// Connect to Your MySQL server (use YOUR parameters):
$db = @ mysql_connect("host","username", "password")
  or die("Could not connect:". mysql_error()); 
// Connect to database: 
@ mysql_select_db("ssMySQLclass",$db) 
  or die("Could not Connect to database:". mysql_error());
// Clean incoming data: 
if(get_magic_quotes_gpc()){
  foreach($_POST as $key => $value){
  // Note: Changing the values in the $_POST array:
  $_POST[$key] = mysql_escape_string(strip_tags(trim(stripslashes($value))));
  }
}
// check for submission from filter form, 
// set up query according to condition: 
if(isset($_POST['filter'])){
  // if Websites:
  if($_POST['web_spon'] == 'web'){
    // if all relationships:
    if($_POST['relations'] == 'all'){
      $query_list = "SELECT DISTINCT w_name AS name, w_id AS id 
                     FROM `Websites`";
      // add status conditional:
      if($_POST['status'] == 'Pending'){
        // Note use of the .=  to add to the $query_list string:
        $query_list .= " WHERE w_status = 'Pending'";
      }else if($_POST['status'] == 'Active'){
        $query_list .= " WHERE w_status = 'Active'"; 
      }else if($_POST['status'] == 'Inactive'){
        $query_list .= " WHERE w_status = 'Inactive'";
      }else if($_POST['status'] == 'Defunct'){
        $query_list .= " WHERE w_status = 'Defunct'";
      }else if($_POST['status'] == 'all'){
        $query_list .= "";
      }
    }else if($_POST['relations'] == 'yes'){
      $query_list = "SELECT DISTINCT w_name AS name, w_id AS id 
                     FROM `Websites`, `Website_Sponsor` 
                     WHERE w_id = ws_w_id";
      if($_POST['status'] == 'Pending'){
        $query_list .= " AND w_status = 'Pending'";
      }else if($_POST['status'] == 'Active'){
        $query_list .= " AND w_status = 'Active'";
      }else if($_POST['status'] == 'Inactive'){
        $query_list .= " AND w_status = 'Inactive'";
      }else if($_POST['status'] == 'Defunct'){
        $query_list .= " AND w_status = 'Defunct'";
      }else if($_POST['status'] == 'all'){
        $query_list .= "";
      }
    }else if($_POST['relations'] == 'no'){
      $query_list = "SELECT DISTINCT w_name AS name, w_id AS id 
                     FROM `Websites` 
                     LEFT JOIN `Website_Sponsor` 
                     ON w_id = ws_w_id 
                     WHERE ws_w_id IS NULL";
      if($_POST['status'] == 'Pending'){
        $query_list .= " AND w_status = 'Pending'";
      }else if($_POST['status'] == 'Active'){
        $query_list .= " AND w_status = 'Active'";
      }else if($_POST['status'] == 'Inactive'){
        $query_list .= " AND w_status = 'Inactive'";
      }else if($_POST['status'] == 'Defunct'){
        $query_list .= " AND w_status = 'Defunct'";
      }else if($_POST['status'] == 'all'){
        $query_list .= "";
      }
    }
  }else if($_POST['web_spon'] == 'spon'){ // if Sponsors
    if($_POST['relations'] == 'all'){
      $query_list = "SELECT DISTINCT s_name AS name, s_id AS id 
                     FROM `Sponsors`";
      if($_POST['status'] == 'Pending'){
        $query_list .= " WHERE s_status = 'Pending'";
      }else if($_POST['status'] == 'Active'){
        $query_list .= " WHERE s_status = 'Active'"; 
      }else if($_POST['status'] == 'Inactive'){
        $query_list .= " WHERE s_status = 'Inactive'";
      }else if($_POST['status'] == 'Defunct'){
        $query_list .= " WHERE s_status = 'Defunct'";
      }else if($_POST['status'] == 'all'){
        $query_list .= "";
      }
    }else if($_POST['relations'] == 'yes'){
      $query_list = "SELECT DISTINCT s_name AS name, s_id AS id 
                     FROM `Sponsors`, `Website_Sponsor` 
                     WHERE s_id = ws_s_id";
      if($_POST['status'] == 'Pending'){
        $query_list .= " AND s_status = 'Pending'";
      }else if($_POST['status'] == 'Active'){
        $query_list .= " AND s_status = 'Active'";
      }else if($_POST['status'] == 'Inactive'){
        $query_list .= " AND s_status = 'Inactive'";
      }else if($_POST['status'] == 'Defunct'){
        $query_list .= " AND s_status = 'Defunct'";
      }else if($_POST['status'] == 'all'){
        $query_list .= "";
      }
    }else if($_POST['relations'] == 'no'){
      $query_list = "SELECT DISTINCT s_name AS name, s_id AS id 
                     FROM `Sponsors` 
                     LEFT JOIN `Website_Sponsor` 
                     ON s_id = ws_s_id 
                     WHERE ws_s_id IS NULL";
      if($_POST['status'] == 'Pending'){
        $query_list .= " AND s_status = 'Pending'";
      }else if($_POST['status'] == 'Active'){
        $query_list .= " AND s_status = 'Active'";
      }else if($_POST['status'] == 'Inactive'){
        $query_list .= " AND s_status = 'Inactive'";
      }else if($_POST['status'] == 'Defunct'){
        $query_list .= " AND s_status = 'Defunct'";
      }else if($_POST['status'] == 'all'){
        $query_list .= "";
      }
    }//END else if($_POST['relations'] == 'no'){
  }//End else if($_POST['web_spon'] == 'spon'){ // if Sponsors
  $status = '<font size=1>Results from Filter</font>';
}//End if(isset($_POST['filter']))

// check for submission from search form: 
if(isset($_POST['search'])){
  $query_list = "SELECT w_name AS name, w_id AS id 
                 FROM `Websites` 
                 WHERE MATCH(w_name, w_url, w_desc) 
                 AGAINST('".$_POST['searchfield']."')
                ";
  $status = '<font size=1>Results from Search</font>';
}

// check for submission from display form:
if(isset($_POST['display'])){ 
  // if relationships:
  if($_POST['dtls_rltnshp'] == 'rltnshp'){
    // if Websites:
    if($_POST['web_spon'] == 'web'){
      $query_list = "SELECT s_name AS name, s_id AS id
                     FROM `Websites`,`Website_Sponsor`,`Sponsors` 
                     WHERE s_id = ws_s_id 
                     AND ws_w_id = w_id 
                     AND w_id = '".$_POST['list']."'
                    ";
      // Switch $_POST['web_spon'] to Sponsors for next query from list 
      $_POST['web_spon'] = 'spon';
      // set $status  list_text comes through JavaScript function:
      $status = '<font size=1>Relationships of Website '.$_POST['list_text'].'</font>';
    // if Sponsors:
    } else if($_POST['web_spon'] == 'spon'){
      $query_list = "SELECT w_name AS name, w_id AS id
                     FROM `Websites`,`Website_Sponsor`,`Sponsors` 
                     WHERE w_id = ws_w_id 
                     AND ws_s_id = s_id 
                     AND s_id = '".$_POST['list']."'
                    ";
      // Switch $_POST['web_spon'] to Websites for next query from list 
      $_POST['web_spon'] = 'web';
      // set $status  list_text comes through JavaScript function:
      $status = '<font size=1>Relationships of Sponsor '.$_POST['list_text'].'</font>';
    }
  }//End if($_POST['dtls_rltnshp'] == 'rltnshp'){
}//END if(isset($_POST['display'])){


//---------- Handle Update Queries -------------
// Update Websites Table
// Note: $_POST variables have already been 'cleaned' for handling 
and input... 
if(isset($_POST['edit_web'])){
  $query_update_web = "UPDATE `Websites`
                       SET w_name = '".$_POST['w_name']."',
                           w_url = '".$_POST['w_url']."',
                           w_desc = '".$_POST['w_desc']."',
                           w_status = '".$_POST['w_status']."'
                       WHERE w_id = '".$_POST['w_id']."'
                      ";
  if($result = @mysql_query($query_update_web,$db) 
     && @mysql_affected_rows($db) == 1){
    $status_update = 'Edited Website Record';
  }else{
    $status_update = 'Did NOT Update Website Record';
  }
  // optimize table to help prevent problems
  $query_optimize_web = "OPTIMIZE TABLE `Websites`";
  @mysql_query($query_optimize_web,$db);
}//End if(isset($_POST['edit_web'])){
if(isset($_POST['edit_spon'])){
  $query_update_spon = "UPDATE `Sponsors`
                        SET s_name = '".$_POST['s_name']."',
                            s_url = '".$_POST['s_url']."',
                            s_staturl = '".$_POST['s_staturl']."',
                            s_email = '".$_POST['s_email']."',
                            s_uname = '".$_POST['s_uname']."',
                            s_pass = '".$_POST['s_pass']."',
                            s_status = '".$_POST['s_status']."',
                            s_date = '".$_POST['s_date']."'
                        WHERE s_id = '".$_POST['s_id']."'
                       ";
  if($result = @mysql_query($query_update_spon,$db) 
     && @mysql_affected_rows($db) == 1){
    $status_update = 'Edited Sponsor Record'; 
  }else{
    $status_update = 'Did NOT Update Sponsor Record';
  }
  // optimize table to help prevent problems
  $query_optimize_spon = "OPTIMIZE TABLE `Sponsors`";
  @mysql_query($query_optimize_spon,$db);
}//End if(isset($_POST['edit_spon'])){
?>
 
<html>
<head>
<title>Script School MySQL101 Script3</title>
<meta http-equiv="Content-Type" content="text/html; 
charset=iso-8859-1">
<script language="JavaScript" type="text/JavaScript">
/* Using JavaScript to avoid a query to the 
database, *\
* since this is not a critical operation.
* function toHiddenVar sets hidden form variable list_text 
* to the name of the Website or Sponsor having it's relationships shown. 
\* It's picked up again as: $_POST['list_text'] */
function toHiddenVar(){
  // form name is: 'display' 
  // select name is: 'list'
  lst = document.display.list;
  txt = lst.options[lst.selectedIndex].text;
  // hidden variable name is: 'list_text'
  document.display.list_text.value = txt;
}
</script>
</head>

<body>
<div align="center">
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" 
method="post" enctype="application/x-www-form-urlencoded" 
name="filter" id="filter">
<table width="600" border="1" cellpadding="2" 
cellspacing="1" bordercolor="#0000FF" bgcolor="#CCFFFF" 
rules="rows">
<caption align="left">
<strong>Filter:</strong> 
</caption>
  <tr align="right"> 
    <td valign="middle"> 
    <label for="web">Websites</label> 
    <input type="radio" name="web_spon" value="web" id="web" 
<?php check_radio('web_spon', 'web', 'web'); ?>></td>
    <td valign="middle"> 
    <label for="spon">Sponsors</label> 
    <input type="radio" name="web_spon" value="spon" id="spon"
<?php check_radio('web_spon', 'spon', 'web'); ?>></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr align="right"> 
    <td>
    <label for="rel1">With Relations</label>
    <input type="radio" name="relations" value="yes" id="rel1"
<?php check_radio('relations', 'yes', 'yes'); ?>></td>
    <td>
    <label for="rel2">No Relations</label>
    <input type="radio" name="relations" value="no" id="rel2" 
<?php check_radio('relations', 'no', 'yes'); ?>></td>
    <td>
    <label for="rel3">All</label>
    <input type="radio" name="relations" value="all" id="rel3" 
<?php check_radio('relations', 'all', 'yes'); ?>></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr align="right"> 
    <td>
    <label for="status1">Pending</label>
    <input type="radio" name="status" value="Pending" id="status1"
<?php check_radio('status', 'Pending', 'Active'); ?>></td>
    <td>
    <label for="status2">Active</label>
    <input type="radio" name="status" value="Active" id="status2"
<?php check_radio('status', 'Active', 'Active'); ?>></td>
    <td>
    <label for="status3">Inactive</label>
    <input type="radio" name="status" value="Inactive" id="status3"
<?php check_radio('status', 'Inactive', 'Active'); ?>></td>
    <td>
    <label for="status4">Defunct</label>
    <input type="radio" name="status" value="Defunct" id="status4"
<?php check_radio('status', 'Defunct', 'Active'); ?>></td>
    <td>
    <label for="status5">All</label>
    <input type="radio" name="status" value="all" id="status5" 
<?php check_radio('status', 'all', 'Active'); ?>></td>
  </tr>
  <tr align="center"> 
    <td colspan="5">
    <input name="filter" type="submit" id="filter" value="Filter">
    </td>
  </tr>
</table>
</form>
 
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" 
method="post" enctype="application/x-www-form-urlencoded" 
name="search" id="search">
<table width="600" border="1" cellpadding="2" 
cellspacing="1" bordercolor="#0000FF" bgcolor="#CCFFFF" 
rules="rows">
<caption align="left">
<strong>Search:</strong>
</caption>
  <tr align="right"> 
    <td width="199"> Show Websites With: </td>
    <td width="284" align="center"> 
    <input name="searchfield" type="text" id="searchfield" 
size="35" maxlength="100" 
value="<?php echo stripslashes($_POST['searchfield']); ?>">
    </td>
    <td width="93" align="left">
    <!-- set web_spon to web for Search form -->
    <input type="hidden" name="web_spon" value="web"> 
    <input name="search" type="submit" id="search" value="Search">
    </td>
  </tr>
</table>
</form>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" 
method="post" enctype="application/x-www-form-urlencoded" 
name="display" id="display">
<table width="600" border="1" cellpadding="2" 
cellspacing="1" bordercolor="#0000FF" bgcolor="#CCFFFF" 
rules="none">
<caption align="left"><strong>Display:</strong></caption>
  <tr> 
    <td width="342" rowspan="3" align="center" valign="middle"> 
    <?php 
    // if $query_list variable not empty, set array to fill select list box,
    // to apply 'disabled' to list tag when no active options are available:
    if(!empty($query_list)){
      // set variable to disable select list when appropriate:
      $disabled = '';
      // obtain $result: 
      $result = @mysql_query($query_list,$db);
      // Get data one row at a time: 
      while($row = @mysql_fetch_array($result)){
        // Create options in an array: 
        $option[] = '<option value="'.$row['id'].'">'.$row['name'].'</option>';
      }
      if(@mysql_num_rows($result) < 1){ // no records
        // rewrite the option[] array with status message:
        $option[] = '<option value="">No Records Match</option>';
        // used to disable select list
        $disabled = 'disabled';
      }else{ // results OK
        // clear MySQL memory:
        mysql_free_result($result);
      }
    } else {// $query_list empty, has not been set by Filter or Search  
      $option[] = '<option value="">Use Filter or Search</option>';
      // used to disable select list
      $disabled = 'disabled';
    }
    ?> 
    <!-- Note onChange event handler -->
    <select name="list" id="list" size="6" onChange="toHiddenVar(); return true" 
    <?php echo $disabled; ?>>
    <?php 
    // fill select list:
    foreach($option as $value){
      echo $value;
    } 
    ?>
    </select>
    <!-- hidden list_text variable to hold/send name of Website or Sponsor.
     value assigned by JavaScript function toHiddenVar() 
     when list onChange event handler triggered. -->
    <input type="hidden" name="list_text">
    <br>
    <?php echo $status; ?> 
    </td>
    <td width="241">
    <label for="dt2">Display to Edit Details</label>
    <input name="dtls_rltnshp" type="radio" value="dtls" id="dt2"
<?php check_radio('dtls_rltnshp', 'dtls', 'dtls'); ?>></td>
  </tr>
  <tr> 
    <td>
    <label for="dt1">Display Relationships</label>
    <input name="dtls_rltnshp" type="radio" value="rltnshp" id="dt1" 
<?php check_radio('dtls_rltnshp', 'rltnshp', 'dtls'); ?>>
    </td>
  </tr>
  <tr> 
    <td align="left" valign="middle">
    <!-- $_POST['web_spon'] hidden variable
    flags if Websites or Sponsors are in list...
    from radio button in Filter form 
    OR from hidden variable in Search form
    OR if showing relationships from Display form -->
    <input name="web_spon" type="hidden" 
value="<?php echo $_POST['web_spon']; ?>">

    <input name="display" type="submit" id="display" value="Display">
    </td>
  </tr>
</table>
</form>


<?php
// if display and dtls OR if from either edit updates
if( ( isset($_POST['display']) && ($_POST['dtls_rltnshp'] == 'dtls') ) 
   || ( isset($_POST['edit_web']) || isset($_POST['edit_spon']) ) ){
  // $_POST['web_spon'] from hidden form variable can be 'web' or 'spon'
  // 'web_spon' from display OR 'edit_web' to show update
  if( ($_POST['web_spon'] == 'web') || (isset($_POST['edit_web'])) ){
    if(isset($_POST['edit_web'])){
      $query_web_dtls = "SELECT * FROM `Websites`
                         WHERE w_id = '".$_POST['w_id']."'
                        "; 
    }elseif($_POST['web_spon'] == 'web'){
      $query_web_dtls = "SELECT * FROM `Websites`
                         WHERE w_id = '".$_POST['list']."'
                        ";
    }
    $result = mysql_query($query_web_dtls,$db);
    $row = mysql_fetch_array($result);
    $w_id = $row['w_id'];
    $w_name = stripslashes($row['w_name']);
    $w_url = stripslashes($row['w_url']);
    $w_desc = stripslashes($row['w_desc']);
    $w_status = stripslashes($row['w_status']);
    // clear MySQL memory:
    mysql_free_result($result);
    ?>
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" 
method="post" enctype="application/x-www-form-urlencoded" 
name="edit_web" id="edit_web">
    <table width="600" border="1" cellpadding="2" 
cellspacing="1" bordercolor="#0000FF" bgcolor="#CCFFFF" 
rules="rows">
    <caption>
      <strong>Display / Edit Website Details </strong> 
    </caption>
    <tr> 
      <td width="119" align="right">Name:</td>
      <td width="5">&nbsp;</td>
      <td width="456" align="left">
      <input name="w_name" type="text" id="w_name" 
size="50" maxlength="255"
value="<?php echo $w_name; ?>">
      </td>
    </tr>
    <tr> 
      <td align="right">URL:</td>
      <td>&nbsp;</td>
      <td align="left">
      <input name="w_url" type="text" id="w_url" 
size="50" maxlength="255" 
value="<?php echo $w_url; ?>">
      </td>
    </tr>
    <td align="right">Description:</td>
      <td>&nbsp;</td>
      <td align="left">
      <textarea name="w_desc" cols="50" rows="5" 
id="w_desc" wrap="hard"><?php echo $w_desc; ?></textarea>
      </td>
    </tr>
    <tr> 
      <td align="right">Status:</td>
      <td>&nbsp;</td>
      <td align="left">
      <select name="w_status" id="w_status">
        <option value="Pending"
<?php if($w_status == 'Pending') echo ' selected '; ?>>Pending</option>
        <option value="Active"
<?php if($w_status == 'Active') echo ' selected '; ?>>Active</option>
        <option value="Inactive"
<?php if($w_status == 'Inactive') echo ' selected '; ?>>Inactive</option>
        <option value="Defunct"
<?php if($w_status == 'Defunct') echo ' selected '; ?>>Defunct</option>
      </select>
      </td>
    </tr>
    <tr align="center"> 
    <td colspan="3">
    <input name="w_id" type="hidden" value="<?php echo $w_id; ?>">
    <input type="reset" name="Reset" value="Reset">&nbsp;&nbsp;&nbsp;
    <input name="edit_web" type="submit" id="edit_web" value="Edit">
    <br>
    <?php echo $status_update; ?>
    </td>
  </tr>
</table>
</form>
<?php 
  //End if($_POST... == 'web')
  // if 'web_spon' from display OR if 'edit_spon' to show edit update 
  }elseif( ($_POST['web_spon'] == 'spon') 
        || (isset($_POST['edit_spon'])) ){
    if($_POST['web_spon'] == 'spon'){
      $query_spon_dtls = "SELECT * FROM `Sponsors`
                          WHERE s_id = '".$_POST['list']."' 
                         ";
    }elseif(isset($_POST['edit_spon'])){
      $query_spon_dtls = "SELECT * FROM `Sponsors`
                          WHERE s_id = '".$_POST['s_id']."' 
                         "; 
    }
    $result = mysql_query($query_spon_dtls,$db);
    $row = mysql_fetch_array($result);
    $s_id = $row['s_id'];
    $s_name = stripslashes($row['s_name']);
    $s_url = stripslashes($row['s_url']);
    $s_staturl = stripslashes($row['s_staturl']);
    $s_email = stripslashes($row['s_email']);
    $s_uname = stripslashes($row['s_uname']);
    $s_pass = stripslashes($row['s_pass']);
    $s_status = stripslashes($row['s_status']);
    $s_date = stripslashes($row['s_date']);
    // clear MySQL memory:
    mysql_free_result($result); 
?> 
    <form name="edit_spon" id="edit_spon" method="post" 
action="<?php echo $_SERVER['PHP_SELF']; ?>" 
enctype="application/x-www-form-urlencoded">
    <table width="600" border="1" cellpadding="2" 
cellspacing="1" bordercolor="#0000FF" bgcolor="#CCFFFF" 
rules="rows">
    <caption>
      <strong>Display / Edit Sponsor Details</strong> 
    </caption>
    <tr> 
      <td width="157" align="right">Name:</td>
      <td width="5">&nbsp;</td>
      <td width="407" align="left">
      <input name="s_name" type="text" id="s_name" 
size="50" maxlength="255"
value="<?php echo $s_name;?>">
      </td>
    </tr>
    <tr> 
      <td align="right">URL:</td>
      <td width="5">&nbsp;</td>
      <td align="left">
      <input name="s_url" type="text" id="s_url" 
size="50" maxlength="255"
value="<?php echo $s_url;?>">
      </td>
    </tr>
    <tr> 
      <td align="right">Statistics URL:</td>
      <td width="5">&nbsp;</td>
      <td align="left">
      <input name="s_staturl" type="text" id="s_staturl" 
size="50" maxlength="255"
value="<?php echo $s_staturl;?>">
      </td>
    </tr>
    <tr> 
      <td align="right">Email:</td>
      <td width="5">&nbsp;</td>
      <td align="left">
      <input name="s_email" type="text" id="s_email" 
size="50" maxlength="255"
value="<?php echo $s_email;?>">
      </td>
    </tr>
    <tr> 
      <td align="right">User Name:</td>
      <td width="5">&nbsp;</td>
      <td align="left">
      <input name="s_uname" type="text" id="s_uname" 
size="50" maxlength="255"
value="<?php echo $s_uname;?>">
      </td>
    </tr>
    <tr> 
      <td align="right">Password:</td>
      <td width="5">&nbsp;</td>
      <td align="left">
      <input name="s_pass" type="text" id="s_pass" 
size="50" maxlength="255"
value="<?php echo $s_pass;?>">
      </td>
    </tr>
    <tr>
      <td align="right">Status:</td>
      <td width="5">&nbsp;</td>
      <td align="left">
      <select name="s_status" id="s_status">
        <option value="Pending"<?php if($s_status == 'Pending') 
echo ' selected '; ?>>Pending</option>
        <option value="Active"<?php if($s_status == 'Active') 
echo ' selected '; ?>>Active</option>
        <option value="Inactive"<?php if($s_status == 'Inactive') 
echo ' selected '; ?>>Inactive</option>
        <option value="Defunct"<?php if($s_status == 'Defunct') 
echo ' selected '; ?>>Defunct</option>
      </select></td>
    </tr>
    <tr> 
      <td align="right">Date:</td>
      <td width="5">&nbsp;</td>
      <td align="left">
      <input name="s_date" type="text" id="s_date" 
size="10" maxlength="10"
value="<?php echo $s_date;?>">
&nbsp;&nbsp; <font size="1">[ YYYY-MM-DD ]</font></td>
    </tr>
    <tr align="center"> 
      <td colspan="3">
      <input name="s_id" type="hidden" value="<?php 
echo $s_id; ?>">
      <input type="reset" name="Reset" value="Reset">
      &nbsp;&nbsp;&nbsp;
      <input name="edit_spon" type="submit" id="edit_spon" 
value="Edit">
      <br>
      <?php echo $status_update; ?>
      </td>
    </tr>
  </table>
  </form>
  <?php 
  }//End elseif($_POST... == 'spon')
}//End if(isset($_POST['display'])){
?>
</div>
</body>
</html>
 

 

If You have any questions, please use the Script School Forum, the LIVE CHAT Tuesday workshops, or Chat during the LIVE Radio Show on Fridays.

Next Week We'll discuss backing up databases and catch up on a few other things like encryption and spanning pages with data.

Week #4 Assignments

Basic Assignment:  Create interfaces to Add Relationships, Delete Relationships and Display the information in Your database in a useful way.

Advanced Assignment: Do the basic assignment and add more features to the Display form.  Allow sorting by entry date, Ascending Or Descending alphabetical order, add controls to set the default radio button states, and if You have added additional Tables to Your database create the means to add, edit, delete and display that information.  Consider usefulness and ease of use. Consider adding rating or profit fields which can be displayed visually as bar charts, these can be as simple as a line of  *** or can be stretchable images controlled by HTML through PHP.  If You have advanced graphical skills these can even be complex 3D images.

Submit Your assignments to the Script School Forum for comments and 'credit'

Need help with your assignment or have questions about the first week's course?

School Map:  Home / Enroll $ Student Records; Class  
$ News @ LIVE Tech Radio * Support/FAQ | Store

Advertising | Link To Us | Privacy 
Copyright 2000-2002 Script School Productions / KMR Enterprises 
No part of this website may be reproduced, copied and/or distributed in any medium 
without express written permission