Beispiel #1
0
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;
    }
}
Beispiel #2
0
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;
    }
}
Beispiel #3
0
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);
Beispiel #5
0
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;
}
Beispiel #6
0
 $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"];
Beispiel #7
0
    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>