PHP MySQL::BuildSQLUpdate方法代码示例

/**
 * set cleared state for exp entry
 *
 * @param integer $id -> ID of record
 * @param boolean $cleared -> true if record is cleared, otherwise false
 * @global array  $kga kimai-global-array
 * @author sl
 */
function export_expense_set_cleared($id, $cleared)
{
    global $kga, $database;
    $conn = $database->getConnectionHandler();
    $table = $kga['server_prefix'] . "expenses";
    $values['cleared'] = $cleared ? 1 : 0;
    $filter['expenseID'] = MySQL::SQLValue($id, MySQL::SQLVALUE_NUMBER);
    $query = MySQL::BuildSQLUpdate($table, $values, $filter);
    if ($conn->Query($query)) {
        return true;
    } else {
        return false;
    }
}

/**
 * save deselection of columns
 *
 * @param string  $header -> header name
 * @global array  $kga kimai-global-array
 * @global array  $all_column_headers array containing all columns
 * @author sl
 */
function xp_toggle_header($header)
{
    global $kga, $conn, $all_column_headers;
    $header_number = array_search($header, $all_column_headers);
    $table = $kga['server_prefix'] . "preferences";
    $values['value'] = "`value`^POWER(2,{$header_number})";
    $filter['userID'] = MySQL::SQLValue($kga['usr']['usr_ID'], MySQL::SQLVALUE_NUMBER);
    $filter['var'] = MySQL::SQLValue('export_disabled_columns');
    $query = MySQL::BuildSQLUpdate($table, $values, $filter);
    if ($conn->Query($query)) {
        return true;
    } else {
        return false;
    }
}

 /**
  * @param $membershipRoleID
  * @param $data
  * @return object
  */
 public function membership_role_edit($membershipRoleID, $data)
 {
     $values = array();
     foreach ($data as $key => $value) {
         if ($key == 'name') {
             $values[$key] = MySQL::SQLValue($value);
         } else {
             $values[$key] = MySQL::SQLValue($value, MySQL::SQLVALUE_NUMBER);
         }
     }
     $filter['membershipRoleID'] = MySQL::SQLValue($membershipRoleID, MySQL::SQLVALUE_NUMBER);
     $table = $this->kga['server_prefix'] . "membershipRoles";
     $query = MySQL::BuildSQLUpdate($table, $values, $filter);
     return $this->conn->Query($query);
 }

/**
 * Update the ban status of a user. This increments the ban counter.
 * Optionally it sets the start time of the ban to the current time.
 * 
 * @global array $kga          kimai global array
 * @global array $conn         MySQL connection
 * @author sl
 */
function loginUpdateBan($userId, $resetTime = false)
{
    global $kga, $conn;
    $table = $kga['server_prefix'] . "usr";
    $filter['usr_ID'] = MySQL::SQLValue($userId);
    $values['ban'] = "ban+1";
    if ($resetTime) {
        $values['banTime'] = MySQL::SQLValue(time(), MySQL::SQLVALUE_NUMBER);
    }
    $table = $kga['server_prefix'] . "usr";
    $query = MySQL::BuildSQLUpdate($table, $values, $filter);
    $conn->Query($query);
}

/**
 * edit exp entry 
 *
 * @param integer $id ID of record
 * @global array $kga kimai-global-array
 * @param integer $data  array with new record data
 * @author th
 */
function expense_edit($id, $data)
{
    global $kga, $database;
    $conn = $database->getConnectionHandler();
    $data = $database->clean_data($data);
    $original_array = expense_get($id);
    $new_array = array();
    foreach ($original_array as $key => $value) {
        if (isset($data[$key]) == true) {
            $new_array[$key] = $data[$key];
        } else {
            $new_array[$key] = $original_array[$key];
        }
    }
    $values['projectID'] = MySQL::SQLValue($new_array['projectID'], MySQL::SQLVALUE_NUMBER);
    $values['designation'] = MySQL::SQLValue($new_array['designation']);
    $values['comment'] = MySQL::SQLValue($new_array['comment']);
    $values['commentType'] = MySQL::SQLValue($new_array['commentType'], MySQL::SQLVALUE_NUMBER);
    $values['timestamp'] = MySQL::SQLValue($new_array['timestamp'], MySQL::SQLVALUE_NUMBER);
    $values['multiplier'] = MySQL::SQLValue($new_array['multiplier'], MySQL::SQLVALUE_NUMBER);
    $values['value'] = MySQL::SQLValue($new_array['value'], MySQL::SQLVALUE_NUMBER);
    $values['refundable'] = MySQL::SQLValue($new_array['refundable'], MySQL::SQLVALUE_NUMBER);
    $filter['expenseID'] = MySQL::SQLValue($id, MySQL::SQLVALUE_NUMBER);
    $table = $kga['server_prefix'] . "expenses";
    $query = MySQL::BuildSQLUpdate($table, $values, $filter);
    $success = true;
    if (!$conn->Query($query)) {
        $success = false;
    }
    return $success;
}

/**
 * edit exp entry 
 *
 * @param integer $id ID of record
 * @global array $kga kimai-global-array
 * @param integer $data  array with new record data
 * @author th
 */
function exp_edit_record($id, $data)
{
    global $kga, $conn;
    $data = clean_data($data);
    $original_array = exp_get_data($id);
    $new_array = array();
    foreach ($original_array as $key => $value) {
        if (isset($data[$key]) == true) {
            $new_array[$key] = $data[$key];
        } else {
            $new_array[$key] = $original_array[$key];
        }
    }
    $values['exp_pctID'] = MySQL::SQLValue($new_array['exp_pctID'], MySQL::SQLVALUE_NUMBER);
    $values['exp_designation'] = MySQL::SQLValue($new_array['exp_designation']);
    $values['exp_comment'] = MySQL::SQLValue($new_array['exp_comment']);
    $values['exp_comment_type'] = MySQL::SQLValue($new_array['exp_comment_type'], MySQL::SQLVALUE_NUMBER);
    $values['exp_timestamp'] = MySQL::SQLValue($new_array['exp_timestamp'], MySQL::SQLVALUE_NUMBER);
    $values['exp_multiplier'] = MySQL::SQLValue($new_array['exp_multiplier'], MySQL::SQLVALUE_NUMBER);
    $values['exp_value'] = MySQL::SQLValue($new_array['exp_value'], MySQL::SQLVALUE_NUMBER);
    $values['exp_refundable'] = MySQL::SQLValue($new_array['exp_refundable'], MySQL::SQLVALUE_NUMBER);
    $filter['exp_ID'] = MySQL::SQLValue($id, MySQL::SQLVALUE_NUMBER);
    $table = $kga['server_prefix'] . "exp";
    $query = MySQL::BuildSQLUpdate($table, $values, $filter);
    $success = true;
    if (!$conn->Query($query)) {
        $success = false;
    }
    if ($success) {
        if (!$conn->TransactionEnd()) {
            $conn->Kill();
        }
    } else {
        if (!$conn->TransactionRollback()) {
            $conn->Kill();
        }
    }
    return $success;
    $original_array = exp_get_data($id);
    $new_array = array();
    foreach ($original_array as $key => $value) {
        if (isset($data[$key]) == true) {
            $new_array[$key] = $data[$key];
        } else {
            $new_array[$key] = $original_array[$key];
        }
    }
}

// Want to know if you are connected? Use IsConnected()
echo "Are we connected? ";
var_dump($db->IsConnected());
echo "\n<br />\n";
// --------------------------------------------------------------------------
// Now we can generate SQL statements from arrays!
// Let's create an array for the examples
// $arrayVariable["column name"] = formatted SQL value
$values["Name"] = MySQL::SQLValue("Violet");
$values["Age"] = MySQL::SQLValue(777, MySQL::SQLVALUE_NUMBER);
// Echo out some SQL statements
echo "<pre>" . "\n";
echo MySQL::BuildSQLDelete("Test", $values) . "\n<br />\n";
echo MySQL::BuildSQLInsert("Test", $values) . "\n<br />\n";
echo MySQL::BuildSQLSelect("Test", $values) . "\n<br />\n";
echo MySQL::BuildSQLUpdate("Test", $values, $values) . "\n<br />\n";
echo MySQL::BuildSQLWhereClause($values) . "\n<br />\n";
echo "</pre>" . "\n";
// Or create more advanced SQL SELECT statements
$columns = array("Name", "Age");
$sort = "Name";
$limit = 10;
echo MySQL::BuildSQLSelect("Test", $values, $columns, $sort, true, $limit);
echo "\n<br />\n";
$columns = array("Color Name" => "Name", "Total Age" => "Age");
$sort = array("Age", "Name");
$limit = "10, 20";
echo MySQL::BuildSQLSelect("Test", $values, $columns, $sort, false, $limit);
echo "\n<br />\n";
// The following methods take the same parameters and automatically execute!
// $db->DeleteRows("Test", $values);

 /**
  * edit exp entry
  *
  * @param integer $id
  * @param array $data
  * @return object
  */
 public function expense_edit($id, array $data)
 {
     $conn = $this->conn;
     $data = $this->dbLayer->clean_data($data);
     $original_array = $this->expense_get($id);
     $new_array = array();
     foreach ($original_array as $key => $value) {
         if (isset($data[$key]) == true) {
             $new_array[$key] = $data[$key];
         } else {
             $new_array[$key] = $original_array[$key];
         }
     }
     $values['projectID'] = MySQL::SQLValue($new_array['projectID'], MySQL::SQLVALUE_NUMBER);
     $values['designation'] = MySQL::SQLValue($new_array['designation']);
     $values['comment'] = MySQL::SQLValue($new_array['comment']);
     $values['commentType'] = MySQL::SQLValue($new_array['commentType'], MySQL::SQLVALUE_NUMBER);
     $values['timestamp'] = MySQL::SQLValue($new_array['timestamp'], MySQL::SQLVALUE_NUMBER);
     $values['multiplier'] = MySQL::SQLValue($new_array['multiplier'], MySQL::SQLVALUE_NUMBER);
     $values['value'] = MySQL::SQLValue($new_array['value'], MySQL::SQLVALUE_NUMBER);
     $values['refundable'] = MySQL::SQLValue($new_array['refundable'], MySQL::SQLVALUE_NUMBER);
     $values['cleared'] = MySQL::SQLValue($new_array['cleared'], MySQL::SQLVALUE_NUMBER);
     $filter['expenseID'] = MySQL::SQLValue($id, MySQL::SQLVALUE_NUMBER);
     $table = $this->getExpenseTable();
     $query = MySQL::BuildSQLUpdate($table, $values, $filter);
     return $conn->Query($query);
 }