function query_start($query)
{
// For reg expressions
$query = trim($query);
// Query was an insert, delete, update, replace
if (preg_match("/^(insert|delete|update|replace)\\s+/i", $query)) {
return false;
}
// Flush cached values..
$this->flush();
// Log how the function was called
$this->func_call = "\$db->query_start(\"{$query}\")";
// Keep track of the last query for debug..
$this->last_query = $query;
// Perform the query via std pg_query function..
if (!($this->result = @pg_query($this->dbh, $query))) {
$this->print_error();
return false;
}
$this->num_queries++;
// =======================================================
// Take note of column info
$i = 0;
while ($i < @pg_num_fields($this->result)) {
$this->col_info[$i]->name = pg_field_name($this->result, $i);
$this->col_info[$i]->type = pg_field_type($this->result, $i);
$this->col_info[$i]->size = pg_field_size($this->result, $i);
$i++;
}
$this->last_result = array();
$this->num_rows = 0;
// If debug ALL queries
$this->trace || $this->debug_all ? $this->debug() : null;
return true;
}
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 printUserLog()
{
//db connection
$conn = pg_connect(HOST . " " . DBNAME . " " . USERNAME . " " . PASSWORD) or die('Could not connect: ' . pg_last_error());
//query the database
$result = pg_prepare($conn, "getLog", "SELECT * FROM lab8.log\n\t\t\tWHERE username LIKE \$1") or die("getLog prepare fail: " . pg_last_error());
$result = pg_execute($conn, "getLog", array($_SESSION['user'])) or die("getLog execute fail: " . pg_last_error());
//Printing results in HTML
echo "<br>There where <em>" . pg_num_rows($result) . "</em> rows returned<br><br>\n";
echo "<table class='tablestuff' border='1'>";
//account for added form row
echo "<tr>";
//checking the number of fields return to populate header
$numFields = pg_num_fields($result);
//populating the header
for ($i = 0; $i < $numFields; $i++) {
$fieldName = pg_field_name($result, $i);
echo "<th width=\"135\">" . $fieldName . "</th>\n";
}
echo "</tr>";
//populating table with the results
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>{$col_value}</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
pg_free_result($result);
//close connection
pg_close($conn);
}
function exportWithQuery($qry, $file_name, $type)
{
$tmprst = pg_query($qry);
$header = '<center><table width="100%">';
$num_field = pg_num_fields($tmprst);
while ($row = pg_fetch_array($tmprst)) {
$body .= "<tr>";
for ($i = 0; $i < $num_field; $i++) {
$body .= "<td>" . $row[$i] . "</td>";
}
$body .= "</tr>";
}
if ($type == 'xls') {
$this->setHeaderXLS($file_name);
} else {
if ($type == 'doc') {
$this->setHeaderDoc($file_name);
} else {
if ($type == 'csv') {
$this->setHeaderCSV($file_name);
}
}
}
echo $header . $body . "</table>";
}
function PG_QueryStart($SQL, &$aFields, &$link, &$Errors)
{
$gMyHOST = "127.0.0.1";
$gMyDB = "osm";
$gMyUSER = "******";
$gMyPASS = "******";
$Errors = "";
$result = false;
$conn_string = "host={$gMyHOST} port=5432 dbname={$gMyDB} user={$gMyUSER} password={$gMyPASS}";
$link = pg_connect($conn_string);
if ($link === false) {
$Errors .= "Impossibile connettersi a '{$gMyHOST}' come '{$gMyUSER}'";
} else {
$result = pg_query($SQL);
if ($result === false) {
$Errors .= ($Errors[0] ? "" : "<br>") . "Errore durante la query '{$SQL}': " . pg_last_error();
} else {
$nFields = pg_num_fields($result);
for ($ind = 0; $ind < $nFields; $ind++) {
$aFields[$ind] = array(pg_field_name($result, $ind), pg_field_type($result, $ind));
}
}
}
return $result;
}
/**
* Constructor.
*
* @param resource $resource SQL result resource.
* @param TypeConverterFactory $factory
* @param array $types Types information, used to convert output values (overrides auto-generated types).
* @throws exceptions\InvalidArgumentException
*/
public function __construct($resource, TypeConverterFactory $factory, array $types = array())
{
if (!is_resource($resource) || 'pgsql result' !== get_resource_type($resource)) {
throw new exceptions\InvalidArgumentException(sprintf("%s requires a query result resource, '%s' given", __CLASS__, is_resource($resource) ? 'resource(' . get_resource_type($resource) . ')' : gettype($resource)));
}
$this->_resource = $resource;
$this->_converterFactory = $factory;
$this->_numRows = pg_num_rows($this->_resource);
$this->_numFields = pg_num_fields($this->_resource);
$oids = array();
for ($i = 0; $i < $this->_numFields; $i++) {
$this->_namesHash[pg_field_name($this->_resource, $i)] = $i;
$oids[$i] = pg_field_type_oid($this->_resource, $i);
}
// first set the explicitly given types...
foreach ($types as $index => $type) {
$this->setType($index, $type);
}
// ...then use type factory to create default converters
for ($i = 0; $i < $this->_numFields; $i++) {
if (!isset($this->_converters[$i])) {
$this->_converters[$i] = $this->_converterFactory->getConverter($oids[$i]);
}
}
}
public static function castResult($result, array $a, Stub $stub, $isNested)
{
$a['num rows'] = pg_num_rows($result);
$a['status'] = pg_result_status($result);
if (isset(self::$resultStatus[$a['status']])) {
$a['status'] = new ConstStub(self::$resultStatus[$a['status']], $a['status']);
}
$a['command-completion tag'] = pg_result_status($result, PGSQL_STATUS_STRING);
if (-1 === $a['num rows']) {
foreach (self::$diagCodes as $k => $v) {
$a['error'][$k] = pg_result_error_field($result, $v);
}
}
$a['affected rows'] = pg_affected_rows($result);
$a['last OID'] = pg_last_oid($result);
$fields = pg_num_fields($result);
for ($i = 0; $i < $fields; ++$i) {
$field = array('name' => pg_field_name($result, $i), 'table' => sprintf('%s (OID: %s)', pg_field_table($result, $i), pg_field_table($result, $i, true)), 'type' => sprintf('%s (OID: %s)', pg_field_type($result, $i), pg_field_type_oid($result, $i)), 'nullable' => (bool) pg_field_is_null($result, $i), 'storage' => pg_field_size($result, $i) . ' bytes', 'display' => pg_field_prtlen($result, $i) . ' chars');
if (' (OID: )' === $field['table']) {
$field['table'] = null;
}
if ('-1 bytes' === $field['storage']) {
$field['storage'] = 'variable size';
} elseif ('1 bytes' === $field['storage']) {
$field['storage'] = '1 byte';
}
if ('1 chars' === $field['display']) {
$field['display'] = '1 char';
}
$a['fields'][] = new EnumStub($field);
}
return $a;
}
public function columnCount()
{
if ($this->_result) {
return pg_num_fields($this->_result);
}
return 0;
}
/**
* Public method:
* Checks if query was valid and returns how may fields returns
* this->columnCount( void ):Void
*/
function columnCount()
{
$result = 0;
if (!is_null($this->__result)) {
$result = pg_num_fields($this->__result);
}
return $result;
}
function getColumnCount($table)
{
global $connection;
$connection;
$result = pg_query($connection, "SELECT * FROM {$table}");
$num = pg_num_fields($result);
return $num;
pg_close($connection);
}
function numCols()
{
if (IFPG) {
return pg_num_fields($this->result);
}
if (IFMY) {
return mysql_num_fields($this->result);
}
}
function getTitulos($result)
{
$num = pg_num_fields($result);
$titulos = array();
for ($i = 0; $i < $num; $i++) {
$titulos[$i] = pg_field_name($result, $i);
}
return $titulos;
}
/**
* Constructor
*
* @param resource handle
*/
public function __construct($result, TimeZone $tz = NULL)
{
$fields = array();
if (is_resource($result)) {
for ($i = 0, $num = pg_num_fields($result); $i < $num; $i++) {
$fields[pg_field_name($result, $i)] = pg_field_type($result, $i);
}
}
parent::__construct($result, $fields, $tz);
}
public function getTypes()
{
$types = [];
$count = pg_num_fields($this->result);
for ($i = 0; $i < $count; $i++) {
$nativeType = pg_field_type($this->result, $i);
$types[pg_field_name($this->result, $i)] = [0 => isset(self::$types[$nativeType]) ? self::$types[$nativeType] : self::TYPE_AS_IS, 1 => $nativeType];
}
return $types;
}
function _performGetBlobFieldNames($result)
{
$blobFields = array();
for ($i = pg_num_fields($result) - 1; $i >= 0; $i--) {
$type = pg_field_type($result, $i);
if (strpos($type, "BLOB") !== false) {
$blobFields[] = pg_field_name($result, $i);
}
}
return $blobFields;
}
function GetFields()
{
$_fields = array();
$_result = pg_query($this->_Link, $this->SelectCommand);
$_num_fields = pg_num_fields($_result);
for ($i = 0; $i < $_num_fields; $i++) {
$_field = array("Name" => pg_field_name($_result, $i), "Type" => pg_field_type($_result, $i), "Not_Null" => 0);
array_push($_fields, $_field);
}
return $_fields;
}
/**
* Constructor method for the adapter. This constructor implements the setting of the
* 3 required properties for the object.
*
* @param resource $d The datasource resource
*/
function pgsqlAdapter($d)
{
parent::RecordSetAdapter($d);
$fieldcount = pg_num_fields($d);
$ob = "";
$be = $this->isBigEndian;
$fc = pack('N', $fieldcount);
if (pg_num_rows($d) > 0) {
pg_result_seek($d, 0);
while ($line = pg_fetch_row($d)) {
// write all of the array elements
$ob .= "\n" . $fc;
foreach ($line as $value) {
// write all of the array elements
if (is_string($value)) {
// type as string
$os = $this->_directCharsetHandler->transliterate($value);
//string flag, string length, and string
$len = strlen($os);
if ($len < 65536) {
$ob .= "" . pack('n', $len) . $os;
} else {
$ob .= "\f" . pack('N', $len) . $os;
}
} elseif (is_float($value) || is_int($value)) {
// type as double
$b = pack('d', $value);
// pack the bytes
if ($be) {
// if we are a big-endian processor
$r = strrev($b);
} else {
// add the bytes to the output
$r = $b;
}
$ob .= "" . $r;
} elseif (is_bool($value)) {
//type as bool
$ob .= "";
$ob .= pack('c', $value);
} elseif (is_null($value)) {
// null
$ob .= "";
}
}
}
}
$this->serializedData = $ob;
$this->numRows = pg_num_rows($d);
for ($i = 0; $i < $fieldcount; $i++) {
$this->columnNames[$i] = $this->_charsetHandler->transliterate(pg_field_name($d, $i));
}
}
public function __construct($resource)
{
if (!is_resource($resource) || is_resource($resource) && get_resource_type($resource) !== self::EXPECTED_RESOURCE_TYPE) {
throw new DatabaseException('Invalid resource type passed, expected "' . $this->expectedResource . '" and got "' . get_resource_type($resource) . '"');
}
$this->resource = $resource;
$this->count = pg_num_rows($this->resource);
// Build an array of column types
$this->fieldCount = pg_num_fields($this->resource);
for ($i = 0; $i < $this->fieldCount; ++$i) {
$this->types[$i] = [pg_field_name($this->resource, $i), pg_field_type($this->resource, $i)];
}
}
/**
* Structure of our fields (type, length and null)
*
* @param resource $resource
* @return array
*/
public function field_structures($resource)
{
$result = [];
if ($resource) {
for ($i = 0; $i < pg_num_fields($resource); $i++) {
$name = pg_field_name($resource, $i);
$result[$name]['type'] = pg_field_type($resource, $i);
$result[$name]['null'] = pg_field_is_null($resource, $i);
$result[$name]['length'] = pg_field_size($resource, $i);
}
}
return $result;
}
public function __construct($result)
{
$this->result = $result;
$this->row = 0;
$this->max = pg_num_rows($result);
if ($this->max > 0) {
$this->types = [];
$num_fields = pg_num_fields($result);
for ($i = 0; $i < $num_fields; $i++) {
$this->types[pg_field_name($result, $i)] = pg_field_type($result, $i);
}
}
}
/**
* Build a new recordset to iterate over.
*
* @param resource $result A pg_query() result object to create a recordset from.
*/
public function __construct($result)
{
$this->result = $result;
// Find out if there are any blobs.
$numfields = pg_num_fields($result);
for ($i = 0; $i < $numfields; $i++) {
$type = pg_field_type($result, $i);
if ($type == 'bytea') {
$this->blobs[] = pg_field_name($result, $i);
}
}
$this->current = $this->fetch_next();
}
function getfldnames($tab)
{
$names = "";
$rs = pg_exec("SELECT * FROM {$tab}");
for ($i = 0; $i < pg_num_fields($rs); $i++) {
$name = pg_field_name($rs, $i);
if ($i + 1 == pg_num_fields($rs)) {
$names .= "{$name}";
} else {
$names .= "{$name},";
}
}
return $names;
}
public function __construct($result, $bytea_oid)
{
$this->result = $result;
$this->bytea_oid = $bytea_oid;
// find out if there are any blobs
$numrows = pg_num_fields($result);
for ($i = 0; $i < $numrows; $i++) {
$type_oid = pg_field_type_oid($result, $i);
if ($type_oid == $this->bytea_oid) {
$this->blobs[] = pg_field_name($result, $i);
}
}
$this->current = $this->fetch_next();
}
/**
* Constructor method for the adapter. This constructor implements the setting of the
* 3 required properties for the object.
*
* @param resource $d The datasource resource
*/
function pgsqlAdapter($d)
{
parent::RecordSetAdapter($d);
$fieldcount = pg_num_fields($d);
for ($i = 0; $i < $fieldcount; $i++) {
$this->columns[] = pg_field_name($d, $i);
}
if (pg_num_rows($d) > 0) {
pg_result_seek($d, 0);
while ($line = pg_fetch_row($d)) {
$this->rows[] = $line;
}
}
}
function spittable($query)
{
$res = pg_query($query);
$num = pg_num_rows($res);
$cols = pg_num_fields($res);
for ($i = 0; $i < $num; $i++) {
$c = "";
for ($j = 0; $j < $cols; $j++) {
$c .= pg_result($res, $i, $j) . "|";
}
echo "{$c}\n";
}
echo "\n";
exit;
}
public static function select($query = "")
{
$list = array();
if ($query !== "") {
self::connect();
if (($result = pg_query(self::$connection, $query)) === FALSE) {
throw new DBException(pg_last_error(self::$connection) . "\n" . $query);
}
if (pg_num_fields($result)) {
while ($row = pg_fetch_array($result, NULL, PGSQL_ASSOC)) {
$list[] = $row;
}
}
}
return $list;
}
function fieldsMemory($rs, $idx, $formata = false, $mostra = false, $lEncode = false)
{
$oFields = new _db_fields();
$numFields = pg_num_fields($rs);
$iTotalLinhas = pg_num_rows($rs);
for ($i = 0; $i < $numFields; $i++) {
$sValor = "";
$sFieldName = @pg_field_name($rs, $i);
$sFieldType = @pg_field_type($rs, $i);
if ($iTotalLinhas > 0) {
$sValor = trim(@pg_result($rs, $idx, $sFieldName));
}
if ($formata) {
switch ($sFieldType) {
case "date":
if ($sValor != null) {
$sValor = implode('/', array_reverse(explode("-", $sValor)));
}
break;
default:
$sValor = stripslashes($sValor);
break;
}
}
if ($mostra) {
echo $sFieldName . " => " . $sValor . " <br>";
}
if ($lEncode) {
switch ($sFieldType) {
case "bpchar":
$sValor = urlencode($sValor);
break;
case "varchar":
$sValor = urlencode($sValor);
break;
case "text":
$sValor = urlencode($sValor);
break;
}
}
$oFields->{$sFieldName} = $sValor;
}
return $oFields;
}
function consulta_por_rectangulo($tabla, $wkt)
{
global $db;
$result = pg_query($db, "\n\t\t\t\tSELECT * FROM {$tabla} \n\t\t\t\tWHERE \n\t\t\t\t\tst_intersects(\n\t\t\t\t\tST_geomfromtext('{$wkt}',4326),\n\t\t\t\t\tgeom\n\t\t\t\t\t)\n\t\t\t\tLIMIT 200\n\t\t\t\t");
if (!$result) {
echo "An error occurred.\n";
exit;
}
$nro_campos = pg_num_fields($result);
$nro_registros = pg_num_rows($result);
$header = '<tr>';
while ($i < $nro_campos) {
$fieldName = pg_field_name($result, $i);
if ($fieldName != 'geom') {
$header .= '<th>' . $fieldName . '</td>';
}
$i++;
}
$header .= '</tr>';
$cuerpo = '';
while ($row = pg_fetch_row($result)) {
$cuerpo .= '<tr>';
$count = count($row);
$i = 0;
while ($i < $nro_campos) {
if (pg_field_name($result, $i) != 'geom') {
$cuerpo .= '<td>' . $row[$i] . '</td>';
}
$i++;
}
$cuerpo .= '</tr>';
}
echo '<div class="">';
echo '<table class="table">';
echo "<thead>";
echo $header;
echo "</thead>";
echo "<tdoby>";
echo $cuerpo;
echo "</tbody>";
echo "</table>";
echo '</div>';
}
/**
* Dynamic Get Function Override
*
* @param $name
* A string containing the name of the property to get.
* @return
* Value of the property.
*/
public function __get($propertyName)
{
global $firePHP;
if ($propertyName == 'columns') {
if (!isset($this->_columns)) {
//---- Get Columns
$this->_columns = new anvilCollection();
$i = 0;
// $sql = 'SHOW COLUMNS FROM ';
while ($i < pg_num_fields($this->result)) {
$newColumn = new anvilData_postgresql_Column(pg_field_name($this->result, $i), pg_field_type($this->result, $i));
$this->_columns->add($newColumn);
$i++;
}
}
return $this->_columns;
} else {
return parent::__get($propertyName);
}
}
/**
* @param resource from pg_query() or pg_get_result()
* @param string SQL used to create this result
* @param resource from pg_connect() or pg_pconnect()
* @param boolean|string
* @return void
*/
public static function factory($result, $sql, $link, $return_objects)
{
// Detect errors, initialize values
$postgresql_result = new Database_Postgresql_Result($result, $sql, $link, $return_objects);
// No rows, nothing to transform
if ($postgresql_result->total_rows === 0) {
return $postgresql_result;
}
$booleans = FALSE;
// Create list of boolean field names
for ($i = pg_num_fields($result) - 1; $i >= 0; --$i) {
if (pg_field_type($result, $i) === 'bool') {
$booleans[] = pg_field_name($result, $i);
}
}
// No booleans to transform, regular result set is fastest
if ($booleans === FALSE) {
return $postgresql_result;
}
return new Database_Postgresql_Result_Boolean($postgresql_result, $booleans);
}
