PHP pg_free_result函数代码示例

 public static function getPrivacyData()
 {
     global $dbconn, $ixmaps_debug_mode;
     $sql1 = "SELECT privacy_stars.* FROM privacy_stars";
     $sql2 = "SELECT privacy_scores.* FROM privacy_scores order by asn, star_id";
     //echo $sql;
     $result1 = pg_query($dbconn, $sql1) or die('Query privacy_stars failed: ' . pg_last_error());
     $result2 = pg_query($dbconn, $sql2) or die('Query privacy_scores failed: ' . pg_last_error());
     // loop and format the data
     $stars = array();
     $scores = array();
     // stars
     while ($line1 = pg_fetch_array($result1, null, PGSQL_ASSOC)) {
         $stars[$line1['star_id']] = $line1;
     }
     while ($line2 = pg_fetch_array($result2, null, PGSQL_ASSOC)) {
         $scores[$line2['asn']][] = $line2;
     }
     //$stars = pg_fetch_all($result1);
     //$scores = pg_fetch_all($result2);
     pg_free_result($result1);
     pg_free_result($result2);
     $privacy = array('stars' => $stars, 'scores' => $scores);
     pg_close($dbconn);
     //print_r($privacy);
     return $privacy;
 }

function lookup_user($usr_id, $cur_time)
{
    // check if user exists (if not, create user)
    // check if url exists (if not, create url)
    // add vote
    if ($con = connect_db('../auth.txt')) {
        $result = pg_prepare($con, "check_user", 'SELECT * FROM users where id = $1');
        $result = pg_execute($con, "check_user", array($usr_id));
        $usr_entry = pg_fetch_array($result);
        pg_free_result($result);
        if (!$usr_entry) {
            $result = pg_prepare($con, "reg_user", 'INSERT INTO users VALUES ($1, $2, $3)');
            $result = pg_execute($con, "reg_user", array($usr_id, 0, (int) $cur_time));
            pg_free_result($result);
            $id = $usr_id;
            $spent = 0;
            $reg = $cur_time;
        } else {
            $id = $usr_entry[0];
            $spent = $usr_entry[1];
            $reg = $usr_entry[2];
        }
        echo json_encode(array("usr" => $id, "spent" => $spent, "reg" => $reg));
    } else {
        echo json_encode(array("fuck" => "nuts"));
    }
}

 function upload($database, $userlogin, $file, $maxsize, $extensions)
 {
     if (isset($_FILES[$file]) && $_FILES[$file]['error'] == 0) {
         $upload = false;
         $upload_dest = '../Users/Images/' . $_SESSION['login'] . '.jpg';
         if ($_FILES[$file]['size'] <= $maxsize) {
             $infosfichier = pathinfo($_FILES[$file]['name']);
             $extension_upload = $infosfichier['extension'];
             if (in_array($extension_upload, $extensions)) {
                 $upload = move_uploaded_file($_FILES[$file]['tmp_name'], $upload_dest);
             }
         } else {
             $msg = "<span class=\"red\">Photo volumineuse</span>";
         }
         if ($upload == true) {
             $photo = pg_escape_string($upload_dest);
             $query_photo = pg_query($database, "UPDATE users SET photo='{$photo}' WHERE login='{$userlogin}'") or die('Échec requête : ' . pg_last_error());
             if ($query_photo != false) {
                 $msg = "Envoi du fichier \"" . $_FILES[$file]['name'] . "\" r&eacute;ussi";
             } else {
                 $msg = "Photo envoy&eacute;e mais non ajout&eacute; &agrave; la base";
             }
             pg_free_result($query_photo);
         } else {
             $msg = "<span class=\"red\">Envoi du fichier \"" . $_FILES[$file]['name'] . "\" &eacute;chou&eacute;</span>";
         }
     } else {
         $msg = "Photo de profil supprim&eacute;e";
     }
     return $msg;
 }

function show_comments_page($dbconn, $diary_login)
{
    $nid = (int) $_GET['nid'];
    // Get Note
    $sql = "SELECT u.uid, n.* FROM " . TABLE_USERS . " AS u, " . TABLE_NOTES . " AS n WHERE u.login='" . $diary_login . "' AND n.uid=u.uid AND n.nid='" . $nid . "' LIMIT 1";
    $result = pg_query($sql) or die(pg_last_error($dbconn));
    if (pg_num_rows($result) == 0) {
        show_error_page($dbconn, $diary_login, "no such note!");
        return;
    } else {
        $datanote = pg_fetch_object($result, NULL);
        pg_free_result($result);
        $sql = "SELECT u.uid, s.format_note, s.page_comments, s.format_comment FROM " . TABLE_SETTINGS . " AS s, " . TABLE_USERS . " AS u WHERE u.login='" . $diary_login . "' AND s.uid=u.uid LIMIT 1";
        $result = pg_query($sql) or die(pg_last_error($dbconn));
        $dataformat = pg_fetch_object($result, NULL);
        // Strip link to add comment and number of comments
        $dataformat->format_note = preg_replace('/(<a.* href="{hrefcomment}")(.+)(.*>)/', '', $dataformat->format_note);
        $dataformat->format_note = preg_replace('/(.?{commentscount}.?)/', '', $dataformat->format_note);
        $note = assign_vars($dataformat->format_note, array('{subject}' => $datanote->subject, '{contents}' => $datanote->contents, '{date}' => date("d-m-Y", $datanote->timestamp), '{time}' => date("H:i:s", $datanote->timestamp)));
        $comments = get_comments($dbconn, $diary_login, $dataformat->format_comment, $nid);
        $sql = "SELECT COUNT(c.*) AS commentscount FROM " . TABLE_USERS . " AS u, " . TABLE_COMMENTS . " AS c WHERE c.nid='" . $nid . "' AND c.uid=u.uid AND u.login='" . $diary_login . "'";
        $result = pg_query($sql) or die(pg_last_error($dbconn));
        $datacount = pg_fetch_object($result, NULL);
        pg_free_result($result);
        $commentscount = (int) $datacount->commentscount;
        echo assign_vars($dataformat->page_comments, array('{login}' => $diary_login, '{note}' => $note, '{comments}' => $comments, '{commentscount}' => $commentscount, '{hrefcommentadd}' => 'http://' . $_SERVER['SERVER_NAME'] . '/' . PAGE_COMMENTS . '&nid=' . $nid . '&action=add'));
    }
}

function postgis_query($string, $pgc = NULL)
{
    $pgct = $pgc;
    if ($pgc == NULL) {
        $pgct = postgis_connect();
    }
    @($result = pg_query($pgct, $string));
    if ($pgc == NULL) {
        pg_close($pgct);
    }
    $retval = array();
    if (!$result) {
        return $retval;
    }
    $arow = array();
    for ($i = 0; $i < pg_num_fields($result); $i++) {
        $arow[pg_field_name($result, $i)] = pg_field_type($result, $i);
    }
    $retval[0] = $arow;
    $ctr = 1;
    while ($row = pg_fetch_row($result)) {
        $arow = array();
        for ($i = 0; $i < count($row); $i++) {
            $arow[pg_field_name($result, $i)] = $row[$i];
        }
        $retval[$ctr] = $arow;
        $ctr++;
    }
    pg_free_result($result);
    return $retval;
}

 function auth($user, $pass, $args)
 {
     $host = access_query("authpgsqlhost", 0);
     $dbuser = access_query("authpgsqluser", 0);
     $dbpass = access_query("authpgsqlpass", 0);
     $dbname = access_query("authpgsqldb", 0);
     $tbname = access_query("authpgsqltable", 0);
     $lname = access_query("authpgsqllogincolumn", 0);
     $pname = access_query("authpgsqlpasscolumn", 0);
     $ps = trim($pass);
     switch (strtolower(access_query("authpgsqlpasstype", 0))) {
         case "md5":
             $pstr = md5($ps);
             break;
         case "plain":
         default:
             $pstr = $ps;
     }
     if (is_callable("pg_connect")) {
         if ($cid = @pg_connect("host={$host} user={$dbuser} password={$dbpass} dbname={$dbname}")) {
             if ($q = @pg_query($cid, "SELECT * FROM {$tbname} WHERE {$lname} = '{$user}' AND {$pname} = '{$pstr}'")) {
                 $r = pg_num_rows($q);
                 pg_free_result($q);
                 $auth = $r > 0;
             } else {
                 techo("WARN: mod_auth_pgsql could not fetch '{$lname}' and '{$pname}' from table '{$tbname}'", NW_EL_WARNING);
             }
         } else {
             techo("WARN: mod_auth_pgsql could not connect to database '{$dbname}@{$host}'", NW_EL_WARNING);
         }
     } else {
         techo("WARN: postgresql extension not built in your PHP binary", NW_EL_WARNING);
     }
     return $auth;
 }

function makeCTFParams()
{
    $cnx = __dbConnect();
    if (!$cnx) {
        print "<b>DATABASE CONNECTION ERROR</b>";
        exit(1);
    }
    $query = pg_query("SELECT * FROM main.ctf_map_def ORDER BY name");
    if (!$query) {
        print "<b>DATABASE ERROR</b>";
        exit(1);
    }
    $ctfMaps = array();
    while ($r = pg_fetch_assoc($query)) {
        $ctfMaps[$r['id']] = $r;
        $query2 = pg_query("SELECT COUNT(*) FROM main.ctf_map_layout WHERE map={$r['id']} AND spawn_here");
        if (!$query2) {
            print "<b>DATABASE ERROR</b>";
            exit(1);
        }
        list($ctfMaps[$r['id']]['players']) = pg_fetch_array($query2);
        pg_free_result($query2);
    }
    pg_free_result($query);
    pg_close($cnx);
    $map = $ctfMaps[$_SESSION['lw_new_game']['ctfmap']];
    $cParams = $_SESSION['lw_new_game']['ctfparams'];
    $params = array('usemap' => $map['id'], 'maxplayers' => $map['players'], 'norealloc' => 1, 'partialtechs' => 0, 'lockalliances' => $map['alliances'], 'alliancecap' => 0, 'victory' => 2, 'novacation' => 1);
    foreach ($cParams as $p => $v) {
        $params[$p] = $v;
    }
    return $params;
}

 public function free()
 {
     if (is_resource($this->result)) {
         pg_free_result($this->result);
     }
     $this->result = false;
 }

 protected function get_new_id()
 {
     $res = pg_query($this->connection, "SELECT LASTVAL() AS seq");
     $data = pg_fetch_assoc($res);
     pg_free_result($res);
     return $data['seq'];
 }

function get_points($table_name, $show_json = FALSE)
{
    // Connecting, selecting database
    $dbconn = pg_connect("host=localhost dbname=steki_db user=postgres password=password") or die('Could not connect: ' . pg_last_error());
    // Performing SQL query
    $query = 'SELECT array_to_json(array_agg(row_to_json(t))) as ' . $table_name . '_json
    FROM (
      SELECT id, latlong[0] as latitude, latlong[1] as longitude, brand, rating, votes, nam as name, descr as description FROM "' . $table_name . '"
    ) t';
    //$query = 'SELECT row_to_json("'.$table_name.'") as '.$table_name.'_json FROM "'.$table_name.'"';
    $result = pg_query($query) or die('Query failed: ' . pg_last_error());
    // Printing results in HTML
    $json = pg_fetch_array($result, null, PGSQL_ASSOC);
    if ($show_json) {
        echo json_encode(array_values($json));
    } else {
        $str = implode("*", $json);
        $out = htmlspecialchars($str);
        echo $out;
    }
    // Free resultset
    pg_free_result($result);
    // Closing connection
    pg_close($dbconn);
    //return $json;
}

 /**
  * \brief test for GetPkgMimetypes()
  */
 function test_GetPkgMimetypes()
 {
     print "test function GetPkgMimetypes()\n";
     global $PG_CONN;
     #prepare database testdata
     $mimeType = "application/x-rpm";
     /** delete test data pre testing */
     $sql = "DELETE FROM mimetype where mimetype_name in ('{$mimeType}');";
     $result = pg_query($PG_CONN, $sql);
     pg_free_result($result);
     /** insert on record */
     $sql = "INSERT INTO mimetype(mimetype_pk, mimetype_name) VALUES(10000, '{$mimeType}');";
     $result = pg_query($PG_CONN, $sql);
     pg_free_result($result);
     #begin test GetPkgMimetypes()
     $sql = "select * from mimetype where\n             mimetype_name='application/x-rpm'";
     $result = pg_query($PG_CONN, $sql);
     DBCheckResult($result, $sql, __FILE__, __LINE__);
     $row = pg_fetch_assoc($result);
     $expected = $row['mimetype_pk'];
     pg_free_result($result);
     $result = GetPkgMimetypes();
     $this->assertContains($expected, $result);
     /** delete test data post testing */
     $sql = "DELETE FROM mimetype where mimetype_name in ('{$mimeType}');";
     $result = pg_query($PG_CONN, $sql);
     pg_free_result($result);
 }

/**
 * \brief 
 * delete from copyright where pfile_fk not in (select pfile_pk from pfile)
 * add foreign constraint on copyright pfile_fk if not exist
 *
 * \return 0 on success, 1 on failure
 **/
function Migrate_20_25($Verbose)
{
    global $PG_CONN;
    $sql = "select count(*) from pg_class where relname='copyright';";
    $result = pg_query($PG_CONN, $sql);
    DBCheckResult($result, $sql, __FILE__, __LINE__);
    $row = pg_fetch_assoc($result);
    pg_free_result($result);
    if (1 > $row['count']) {
        return 0;
        // fresh install, even no copyright table
    }
    $sql = "delete from copyright where pfile_fk not in (select pfile_pk from pfile);";
    $result = pg_query($PG_CONN, $sql);
    DBCheckResult($result, $sql, __FILE__, __LINE__);
    pg_free_result($result);
    /** add foreign key CONSTRAINT on pfile_fk of copyrigyt table when not exist */
    $sql = "SELECT conname from pg_constraint where conname= 'copyright_pfile_fk_fkey';";
    $conresult = pg_query($PG_CONN, $sql);
    DBCheckResult($conresult, $sql, __FILE__, __LINE__);
    if (pg_num_rows($conresult) == 0) {
        $sql = "ALTER TABLE copyright ADD CONSTRAINT copyright_pfile_fk_fkey FOREIGN KEY (pfile_fk) REFERENCES pfile (pfile_pk) ON DELETE CASCADE; ";
        $result = pg_query($PG_CONN, $sql);
        DBCheckResult($result, $sql, __FILE__, __LINE__);
        pg_free_result($result);
        print "add contr\n";
    }
    pg_free_result($conresult);
    return 0;
}

function setActiveGroup($verbose)
{
    global $dbManager;
    $stmt = __METHOD__;
    $sql = "SELECT user_pk,group_pk FROM users LEFT JOIN groups ON group_name=user_name WHERE group_fk IS NULL";
    $dbManager->prepare($stmt, $sql);
    $res = $dbManager->execute($stmt);
    if (pg_num_rows($res) == 0) {
        pg_free_result($res);
        return 0;
    }
    $userGroupMap = pg_fetch_all($res);
    pg_free_result($res);
    $selectStmt = __METHOD__ . '.select';
    $sql = "SELECT user_fk,min(group_fk) group_fk FROM group_user_member WHERE user_fk=\$1";
    $updateStmt = __METHOD__ . '.update';
    $dbManager->prepare($updateStmt, "UPDATE users SET group_fk=\$2 WHERE user_pk=\$1");
    foreach ($userGroupMap as $row) {
        if (!empty($row['group_pk'])) {
            pg_free_result($dbManager->execute($updateStmt, $row));
            continue;
        }
        $rowrow = $dbManager->getSingleRow($sql, array($row['user_pk']), $selectStmt);
        pg_fetch_result($dbManager->execute($updateStmt, $rowrow));
    }
}

 function free()
 {
     if ($this->_handle) {
         pg_free_result($this->_handle);
     }
     $this->_handle = null;
 }

 /**
  * @throws SQLException
  * @return void
  */
 protected function initTables()
 {
     include_once 'creole/drivers/pgsql/metadata/PgSQLTableInfo.php';
     // Get Database Version
     $result = pg_exec($this->conn->getResource(), "SELECT version() as ver");
     if (!$result) {
         throw new SQLException("Failed to select database version");
     }
     // if (!$result)
     $row = pg_fetch_assoc($result, 0);
     $arrVersion = sscanf($row['ver'], '%*s %d.%d');
     $version = sprintf("%d.%d", $arrVersion[0], $arrVersion[1]);
     // Clean up
     $arrVersion = null;
     $row = null;
     pg_free_result($result);
     $result = null;
     $result = pg_exec($this->conn->getResource(), "SELECT oid, relname FROM pg_class\n\t\t\t\t\t\t\t\t\t\tWHERE relkind = 'r' AND relnamespace = (SELECT oid\n\t\t\t\t\t\t\t\t\t\tFROM pg_namespace\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t     nspname NOT IN ('information_schema','pg_catalog')\n\t\t\t\t\t\t\t\t\t\t     AND nspname NOT LIKE 'pg_temp%'\n\t\t\t\t\t\t\t\t\t\t     AND nspname NOT LIKE 'pg_toast%'\n\t\t\t\t\t\t\t\t\t\tLIMIT 1)\n\t\t\t\t\t\t\t\t\t\tORDER BY relname");
     if (!$result) {
         throw new SQLException("Could not list tables", pg_last_error($this->dblink));
     }
     while ($row = pg_fetch_assoc($result)) {
         $this->tables[strtoupper($row['relname'])] = new PgSQLTableInfo($this, $row['relname'], $version, $row['oid']);
     }
     $this->tablesLoaded = true;
 }