function db_query($sql, $proxyFields)
{
$con = dbConnect();
if ($con == null) {
return null;
}
foreach (array_keys($proxyFields) as $key) {
$value = mysqli_real_escape_string($con, $proxyFields[$key]);
if (is_string($value)) {
$value = "\"{$value}\"";
}
$sql = str_replace($key, $value, $sql);
}
$result = mysqli_query($con, $sql);
if (!$result) {
echo "has error";
print_r(debug_backtrace());
$firstCall = end(debug_backtrace());
$errMsg = "Error calling " . $firstCall["function"];
$errMsg .= " on line " . $firstCall["line"];
$errMsg .= " in " . $firstCall["file"] . ": " . mysqli_error($con);
dbErrorMsg($errMsg);
return false;
} else {
if (strpos($sql, "INSERT") !== false) {
return mysqli_insert_id($con);
} else {
return new result($result);
}
}
dbClose($con);
}
function createSchema()
{
// we want to know when we use variables that aren't defined
error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
// First, create a connection to the database with the given config information
$con = mysqli_connect(DB_HOST, DB_USERNAME, DB_PASSWORD);
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error() . "\n";
return;
}
// set database for use for table creation
$sql = "USE " . DB_DATABASE;
if (!mysqli_query($con, $sql)) {
dbErrorMsg("Error selecting datbase" . DB_DATABASE . mysqli_error($con));
return;
}
db_createOutreachTable($con);
db_createProfilesTable($con);
db_createTeamsTable($con);
db_createUsersVsTeamsTable($con);
db_createEmailsVsUsersTable($con);
db_createMediaTable($con);
db_createHourCountingTable($con);
db_createTimesVsOutreachTable($con);
db_createUsersVsOutreachTable($con);
db_createNotificationsTable($con);
db_createOutreachTagsTable($con);
db_createTagsVsOutreachTable($con);
db_createPermissionsTable($con);
db_createPermissionsVsRolesTable($con);
db_createUsersVsRolesTable($con);
db_createRolesTable($con);
db_createOldHoursVsTeamsTable($con);
// finally close the connection
mysqli_close($con);
}
function dbGetUserPrimaryEmail($UID)
{
$sql = "SELECT mail FROM users ";
$sql .= "WHERE uid = :UID;";
$proxyFields = array(":UID" => $UID);
$result = db_query($sql, $proxyFields)->fetchAll(PDO::FETCH_ASSOC);
if ($result) {
return $result[0]['mail'];
} else {
dbErrorMsg("User {$UID} has no primary email!");
}
return false;
}
function dbGenericInsert($row, $table)
{
$proxyFields = array();
$sql = 'INSERT ';
$sql .= "INTO {$table} (";
$first = true;
foreach ($row as $field => $value) {
if (!$first) {
$sql .= ",";
}
$sql .= $field;
$first = false;
}
$sql .= ") VALUES (";
$first = true;
foreach ($row as $field => $value) {
if (!$first) {
$sql .= ",";
}
if ($value === null || $value == '') {
$sql .= "NULL";
} else {
$sql .= ":{$field}";
$proxyFields[":" . $field] = $value;
}
$first = false;
}
$sql .= ") ";
try {
$newID = db_query($sql, $proxyFields);
// this variable is meaningless within Drupal
if (function_exists("dpm")) {
// in Drupal land
return Database::getConnection()->lastInsertId();
} else {
return $newID;
}
} catch (\PDOException $e) {
$error = $e->getMessage();
dbErrorMsg($error);
return false;
}
}
function dbUpdate($table, $modifyFields, $idName, $idValue, $idName2 = null, $idValue2 = null)
{
$hadError = false;
$con = dbConnect();
if ($con == null) {
return null;
}
if ($idName == "OID") {
dbOrderNormalize2SQL($modifyFields);
} else {
if ($idName == "CID") {
dbCustomerNormalize2SQL($modifyFields);
}
}
$i = 1;
$sql = "UPDATE {$table} SET ";
foreach ($modifyFields as $column => $value) {
// escape the incoming value to prevent SQL injection
$safeValue = mysqli_real_escape_string($con, $value);
// note that the PHP triple-equal is used here, it
// specifies that the $value has to be explicitely null
// as opposed to something that "looks" like null - like zero
if ($value === null) {
$sql .= "{$column} = NULL";
} else {
$sql .= "{$column} = '{$safeValue}'";
}
if ($i != sizeOf($modifyFields)) {
$sql .= ", ";
} else {
$sql .= " ";
}
$i++;
}
$sql .= "WHERE {$idName} = {$idValue} ";
if ($idName2 != null && $idValue2 != null) {
$sql .= "AND {$idName2} = {$idValue2} ";
} else {
$sql .= "LIMIT 1;";
}
$result = mysqli_query($con, $sql);
if (!$result) {
dbErrorMsg("Error during sql insert in dbUpdate({$dbname})" . mysqli_error($con));
$hadError = true;
}
dbClose($con);
if (!$hadError) {
return $idValue;
} else {
return 0;
}
}
