PHP SqlQuery函数代码示例

function GenerateDownload()
{
    $loc = rmabs(__FILE__ . ".GenerateDownload");
    $sql = 'SELECT * FROM Users';
    $result = SqlQuery($loc, $sql);
    $first = true;
    header('Content-Type: application/csv');
    header('Content-Disposition: attachment; filename="Users_' . date("Y-m-d") . '.csv";');
    $ff = fopen('php://memory', 'w');
    //rewind($f);
    $fieldnames = array();
    while ($row = $result->fetch_assoc()) {
        if ($first) {
            $fieldnames = array_keys($row);
            fputcsv($ff, $fieldnames, ",");
            $first = false;
        }
        $data = array();
        foreach ($fieldnames as $k) {
            if (isset($row[$k])) {
                $data[] = $row[$k];
            } else {
                $data[] = "";
            }
        }
        fputcsv($ff, $data);
    }
    fseek($ff, 0);
    header('Content-Type: application/csv');
    header('Content-Disposition: attachment; filename="Users_' . date("Y-m-d") . '.csv";');
    fpassthru($ff);
}

 function up()
 {
     $sql = "CREATE TABLE priority\n\t\t\t\t(\n\t\t\t\t\tid int2 primary key,\n\t\t\t\t\tname text\n\t\t\t\t)";
     SqlAlterSchema($sql);
     SqlQuery("insert into priority (id, name) values (1, 'Low')", array());
     SqlQuery("insert into priority (id, name) values (2, 'Medium')", array());
     SqlQuery("insert into priority (id, name) values (3, 'High')", array());
     $sql = "ALTER TABLE request add column priority_id int2 references priority not null default 1";
     SqlAlterSchema($sql);
 }

function get_all_accounts()
{
    $loc = rmabs(__FILE__ . ".get_all_accounts");
    $sql = "SELECT UserID from Users";
    $result = SqlQuery($loc, $sql);
    while ($row = $result->fetch_assoc()) {
        $aws[] = intval($row["UserID"]);
    }
    return $aws;
}

function GetWorkOrderPrereqInfo($userid)
{
    $loc = "userlib.php->GetUserInfo";
    $sql = 'SELECT * FROM UserView WHERE UserID=' . SqlClean($userid);
    $result = SqlQuery($loc, $sql);
    if ($result->num_rows != 1) {
        return false;
    }
    $row = $result->fetch_assoc();
    return $row;
}

function changeactive($wid, $active)
{
    global $username;
    $loc = rmabs(__FILE__ . ".changeactive");
    $sql = 'UPDATE WorkOrders SET Active=' . intval($active) . ' WHERE WID=' . intval($wid);
    $result = SqlQuery($loc, $sql);
    $action = "archvied";
    if ($active) {
        $action = "resurrected";
    }
    return 'Work Order ' . intval($wid) . ' has been ' . $action . ' by ' . $username . '.';
}

function GetPicCaption($wid, $picid)
{
    $loc = rmabs(__FILE__ . ".GetPicCaption");
    if (empty($wid) || empty($picid)) {
        return "";
    }
    $sql = 'SELECT * From AppendedData Where WID=' . intval($wid) . ' AND PicID=' . intval($picid);
    $result = SqlQuery($loc, $sql);
    if ($result->num_rows <= 0) {
        return "";
    }
    $row = $result->fetch_assoc();
    return $row["TextInfo"];
}

function delete_workorder($woinfo)
{
    global $username;
    $loc = rmabs(__FILE__ . ".delete_workorder");
    $wid = $woinfo["WID"];
    $sql = "DELETE FROM Assignments WHERE WID=" . intval($wid);
    SqlQuery($loc, $sql);
    $sql = "DELETE FROM AppendedData WHERE WID=" . intval($wid);
    SqlQuery($loc, $sql);
    $sql = "DELETE FROM WorkOrders WHERE WID=" . intval($wid);
    SqlQuery($loc, $sql);
    $widstr = WIDStr($wid, $woinfo["Revision"], $woinfo["IsApproved"]);
    $msg = "Work Order " . $widstr . " deleted by " . $username . ".";
    log_msg($loc, $msg);
    return $msg;
}

function GetAllWorkers()
{
    $loc = rmabs(__FILE__ . "GetAllWorkers");
    $sql = 'SELECT * FROM AllActiveUsersView ORDER BY LastName, FirstName';
    $result = SqlQuery($loc, $sql);
    $d = array();
    while ($row = $result->fetch_assoc()) {
        $tags = ArrayFromSlashStr($row["Tags"]);
        if (CheckArrayForEasyMatch($tags, "worker")) {
            $row["AbbrivatedName"] = MakeAbbrivatedName($row);
            $d[] = $row;
        }
    }
    return $d;
}

function DeleteAllPrefsForUser($userid)
{
    $loc = "preflib.php->DeleteAllPrefsForUser";
    $sql = 'DELETE FROM Prefs WHERE UserID=' . intval($userid);
    $result = SqlQuery($loc, $sql);
    log_msg($loc, 'All preferences deleted successfully for user ' . intval($userid));
}

function graphsGetRanges($name) {
  global $is_lbf, $pid, $table;
  if ($is_lbf) {
    // Like below, but for LBF data.
    $ranges = sqlQuery("SELECT " .
      "MAX(CONVERT(ld.field_value, SIGNED)) AS max_" . add_escape_custom($name) . ", " .
      "MAX(UNIX_TIMESTAMP(f.date)) AS max_date, " .
      "MIN(UNIX_TIMESTAMP(f.date)) AS min_date " .
      "FROM forms AS f, lbf_data AS ld WHERE " .
      "f.pid = ? AND " .
      "f.formdir = ? AND " .
      "f.deleted = 0 AND " .
      "ld.form_id = f.form_id AND " .
      "ld.field_id = ? AND " .
      "ld.field_value != '0'",
      array($pid, $table, $name));
  }
  else {
    $ranges = SqlQuery("SELECT " .
      "MAX(CONVERT(" . add_escape_custom($name) . ",SIGNED)) AS " .
      "max_" . add_escape_custom($name) . ", " .
      "MAX(UNIX_TIMESTAMP(date)) as max_date, " .
      "MIN(UNIX_TIMESTAMP(date)) as min_date  " .
      "FROM " . add_escape_custom($table) . " " .
		  "WHERE " . add_escape_custom($name) . " != 0 " .
		  "AND pid = ?", array($pid));
  }
  return $ranges;
}

function GetPicIDForUserID($userid)
{
    $loc = 'piclib.php->GetPicIDForUserID';
    $sql = 'SELECT PicID From UserPics WHERE UserID=' . intval($userid);
    $result = SqlQuery($loc, $sql);
    while ($row = $result->fetch_assoc()) {
        $picid = $row["PicID"];
        return $picid;
    }
    return 0;
}

function MakeGifImages()
{
    $loc = 'badges_showall.php->MakeAllBadges';
    $sql = 'SELECT * FROM UserView ORDER BY BadgeID';
    $result = SqlQuery($loc, $sql);
    $nempty = 0;
    $nmade = 0;
    $nfail = 0;
    while ($row = $result->fetch_assoc()) {
        if ($row["Active"] == false) {
            continue;
        }
        $tags = ArrayFromSlashStr($row["Tags"]);
        if (!in_array("member", $tags)) {
            continue;
        }
        $badgeid = $row["BadgeID"];
        if (empty($badgeid)) {
            $nempty++;
            continue;
        }
        $r = MakeGif($row);
        if ($r === true) {
            $nmade++;
        } else {
            $nfail++;
        }
    }
    $status = 'Images Made: ' . $nmade . ', Members without BadgeIDs: ' . $nempty;
    if ($nfail > 0) {
        $status .= ', Failures: ' . $nfail . '. (See sys log!)';
    }
    log_msg($loc, array('All gif images made!', $status));
    return $status;
}

function ShowRawScans($badgeid)
{
    $loc = "attendance_user.php->ShowRawScans()";
    $sql = 'SELECT * from RawScans WHERE BadgeID="' . $badgeid . '"';
    $result = SqlQuery($loc, $sql);
    while ($row = $result->fetch_assoc()) {
        echo '<div style="display: block; height: 20px; ">';
        echo '<br>' . "\n";
        echo '<div style="float: left; width: 180px;">';
        echo $row["ScanTime"];
        echo '</div>' . "\n";
        $dir = "";
        if ($row["Direction"] == 0) {
            $dir = "Scan In";
        }
        if ($row["Direction"] == 1) {
            $dir = "Scan Out";
        }
        if ($row["Direction"] == 2) {
            $dir = "?";
        }
        echo '<div style="float: left; width: 100px;">';
        echo $dir;
        echo '</div>' . "\n";
        echo '<div style="float: left; width: 80px;">';
        echo $row["Flags"];
        echo '</div>' . "\n";
        echo '<div style="clear: both;"></div>' . "\n";
        echo '</div>';
    }
}

 function down()
 {
     SqlQuery("drop table entry");
     SqlQuery("drop table project");
     SqlQuery("drop table person");
 }

    exit;
}
// If blood pressure, then collect the other reading to allow graphing both in same graph
$isBP = 0;
if ($name == "bps" || $name == "bpd") {
    // Set BP flag and collect other pressure reading
    $isBP = 1;
    if ($name == "bps") {
        $name_alt = "bpd";
    }
    if ($name == "bpd") {
        $name_alt = "bps";
    }
    // Collect the pertinent vitals and ranges.
    $values_alt = SqlStatement("SELECT " . add_escape_custom($name_alt) . ", " . "UNIX_TIMESTAMP(date) as unix_date " . "FROM " . add_escape_custom($table) . " " . "WHERE pid=? ORDER BY date", array($pid));
    $ranges_alt = SqlQuery("SELECT MAX(CONVERT(" . add_escape_custom($name_alt) . ",SIGNED)) AS " . "max_" . add_escape_custom($name_alt) . ", " . "MAX(UNIX_TIMESTAMP(date)) as max_date, " . "MIN(UNIX_TIMESTAMP(date)) as min_date  " . "FROM " . add_escape_custom($table) . " " . "WHERE pid=?", array($pid));
}
// Prepare look and feel of data points
$s = new scatter_line('#DB1750', 2);
$def = new hollow_dot();
$def->size(4)->halo_size(3)->tooltip('#val#<br>#date:Y-m-d H:i#');
$s->set_default_dot_style($def);
if ($isBP) {
    //set up the other blood pressure line
    $s_alt = new scatter_line('#0000FF', 2);
    $s_alt->set_default_dot_style($def);
}
// Prepare and insert data
$data = array();
while ($row = sqlFetchArray($values)) {
    if ($row["{$name}"]) {