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("f**k" => "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='******'") or die('Échec requête : ' . pg_last_error()); if ($query_photo != false) { $msg = "Envoi du fichier \"" . $_FILES[$file]['name'] . "\" réussi"; } else { $msg = "Photo envoyée mais non ajouté à la base"; } pg_free_result($query_photo); } else { $msg = "<span class=\"red\">Envoi du fichier \"" . $_FILES[$file]['name'] . "\" échoué</span>"; } } else { $msg = "Photo de profil supprimé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='******' 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='******' 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='******'"; $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; }
/** * @throws SQLException * @return void */ protected function initTables() { include_once 'creole/drivers/pgsql/metadata/PgSQLTableInfo.php'; // Get Database Version // TODO: www.php.net/pg_version $result = pg_query($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_query($this->conn->getResource(), "SELECT c.oid, \n\t\t\t\t\t\t\t\t\t\t\t\t\t\tcase when n.nspname='public' then c.relname else n.nspname||'.'||c.relname end as relname \n\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM pg_class c join pg_namespace n on (c.relnamespace=n.oid)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE c.relkind = 'r'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t AND n.nspname NOT IN ('information_schema','pg_catalog')\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t AND n.nspname NOT LIKE 'pg_temp%'\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t AND n.nspname NOT LIKE 'pg_toast%'\n\t\t\t\t\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; }
protected function release_resource() { if (is_resource($this->_result)) { pg_free_result($this->_result); } $this->_result = null; }
/** * Free resources associated with this result set. */ function free() { if (!$this->freed && is_resource($this->rs)) { pg_free_result($this->rs); } $this->freed = true; }
function search_db($netid) { global $dbfields; if (!preg_match("/\\A[a-z]{3}([0-9]*)\\Z/i", $netid)) { return array(); } init_db(); $query = "select * from users where netid='" . pg_escape_string($netid) . "'"; $result = pg_query($query); $present = pg_fetch_array($result, null, PGSQL_ASSOC); if ($present == null) { return array(); } $person = new Person($netid); pg_free_result($result); foreach ($dbfields as $f) { $query = "select * from " . $f . " where netid='" . pg_escape_string($netid) . "'"; $result = pg_query($query); while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) { $value = $line[$f]; if ($line["ldap"] === "f") { $person->db_fields[$f][] = $value; } else { $person->ldap_fields[$f][] = $value; } } pg_free_result($result); } $person->refresh_db(); return array($person); }
function daemon_scanTime() { require "daemon_db_init.php"; // to initialize database connection // 1, get timestamp // $time = date("j F Y h:i:s A"); // $timestamp = "'" . date('YmdGis') . "'"; global $timestamp; // 2, check if the table "dameontimestamp" in the database "vanguardhe" $query_exist = "SELECT relname FROM pg_class \n\tWHERE relname = 'dameontimestamp';"; $result_exist = pg_query($query_exist) or die('Query failed: ' . pg_last_error()); $exist = ''; while ($row_exist = pg_fetch_object($result_exist)) { $exist = $row_exist->relname; } // // 3, if not existed, create it if ($exist != "dameontimestamp") { # code... $query_construct = "CREATE TABLE PUBLIC.dameontimestamp(\n\t\t\tscanid SERIAL PRIMARY KEY,\n\t\t\ttime TEXT,\n\t\t\talarmcount TEXT );"; $result_construct = pg_query($query_construct) or die('Query failed: ' . pg_last_error()); pg_free_result($result_construct); } // 4, insert data into the table $query_insert = "INSERT INTO PUBLIC.dameontimestamp (time) VALUES ({$timestamp});"; $result_insert = pg_query($query_insert) or die('Query failed: ' . pg_last_error()); pg_free_result($result_exist); pg_free_result($result_insert); }
function free_result() { if (is_resource($this->result_id)) { pg_free_result($this->result_id); $this->result_id = FALSE; } }
public function query($sql) { $resource = pg_query($this->link, $sql); if ($resource) { if (is_resource($resource)) { $i = 0; $data = array(); while ($result = pg_fetch_assoc($resource)) { $data[$i] = $result; $i++; } pg_free_result($resource); $query = new \stdClass(); $query->row = isset($data[0]) ? $data[0] : array(); $query->rows = $data; $query->num_rows = $i; unset($data); return $query; } else { return true; } } else { trigger_error('Error: ' . pg_result_error($this->link) . '<br />' . $sql); exit; } }
/** * \brief Generate the text for this plugin. */ function Output() { if ($this->State != PLUGIN_STATE_READY) { return; } global $PG_CONN; $V = ""; switch ($this->OutputType) { case "XML": break; case "HTML": /* Get uploadtree_pk's for all debian uploads */ $sql = "SELECT uploadtree_pk, upload_pk, upload_filename FROM upload INNER JOIN uploadtree ON upload_fk=upload_pk AND parent IS NULL WHERE upload_filename LIKE '%debian%';"; $result = pg_query($PG_CONN, $sql); DBCheckResult($result, $sql, __FILE__, __LINE__); $row = pg_fetch_assoc($result); if (empty($row['upload_pk'])) { $V .= "There are no uploads with 'debian' in the description."; } else { /* Loop thru results to obtain all licenses in their uploadtree recs*/ $Lics = array(); while ($Row = pg_fetch_array($result)) { if (empty($Row['upload_pk'])) { continue; } else { LicenseGetAll($Row[uploadtree_pk], $Lics); } $V .= "<option value='" . $Row['upload_pk'] . "'>{$Name}</option>\n"; } $V .= "</select><P />\n"; arsort($Lics); $V .= "<table border=1>\n"; foreach ($Lics as $key => $value) { if ($key == " Total ") { $V .= "<tr><th>{$key}<th>{$value}\n"; } else { if (plugin_find_id('search_file_by_license') >= 0) { $V .= "<tr><td><a href='/repo/?mod=search_file_by_license&item={$Row['uploadtree_pk']}&lic=" . urlencode($key) . "'>{$key}</a><td align='right'>{$value}\n"; } else { $V .= "<tr><td>{$key}<td align='right'>{$value}\n"; } } } $V .= "</table>\n"; // print "<pre>"; print_r($Lics); print "</pre>"; } pg_free_result($result); break; case "Text": break; default: break; } if (!$this->OutputToStdout) { return $V; } print $V; return; }
function show_error_page($dbconn, $diary_login, $message) { $sql = "SELECT u.uid, s.page_error FROM " . TABLE_SETTINGS . " AS s, " . TABLE_USERS . " AS u WHERE u.login='******' AND s.uid=u.uid LIMIT 1"; $result = pg_query($sql) or die(pg_last_error($dbconn)); $data = pg_fetch_object($result, NULL); pg_free_result($result); echo assign_vars($data->page_error, array('{login}' => $diary_login, '{errormessage}' => $message)); }
/** * This function frees the command reference. * * @access public * @override */ public function free() { if ($this->command !== NULL) { @pg_free_result($this->command); $this->command = NULL; $this->record = FALSE; } }
protected function _getLastInsertId($queryId) { $row = pg_fetch_row($queryId); pg_free_result($queryId); if (is_array($row)) { return $row[0]; } }
public function close() { if (false === $this->result) { return; } pg_free_result($this->result); $this->result = false; }
function pgerr( $err ) { $e = pg_last_error(); if( $e == '' ) $e = $err; if( $e == '' ) $e = 'unknown pgsql error'; pg_free_result( pg_query( 'ROLLBACK' ) ); err( $e ); }
public function close() { if ($this->result) { pg_free_result($this->result); $this->result = null; } $this->current = null; $this->blobs = null; }
function PQuery($query) { require "config.php"; global $connection; //error_log( $query ); //print $query."<br>"; $res = pg_query($connection, $query); if (!$res) { //print_r (debug_backtrace()); list(, $caller) = debug_backtrace(false); error_log($caller['function'] . ', ' . $caller['line'] . ": {$query}"); $result['count'] = 0; $result['rows'] = NULL; $result['error'] = pg_last_error($connection); return $result; } $result['count'] = pg_num_rows($res); $i = 0; $rowarr = array(); while ($row = pg_fetch_array($res, NULL, PGSQL_ASSOC)) { $rowarr[$i++] = $row; } pg_free_result($res); $result['rows'] = $rowarr; return $result; }