Keep track of changes made to data base be for an UPDATE in SQL

[agr8lemon]

Hello everyone,

I'm not sure the title fits my question, but I didn't know how to word it correctly.

Here is my question.

I'm working on a forum that pulls data from a SQL database via PHP. It's data on employee data (name, job title, what programs they have access to, etc...). On this page, I want the admins to be able to change most of the fields in case the employee needs access to different programs or has name change or whatever.

What I would like to happen is that if an admin changes a value in a drop down box and hits submit, I track what items where changed so I get automate a email out with what was changed.
I'll show an example of what I have:

$id=$_GET['id'];

// Retrieve data from database
$sql="SELECT * FROM $tbl_name WHERE id='$id'";
//$result=mysql_query($sql);
$result=odbc_exec($conn, $sql);
$rows=odbc_fetch_array($result);
?>
<form name="form1" method="post" action="edit_ac.php">
<td>
<table align="center" border="0" cellspacing="1" cellpadding="0">
<tr>
<th colspan="2"><strong><u><?php echo $rows['first']  . ' ' . $rows['middle'] . ' ' . $rows['last']; ?></u></strong> <a href='delete.php?id=<?php echo $id; ?>'<img src="images/deleteuser.png" border="0" alt="Delete User"></a> </th>
</tr>
<tr>
<td colspan="2">&nbsp;</td>
</tr>
<tr>
  <td>First Name:</td>
  <td align="center"><input name="first" size="10" type="text" id="first" value="<?php echo $rows['first']; ?>"></td>
</tr>
<tr>
  <td>Middle Name</td>
  <td align="center"><input name="middle" size="10" type="text" id="middle" value="<?php echo $rows['middle']; ?>"></td>
</tr>
<tr>
  <td>Last Name</td>
  <td align="center"><input name="last" size="10" type="text" id="last" value="<?php echo $rows['last']; ?>"></td>
</tr>
<tr>
<td>Facility</td>
<td align="center">
    <?php 
   switch ($rows['facility'])
   {
    case "SHH":
      echo '<select id="facility" name="facility">
            <option value="SHH" selected="selected">SHH</option>
            <option value="SJH">SJH</option>
            <option value="Both">Both</option>
            </select>';
      break;
    case "SJH":
    echo '<select id="facility" name="facility" STYLE="width: 90px">
          <option value="SHH">SHH</option>
          <option value="SJH" selected="selected">SJH</option>
          <option value="Both">Both</option>
          <select>'; 
      break;
    case "Both":
    echo '<select id="facility" name="facility">
          <option value="SHH">SHH</option>
          <option value="SJH">SJH</option>
          <option value="Both" selected="selected">Both</option>
          </select>';
      break;    
   }      

    ?>
    

</td>
</tr>
<tr>
  <td>Hire Date</td>
  <td align="center"><input name="date" size="10" type="text" id="date" readonly="readonly" STYLE="background-color: silver;" value="<?php echo $rows['date']; ?>"></td>
</tr>
<tr>
  <td>Department</td>
  <td align="center"><input name="department" size="10" type="text" id="department" value="<?php echo $rows['department']; ?>"></td>
</tr>
<tr>
  <td>Title</td>
  <td align="center"><input name="job"  type="text" id="job" value="<?php echo $rows['job']; ?>"></td>
</tr>

</table>
<br>
<br>
<font size="+2"><u><strong>Application Status</strong></u></font><br><br>
<table border="0" align="center" cellspacing="1" cellpadding="0">
<tr>
<td>
  <?php if ($rows['staff'] != NULL) {echo '<select id="staff" name="staff"><option class="green" value="add" selected="selected">√</b></option>
  <option value="remove" class="red">−</option>
  </select>';
  }
   else 
  {
  echo '<select id="staff" name="staff">
  <option value="add" class="green">√</option>
  <option  class="red" value="remove" selected="selected">−</option>
  </select>';
  } 
  ?>
</td>
<td>ANSOS</td>
<td><span></span></td>

<td>
  <?php if ($rows['lab'] != NULL) {echo '<select id="lab" name="lab"><option value="add" selected="selected" class="green">√</option>
  <option value="remove" class="red">−</option>
  </select>';
  }
   else 
  {
  echo '<select id="lab" name="lab">
  <option value="add" class="green">√</option>
  <option value="remove" selected="selected" class="red">−</option>
  </select>';
  } 
  ?>
</td>
<td>Mysis Lab</td>
</tr>

<tr>
<td>
  <?php if ($rows['cerme'] != NULL) {echo '<select id="cerme" name="cerme"><option value="add" selected="selected" class="green">√</option>
  <option value="remove" class="red">−</option>
  </select>';
  }
   else 
  {
  echo '<select id="cerme" name="cerme">
  <option value="add" class="green">√</option>
  <option value="remove" selected="selected" class="red">−</option>
  </select>';
  } 
  ?>

There's more to it, but that should give you an idea. So if someone selects the dropdown box named Cerme and changes the vaule, I'd to to be able to email someone about that change.

I don't want help with the email, I can do that, but it's tracking what's changed and what hasn't.

Any help would be GREAT. If you need some clarification, please let me know.

Thanks!

**Edit**

Sorry for the terrible grammar in the title!

[diafol]

Sorry, can't read all the code - headshot. You want a log table that logs the SQL statement. SO every time you:

$q = "UPDATE ...";
$r = mysql_query($q);
if(mysql_affected_rows() > 0){
  $logSQL = mysql_query("INSERT INTO logs SET sql = '$q'");
  ...
}

[BzzBee]

you have to manage a log table in your database. And after execution of any query you have to enter that query string in your log table with other information like IP address ect.

[agr8lemon]

It looks like I get to learn something new!

Thank you for your help.