Source of spyLib.php

<style type = "text/css">
body{
  background-color: black;
  color: white;
  text-align:center
}

</style>

<?
//spyLib.php
//holds utilities for spy database

//variables
$userName = "";
$serverName = "localhost";
$adminPassword = "absolute";
$dbName = "chapter10";
$dbConn = "";
$mainProgram = "spyMaster.php";

function connectToSpy(){
  //connects to the spy DB
  global $serverName, $userName, $password;
  $dbConn = mysql_connect($serverName, $userName, $password);
  if (!$dbConn){
   print "<h3>problem connecting to database...</h3>\n";
  } // end if
  
  $select = mysql_select_db("chapter10");
  if (!$select){
    print mysql_error() . "<br>\n";
  } // end if
  return $dbConn;
} // end connectToSpy

function qToList($query){
  //given a query, makes a quick list of data
  global $dbConn;
  $output = "";
  $result = mysql_query($query, $dbConn);

  //print "dbConn is $dbConn<br>";
  //print "result is $result<br>";

  while ($row = mysql_fetch_assoc($result)){
    foreach ($row as $col=>$val){
      $output .= "$col: $val<br>\n";
    } // end foreach
    $output .= "<hr>\n" ;
  } // end while
  return $output;
} // end qToList

function qToTable($query){
  //given a query, automatically creates an HTML table output
  global $dbConn;
  $output = "";
  $result = mysql_query($query, $dbConn);

  $output .= "<table border = 1>\n";
  //get column headings

  //get field names
  $output .= "<tr>\n";
  while ($field = mysql_fetch_field($result)){
    $output .= "  <th>$field->name</th>\n";
  } // end while
  $output .= "</tr>\n\n";

  //get row data as an associative array
  while ($row = mysql_fetch_assoc($result)){
    $output .= "<tr>\n";
    //look at each field
    foreach ($row as $col=>$val){
      $output .= "  <td>$val</td>\n";
    } // end foreach
    $output .= "</tr>\n\n";
  }// end while

  $output .= "</table>\n";
  return $output;
} // end qToTable

function tToEdit($tableName){
  //given a table name, generates HTML table including
  //add, delete and edit buttons
  
  global $dbConn;
  $output = "";
  $query = "SELECT * FROM $tableName";

  $result = mysql_query($query, $dbConn);

  $output .= "<table border = 1>\n";
  //get column headings

  //get field names
  $output .= "<tr>\n";
  while ($field = mysql_fetch_field($result)){
    $output .= "  <th>$field->name</th>\n";
  } // end while

  //get name of index field (presuming it's first field)
  $keyField = mysql_fetch_field($result, 0);
  $keyName = $keyField->name;
  
  //add empty columns for add, edit, and delete
  $output .= "<th></th><th></th>\n";
  $output .= "</tr>\n\n";

  //get row data as an associative array
  while ($row = mysql_fetch_assoc($result)){
    $output .= "<tr>\n";
    //look at each field
    foreach ($row as $col=>$val){
      $output .= "  <td>$val</td>\n";
    } // end foreach
    //build little forms for add, delete and edit


    //delete = DELETE FROM <table> WHERE <key> = <keyval>
    $keyVal = $row["$keyName"];
    $output .= <<< HERE

  <td>
    <form action = "deleteRecord.php">
    <input type = "hidden"
           name = "tableName"
           value = "$tableName">
    <input type= "hidden"
           name = "keyName"
           value = "$keyName">
    <input type = "hidden"
           name = "keyVal"
           value = "$keyVal">
    <input type = "submit"
           value = "delete"></form>
  </td>

HERE;
    //update: won't update yet, but set up edit form
    $output .= <<< HERE
  <td>
    <form action = "editRecord.php"
          method = "post">
    <input type = "hidden"
           name = "tableName"
           value = "$tableName">
    <input type= "hidden"
           name = "keyName"
           value = "$keyName">
    <input type = "hidden"
           name = "keyVal"
           value = "$keyVal">
    <input type = "submit"
           value = "edit"></form>
  </td>

HERE;

    $output .= "</tr>\n\n";
    
  }// end while

    //add = INSERT INTO <table> {values}
    //set up insert form send table name
    $keyVal = $row["$keyName"];
    $output .= <<< HERE

  <td colspan = "5">
    <center>
    <form action = "addRecord.php">
    <input type = "hidden"
           name = "tableName"
           value = "$tableName">
    <input type = "submit"
           value = "add a record"></form>
    </center>
  </td>

HERE;


  $output .= "</table>\n";
  return $output;
} // end tToEdit

function rToEdit ($query){
  //given a one-record query, creates a form to edit that record
  //works on any table, but allows direct editing of keys
  //use smartRToEdit instead if you can
  
  global $dbConn;
  $output = "";
  $result = mysql_query($query, $dbConn);
  $row = mysql_fetch_assoc($result);

  //get table name from field object
  $fieldObj = mysql_fetch_field($result, 0);
  $tableName = $fieldObj->table;

  $output .= <<< HERE
<form action = "updateRecord.php"
      method = "post">

<input type = "hidden"
       name = "tableName"
       value = "$tableName">

<table border = 1>

HERE;

  foreach ($row as $col=>$val){
    $output .= <<<HERE
  <tr>
    <th>$col</th>
    <td>
      <input type = "text"
             name = "$col"
             value = "$val">
    </td>
  </tr>

HERE;
  } // end foreach
  $output .= <<< HERE
  <tr>
    <td colspan = 2>
      <center>
      <input type = "submit"
             value = "update this record">
      </center>
    </td>
  </tr>
</table>

HERE;
  return $output;
} // end rToEdit

function smartRToEdit ($query){
  //given a one-record query, creates a form to edit that record
  //Doesn't let user edit first (primary key) field
  //generates dropdown list for foreign keys
  //MUCH safer than ordinary rToEdit function

  // --restrictions on table design--
  //foreign keys MUST be named tableID where 'table' is table name
  //  (because mySQL doesn't recognize foreign key indicators)
  // I also expect a 'name' field in any table used as a foreign key
  //   (for same reason)
  
  global $dbConn;
  $output = "";
  $result = mysql_query($query, $dbConn);
  $row = mysql_fetch_assoc($result);

  //get table name from field object
  $fieldObj = mysql_fetch_field($result, 0);
  $tableName = $fieldObj->table;

  $output .= <<< HERE
<form action = "updateRecord.php"
      method = "post">

<input type = "hidden"
       name = "tableName"
       value = "$tableName">

<table border = 1>

HERE;
  $fieldNum = 0;
  foreach ($row as $col=>$val){
    if ($fieldNum == 0){
      //it's primary key.  don't make textbox,
      //but store value in hidden field instead
      //user shouldn't be able to edit primary keys
      $output .= <<<HERE
  <tr>
    <th>$col</th>
    <td>$val
      <input type = "hidden"
             name = "$col"
             value = "$val">
    </td>
  </tr>

HERE;
    } else if (preg_match("/(.*)ID$/", $col, $match)) {
      //it's a foreign key reference
      // get table name (match[1])
      //create a listbox based on table name and its name field
      $valList = fieldToList($match[1],$col, $fieldNum, "name");
      
      $output .= <<<HERE
  <tr>
    <th>$col</th>
    <td>$valList</td>
  </tr>
  
HERE;

    } else {
      $output .= <<<HERE
  <tr>
    <th>$col</th>
    <td>
      <input type = "text"
             name = "$col"
             value = "$val">
    </td>
  </tr>

HERE;
    } // end if
    $fieldNum++;
  } // end foreach
  $output .= <<< HERE
  <tr>
    <td colspan = 2>
      <center>
      <input type = "submit"
             value = "update this record">
      </center>
    </td>
  </tr>
</table>
</form>

HERE;
  return $output;
} // end rToEdit

function updateRec($tableName, $fields, $vals){
  //expects name of a record, fields array values array
  //updates database with new values
  
  global $dbConn;
  
  $output = "";
  $keyName = $fields[0];
  $keyVal = $vals[0];
  $query = "";
  
  $query .= "UPDATE $tableName SET \n";
  for ($i = 1; $i < count($fields); $i++){
    $query .= $fields[$i];
    $query .= " = '";
    $query .= $vals[$i];
    $query .= "',\n";
  } // end for loop

  //remove last comma from output
  $query = substr($query, 0, strlen($query) - 2);
  
  $query .= "\nWHERE $keyName = '$keyVal'";

  $result = mysql_query($query, $dbConn);
  if ($result){
    $query = "SELECT * FROM $tableName WHERE $keyName = '$keyVal'";
    $output .= "<h3>update successful</h3>\n";
    $output .= "new value of record:<br>";
    $output .= qToTable($query);
  } else {
    $output .= "<h3>there was a problem...</h3><pre>$query</pre>\n";
  } // end if
  return $output;
} // end updateRec

function delRec ($table, $keyName, $keyVal){
  //deletes $keyVal record from $table
  global $dbConn;
  $output = "";
  $query = "DELETE from $table WHERE $keyName = '$keyVal'";
  print "query is $query<br>\n";
  $result = mysql_query($query, $dbConn);
  if ($result){
    $output = "<h3>Record sucessfully deleted</h3>\n";
  } else {
    $output = "<h3>Error deleting record</h3>\n";
  } //end if
  return $output;
} // end delRec

function tToAdd($tableName){
  //given table name, generates HTML form to add an entry to the
  //table.  Works like smartRToEdit in recognizing foreign keys
  
  global $dbConn;
  $output = "";

  //process a query just to get field names
  $query = "SELECT * FROM $tableName";
  $result = mysql_query($query, $dbConn);

  $output .= <<<HERE
  <form action = "processAdd.php"
        method = "post">
  <table border = "1">
    <tr>
      <th>Field</th>
      <th>Value</th>
    </tr>
    
HERE;

  $fieldNum = 0;
  while ($theField = mysql_fetch_field($result)){
    $fieldName = $theField->name;
    if ($fieldNum == 0){
      //it's the primary key field.  It'll be autoNumber
      $output .= <<<HERE
      <tr>
        <td>$fieldName</td>
        <td>AUTONUMBER
          <input type = "hidden"
                 name = "$fieldName"
                 value = "null">
        </td>
      </tr>

HERE;
    } else if (preg_match("/(.*)ID$/", $fieldName, $match)) {
      //it's a foreign key reference.  Use fieldToList to get
      //a select object for this field

      $valList = fieldToList($match[1],$fieldName, 0, "name");
      $output .= <<<HERE
      <tr>
        <td>$fieldName</td>
        <td>$valList</td>
      </tr>

HERE;
    } else {
    //it's an ordinary field.  Print a text box
    $output .= <<<HERE
      <tr>
        <td>$fieldName</td>
        <td><input type = "text"
                   name = "$fieldName"
                   value = "">
        </td>
      </tr>

HERE;
    } // end if
    $fieldNum++;
  } // end while
  $output .= <<<HERE
    <tr>
      <td colspan = 2>
        <input type = "hidden"
               name = "tableName"
               value = "$tableName">
        <input type = "submit"
               value = "add record">
      </td>
    </tr>
  </table>
  </form>

HERE;

  return $output;
      
} // end tToAdd

function procAdd($tableName, $fields, $vals){
  //generates INSERT query, applies to database
  global $dbConn;
  
  $output = "";
  $query = "INSERT into $tableName VALUES (";
  foreach ($vals as $theValue){
    $query .= "'$theValue', ";
  } // end foreach

  //trim off trailing space and comma
  $query = substr($query, 0, strlen($query) - 2);
  
  $query .= ")";
  $output = "query is $query<br>\n";
  
  $result = mysql_query($query, $dbConn);
  if ($result){
    $output .= "<h3>Record added</h3>\n";
  } else {
    $output .= "<h3>There was an error</h3>\n";
  } // end if
  return $output;
} // end procAdd


function fieldToList($tableName, $keyName, $keyVal, $fieldName){
  //given table and field, generates an HTML select structure
  //named $keyName.  values will be key field of table, but
  //text will come from the $fieldName value.
  //keyVal indicates which element is currently selected
  
  global $dbConn;
  $output = "";
  $query = "SELECT $keyName, $fieldName FROM $tableName";
  $result = mysql_query($query, $dbConn);
  $output .= "<select name = $keyName>\n";
  $recNum = 1;
  while ($row = mysql_fetch_assoc($result)){
    $theIndex = $row["$keyName"];
    $theValue = $row["$fieldName"];
    $output .= <<<HERE
    right now, theIndex is $theIndex and keyVal is $keyVal
  <option value = "$theIndex"
HERE;

    //make it currently selected item
    if ($theIndex == $keyVal){
      $output .= " selected";
    } // end if
    $output .= ">$theValue</option>\n";
    $recNum++;
  } // end while
  $output .= "</select>\n";
  return $output;
} // end fieldToList

function mainButton(){
  // creates a button to return to the main program
  
  global $mainProgram;
  
  $output .= <<<HERE
<form action = "$mainProgram"
      method = "get">
<input type = "submit"
       value = "return to main screen">
</form>

HERE;
  return $output;
} // end mainButton
  
?>