function queryLog($fieldnames, $newvalues, $_table, $_field, $_row)
{
for ($i = 0; $i < count($fieldnames); $i++) {
// construct sql select field names from POST array
$_fields .= $fieldnames[$i] . ",";
}
$_fields = removeCharacter(trim($_fields), ",");
// update query - first select existing values
if ($_fields) {
$_sql_select = "SELECT " . $_fields . " FROM " . $_table . " WHERE " . $_field . " = " . $_row . "";
$_result = mysql_query($_sql_select);
if (!$_result) {
die("MySQL Error: " . mysql_error() . "<pre>SELECT: " . $_sql_select . "</pre>");
}
while ($row_select = mysql_fetch_array($_result)) {
for ($i = 0; $i < count($fieldnames); $i++) {
$sql_field = $fieldnames[$i];
$oldvalues[] = $row_select[$sql_field];
}
}
// we should now have 3 arrarys, all with the same number of values
// count each and compare before proceeding
if (count($fieldnames) != count($newvalues) || count($fieldnames) != count($oldvalues)) {
echo 'array count not matched';
exit;
}
// contruct sql for $_table AND changelog table, single query with multiple inserts
$sql_log = "INSERT INTO changelog \n\t(cha_user,cha_session,cha_table,cha_row,cha_field,cha_old,cha_new)\n\tVALUES \n\t";
for ($i = 0; $i < count($fieldnames); $i++) {
// if the new value is different from the old
if ($newvalues[$i] != $oldvalues[$i]) {
// the UPDATE query to be run on $_table WHERE $_field = $_row
$sql_update .= $fieldnames[$i] . "='" . $newvalues[$i] . "',";
// the INSERT query to be run on $_table WHERE $_field = $_row
$sql_insert1 .= $fieldnames[$i] . ",";
$sql_insert2 .= "'" . $newvalues[$i] . "',";
// the log query insert
$sql_log .= "('" . $_SESSION["s_userid"] . "','" . session_id() . "','" . $_table . "','" . $_row . "','" . $fieldnames[$i] . "','" . $oldvalues[$i] . "','" . $newvalues[$i] . "'),\n";
// message for debug
$_msg .= $fieldnames[$i] . " was changed from " . $oldvalues[$i] . " to " . $newvalues[$i] . "\n";
// count the number of changes
$_changecount++;
}
}
$sql_log = removeCharacter(trim($sql_log), ",");
$sql_return = "UPDATE " . $_table . " SET " . removeCharacter(trim($sql_update), ",") . " WHERE " . $_field . " = " . $_row;
if ($_changecount) {
// only execute the sql queries if a change has been made
$result_return = mysql_query($sql_return);
if (!$result_return) {
die("MySQL Error: " . mysql_error() . "<pre>RETURN: " . $sql_return . "</pre>");
}
$result_log = mysql_query($sql_log);
}
return $sql_return;
}
}
function queryLog($fieldnames, $newvalues, $_table, $_field, $_row, $_action)
{
for ($i = 0; $i < count($fieldnames); $i++) {
// construct sql select field names from POST array
$_fields .= $fieldnames[$i] . ",";
}
$_fields = removeCharacter(trim($_fields), ",");
if ($_fields) {
$sql = "SELECT " . $_fields . " FROM " . $_table . " WHERE " . $_field . " = " . $_row . "";
echo $sql;
$result = mysql_query($sql);
if (!$result) {
die("MySQL Error: " . mysql_error() . "<pre>" . $sql . "</pre>");
}
while ($row = mysql_fetch_array($result)) {
for ($i = 0; $i < count($fieldnames); $i++) {
$sql_field = $fieldnames[$i];
$oldvalues[] = $row[$sql_field];
}
}
//print_r($fieldnames);
//print_r($newvalues);
//print_r($oldvalues);
// we should now have 3 arrarys, all with the same number of values
// count each and compare before proceeding
if (count($fieldnames) != count($newvalues) || count($fieldnames) != count($oldvalues)) {
echo 'array count not matched';
exit;
}
// contruct sql for $_table AND changelog table, single query with multiple inserts
$sql_log = "INSERT INTO changelog \n\t(cha_user,cha_session,cha_table,cha_row,cha_field,cha_old,cha_new)\n\tVALUES \n\t";
for ($i = 0; $i < count($fieldnames); $i++) {
// if the new value is different from the old
if ($newvalues[$i] != $oldvalues[$i]) {
// the UPDATE query to be run on $_table WHERE $_field = $_row
$sql_update .= $fieldnames[$i] . "='" . $newvalues[$i] . "',";
// the INSERT query to be run on $_table WHERE $_field = $_row
$sql_insert1 .= $fieldnames[$i] . ",";
$sql_insert2 .= "'" . $newvalues[$i] . "',";
// the log query insert
$sql_log .= "('" . $_SESSION["s_userid"] . "','" . $PHPSESSID . "','" . $_table . "','" . $_row . "','" . $fieldnames[$i] . "','" . $oldvalues[$i] . "','" . $newvalues[$i] . "'),\n";
// message for debug
$_msg .= $fieldnames[$i] . " was changed from " . $oldvalues[$i] . " to " . $newvalues[$i] . "\n";
}
}
if ($_action == "Update") {
$sql_return = "UPDATE " . $_table . " SET " . removeCharacter(trim($sql_update), ",") . " WHERE " . $_field . " = " . $_row;
} elseif ($_action == "Insert") {
$sql_return = "INSERT INTO " . $_table . " (" . removeCharacter(trim($sql_insert1), ",") . ") VALUES (" . removeCharacter(trim($sql_insert2), ",") . ")";
}
$sql_log = removeCharacter(trim($sql_log), ",");
return $sql_return . "\n\n" . $sql_log;
}
}
function change_log($cha_user, $cha_table, $cha_field, $cha_row, $cha_sql, $cha_session)
{
// split $sql
$sql_split = preg_replace("/[\r\n]+[\\s\t]*[\r\n]+/", "", $cha_sql);
$sql_split = explode(",", $sql_split);
$sql_count = count($sql_split);
for ($i = 0; $i < $sql_count; $i++) {
$split = explode("=", $sql_split[$i]);
$fields .= trim($split[0]) . ",";
$values .= trim($split[1]) . ",";
}
// comma seperate list of database field names
$cha_columns = removeCharacter($fields, ",");
// comma seperated list of current values for above field names
$cha_values = removeCharacter($values, ",");
// split both into arrays to loop through
$cha_columns_array = explode(",", $cha_columns);
$cha_values_array = explode(",", $cha_values);
// compare number of fields to columns
if (count($cha_columns_array) != count($cha_values_array)) {
echo "number of fields and values do not match";
exit;
} else {
$cha_columns_count = count($cha_columns_array);
}
// select current values
$sql = "SELECT " . $cha_columns . " FROM " . $cha_table . " WHERE " . $cha_field . " = " . $cha_row . "";
echo $sql;
$result = mysql_query($sql);
if (!$result) {
die("MySQL Error: " . mysql_error());
}
while ($row = mysql_fetch_array($result)) {
for ($i = 0; $i < $cha_columns_count; $i++) {
$array_field[] = mysql_field_name($result, $i);
$array_current[] = $row[$i];
$new_value = str_replace("'", "", $cha_values_array[$i]);
// compare old and new values, if different, insert into changelog
if ($row[$i] != $new_value) {
$render .= "<p>" . mysql_field_name($result, $i) . " has changed from " . $row[$i] . " to " . $cha_values_array[$i] . "</p>";
$sqlChangeLog = "INSERT INTO changelog \n\t\t\t\t(cha_user,cha_session,cha_table,cha_field,cha_row,cha_old,cha_new)\n\t\t\t\tVALUES \n\t\t\t\t('{$cha_user}','{$cha_session}','{$cha_table}','{$array_field[$i]}','{$cha_row}','{$row[$i]}','{$new_value}')\n\t\t\t\t";
mysql_query($sqlChangeLog) or die("Error in ChangeLog Query: " . mysql_error() . "\n" . $sqlChangeLog);
}
}
}
//return $render;
}
$newvalues[] = trim($_POST[$field]);
}
}
if ($_POST["Areas"]) {
foreach ($_POST["Areas"] as $area) {
$AreaSQL .= $area . "^";
}
$AreaSQL = removeCharacter($AreaSQL, "^");
$fieldnames[] = "Areas";
$newvalues[] = $AreaSQL;
}
if ($_POST["Branch"]) {
foreach ($_POST["Branch"] as $b) {
$selected_branches .= $b . ",";
}
$BranchSQL = removeCharacter($selected_branches, ",");
$fieldnames[] = "Branch";
$newvalues[] = $BranchSQL;
}
//$fieldnames[] = "DateModified";
//$newvalues[] = $dateToday;
#print_r($fieldnames);
#echo "<br>";
#print_r($newvalues);
if ($errors) {
echo html_header("Error");
echo error_message($errors);
exit;
}
queryLog($fieldnames, $newvalues, 'clients', 'Client_ID', $_POST["cli_id"], 'Update');
#print_r($fieldnames);
function change_log($cha_user = 0, $cha_table, $cha_field, $cha_row, $cha_sql, $cha_session)
{
// split $sql
$cha_sql_split = $cha_sql;
// took this out as it altered the description and notes
//$cha_sql_split = preg_replace("/[\r\n]+[\s\t]*[\r\n]+/","",$cha_sql);
$cha_sql_split = explode(",", $cha_sql_split);
$cha_sql_count = count($cha_sql_split);
for ($cha_i = 0; $cha_i < $cha_sql_count; $cha_i++) {
$cha_split = explode("=", $cha_sql_split[$cha_i]);
$cha_fields .= trim($cha_split[0]) . "|";
$cha_values .= trim($cha_split[1]) . "|";
}
//echo $cha_fields;
// comma seperate list of database field names
$cha_columns = removeCharacter($cha_fields, "|");
// comma seperated list of current values for above field names
$cha_values = removeCharacter($cha_values, "|");
//echo "<p><b>new values</b>$cha_values</p>";
// split both into arrays to loop through
$cha_columns_array = explode("|", $cha_columns);
$cha_values_array = explode("|", $cha_values);
//print_r($cha_columns_array);
// compare number of fields to columns
if (count($cha_columns_array) != count($cha_values_array)) {
echo "number of fields and values do not match";
exit;
} else {
$cha_columns_count = count($cha_columns_array);
}
// select current values
$cha_sql2 = "SELECT " . str_replace("|", ",", $cha_columns) . " FROM " . $cha_table . " WHERE " . $cha_field . " = " . $cha_row . "";
//echo "<p>SELECT: $cha_sql2</p>";
$cha_result2 = mysql_query($cha_sql2);
if (!$cha_result2) {
die("MySQL Error: " . mysql_error() . $cha_sql2);
}
while ($cha_row2 = mysql_fetch_array($cha_result2)) {
for ($cha_i = 0; $cha_i < $cha_columns_count; $cha_i++) {
$cha_array_field[] = $cha_columns_array[$cha_i];
//mysql_field_name($cha_result2, $cha_i); //$cha_fields
$cha_array_current[] = $cha_row2[$cha_i];
$cha_new_value = str_replace("'", "", $cha_values_array[$cha_i]);
// testing
/*
echo "old value: ".$cha_array_current[$cha_i]."<br>
new value: ".$cha_new_value."<br>
field: ".$cha_array_field[$cha_i]."<p>";
*/
// compare old and new values, if different, insert into changelog
if ($cha_row2[$cha_i] != $cha_new_value) {
//$cha_render .= "<p>".mysql_field_name($cha_result2, $cha_i)." has changed from ".$row[$i]." to ".$cha_values_array[$cha_i]."</p>";
$cha_old_value = addslashes(substr($cha_row2[$cha_i], 0, 250));
$cha_new_value = addslashes(substr($cha_new_value, 0, 250));
$sqlChangeLog = "INSERT INTO changelog\n\t\t\t\t(cha_user,cha_session,cha_table,cha_field,cha_row,cha_old,cha_new)\n\t\t\t\tVALUES \n\t\t\t\t('{$cha_user}','{$cha_session}','{$cha_table}','{$cha_array_field[$cha_i]}','{$cha_row}','{$cha_old_value}','{$cha_new_value}')\n\t\t\t\t";
//echo $sqlChangeLog;
mysql_query($sqlChangeLog) or die("Error in ChangeLog Query: " . mysql_error() . "\n" . $sqlChangeLog);
}
}
}
return $cha_render;
}
$Postcode = $_POST["Postcode"];
$Tel = $_POST["Tel"];
$Fax = $_POST["Fax"];
$Mobile = $_POST["Mobile"];
$PropertyType = $_POST["PropertyType"];
$MinPrice = $_POST["MinPrice"];
$MaxPrice = $_POST["MaxPrice"];
$Receptions = $_POST["Receptions"];
$Bedrooms = $_POST["Bedrooms"];
$Bathrooms = $_POST["Bathrooms"];
if ($_POST["Areas"]) {
foreach ($_POST["Areas"] as $area) {
$AreaSQL .= $area . "^";
}
}
$AreaSQL = removeCharacter($AreaSQL, "^");
$Areas2 = $_POST["Areas2"];
$Notes = $_POST["Notes"];
$DG = $_POST["DG"];
$GCH = $_POST["GCH"];
$Modern = $_POST["Modern"];
$Period = $_POST["Period"];
$Tenure = $_POST["Tenure"];
$Garden = $_POST["Garden"];
$Parking = $_POST["Parking"];
$BuyToLet = $_POST["BuyToLet"];
$HeardBy = $_POST["HeardBy"];
$Selling = $_POST["Selling"];
$Valuation = $_POST["Valuation"];
$DateModified = $_POST["DateModified"];
$Status = $_POST["Status"];
print_r($oldvalues);
// we should now have 3 arrarys, all with the same number of values
// count each and compare before proceeding
if (count($fieldnames) != count($newvalues) || count($fieldnames) != count($oldvalues)) {
echo 'array count not matched';
exit;
}
// contruct sql for $_table AND changelog table, single query with multiple inserts
$sql_log = "INSERT INTO changelog \n(cha_user,cha_session,cha_table,cha_row,cha_field,cha_old,cha_new)\nVALUES \n";
for ($i = 0; $i < count($fieldnames); $i++) {
// if the new value is different from the old
if ($newvalues[$i] != $oldvalues[$i]) {
// the UPDATE query to be run on $_table WHERE $_field = $_row
$sql_update .= $fieldnames[$i] . "='" . $newvalues[$i] . "',";
// the INSERT query to be run on $_table WHERE $_field = $_row
$sql_insert1 .= $fieldnames[$i] . ",";
$sql_insert2 .= "'" . $newvalues[$i] . "',";
// the log query insert
$sql_log .= "('" . $_SESSION["s_userid"] . "','" . $PHPSESSID . "','" . $_table . "','" . $_row . "','" . $fieldnames[$i] . "','" . $oldvalues[$i] . "','" . $newvalues[$i] . "'),\n";
// message for debug
$_msg .= $fieldnames[$i] . " was changed from " . $oldvalues[$i] . " to " . $newvalues[$i] . "\n";
}
}
//
echo "\n\nUPDATE " . $_table . " SET " . removeCharacter(trim($sql_update), ",") . " WHERE " . $_field . " = " . $_row . "";
echo "\n\nINSERT INTO " . $_table . " (" . removeCharacter(trim($sql_insert1), ",") . ") VALUES (" . removeCharacter(trim($sql_insert2), ",") . ")";
echo "\n\n" . removeCharacter(trim($sql_log), ",");
}
?>
</pre>