[STATIC] Builds a SQL UPDATE statement
| public static BuildSQLUpdate ( string $tableName, array $valuesArray, array $whereArray = null ) : string | ||
| $tableName | string | The name of the table |
| $valuesArray | array | An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, ect) |
| $whereArray | array | (Optional) An associative array containing the column names as keys and values as data. The values must be SQL ready (i.e. quotes around strings, formatted dates, ect). If not specified then all values in the table are updated. |
| return | string | Returns a SQL UPDATE statement |
/**
* 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);
}
