php.ComLanka.com PHP Tutorial ,PHP source code, PHP online example

Algorithms Arrays Passwords Uncategorized
Commerce Uncategorized
Databases Abstraction Layers Informix LDAP MS SQL MySQL ODBC PostgreSQL Uncategorized
Date & Time Calculators Conversion Translation Uncategorized
Discussion Chat Forums Guestbooks Polls Uncategorized
Email Validation Uncategorized
External Uncategorized
Files and Directories Uncategorized
Games Uncategorized
Graphics 3-D Graphs Resizing Thumbnails Uncategorized
HTML Forms Menus & Navigation Source Viewers Tables Templates Uncategorized
HTTP Authentication Cookies File Uploading Uncategorized
I18N Uncategorized
Math Uncategorized
Miscellaneous Uncategorized
Searching and Trees Uncategorized
Security Uncategorized
Text Uncategorized
Utilities Calendars Counters Link Checkers Weather Uncategorized
XML Uncategorized

php sample source codes
This is an example code for getting PostgreSQL metadata from its system tables. I've seen several metadata functions that are not using PostgreSQL system tables, so I posted this. This function can get metadata MUCH faster than using many pg_Field*(). <?php /* * Function MetaData() * This function can get metadata MUCH faster than using many pg_Field*(). * * == Slow way == * $id = pg_Exec($this-link_id, "select * from $table"); * pg_FieldNames($id,$n) for getting field names. * pg_FieldSize($id,$n) for getttig field sizes. * and so on. * This can be really slow when there are many rows in the table. * Note: This method is still useful to get metadata from joined result. * * == Better way == * Get metadata from database's system catalogs. * * $Id: metadata.php,v 1.1 2001/03/16 01:58:05 www Exp $ * */ ///// // Returns array contains metadata // $db: Database connection resource // $table: Table name function MetaData($db, $table) { $rows = 0; // Number of rows $qid = 0; // Query result resource $meta = array(); // Metadata array - return value // See PostgreSQL developer manual (www.postgresql.org) for system table spec. // Get catalog data from system tables. $sql = 'SELECT a.attnum, a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef FROM pg_class as c, pg_attribute a, pg_type t WHERE a.attnum > 0 and a.attrelid = c.oid and c.relname = '."'$table'".' and a.atttypid = t.oid order by a.attnum'; $qid = pg_Exec($db, $sql); // Check error if (!is_resource($qid)) { print('MetaData(): Query Error - table does not exist'); return null; } $rows = pg_NumRows($qid); // Store meta data for ($i = 0; $i < $rows; $i++) { $field_name = pg_Result($qid,$i,1); // Field Name $meta[$field_name]['id'] = pg_Result($qid,$i,0); // Attrbute ID $meta[$field_name]['type'] = pg_Result($qid,$i,2); // Data type name $meta[$field_name]['len'] = pg_Result($qid,$i,3); // Length: -1 for variable length $meta[$field_name]['modifier'] = pg_Result($qid,$i,4); // Modifier $meta[$field_name]['notnull'] = (pg_Result($qid,$i,5) === 't' ? TRUE : FALSE); // Not NULL? $meta[$field_name]['hasdefault'] = (pg_Result($qid,$i,6) === 't' ? TRUE : FALSE); // Has default value? } // Clean up. PHP4 reference count code would be smart enough to do this, though. pg_FreeResult($qid); return $meta; } //// Test code //// $dbName = 'db_session'; // Change this to your db name $dbUser = 'yohgaki'; // Change this to your db user name $tableName = 'sys_session'; // Change this to your table name $db = pg_connect('host=dev dbname='.$dbName.' user='.$dbUser); $meta = metadata($db, $tableName); print("<pre>\n"); print_r($meta); print("</pre>\n"); ?>
Tired with adding quotes around vars for SQL query? Do you want to make sure supplied values are valid for fields? Do you want to use database default value, if user send no values and default value is defined for the field? Then this function might be useful. <?php /* * Description: * array get_db_values(resource db_link_id, string table_name, array fields) * * Parameters: * db_linkid PostgreSQL link resource * table_name table name - needed to get metadata. * fields array(<field name> => <user input>) All field name should much database field name. * * Return Value: * array(<field name> => <value for INSERT/UPDATE>) Return NULL if error. * * It converts assoc array data so that values are sutable for INSERT/UPDATE * SQL statements. (WHERE clause also) * All array key should match database field names. Return NULL if error. * * It checks if there is a default field value in the table definition. If there * is, it does not return those fields. * If there is no input (null string for field), it set NULL for the field. * If quote is required, add slashes and put quotes. * * Format checks are limited. Complex format checks should be done * by other functions/class other than this function. * (For example, HTML FORM class) * * Not all PostgreSQL data types/formats are supported. * OID is treated as invalid. You need to modify to allow if you want to * update/insert OID type. * * Function name sounds odd to me. Anyone have better name for it? * email: yohgaki@hotmail.com * */ function Get_DB_Values($db, $table, $fields) { // Get Metadata $meta = get_Metadata($db, $table); if (!$meta) { trigger_error('Get_DB_Values(): Failed to getting metada for '. $table, E_USER_WARNING); return NULL; } // Check values foreach($fields as $k => $v) { if (!isset($meta[$k])) { trigger_error('Get_DB_Values(): Non-existent field. Table: '. $table .'. Field: '.$k, E_USER_WARNING); return NULL; } $v = trim($v); // Value is NULL and default is defined, use DB default. if ($v !== '' || !$meta[$k]['hasdefault']) { switch ($meta[$k]['type']) { case 'bool': // Does not support numeric type. Must be string. $v = strtolower($v); if ($v === '') { $ret[$k] = 'NULL'; } elseif ($v == 't' || $v == 'true' || $v == 'y' || $v == 'yes' || $v == 'on') { $ret[$k] = 'TRUE'; } elseif ($v == 'f' || $v == 'false' || $v == 'n' || $v == 'no' || $v == 'off') { $ret[$k] = 'FALSE'; } else { trigger_error('Get_DB_Values(): BOOLEAN type has invalid value. ', E_USER_WARNING); return NULL; } break; case 'cidr': case 'inet': if ($v === '') { $ret[$k] = 'NULL'; } // Check format - 10.10.10.10 or 10.10.10.10/32 elseif (!ereg('^([0-9]{1,3}\.){3}[0-9]{1,3}(/[0-9]{1,2}){0,1}$',$v)) { trigger_error('Get_DB_Values(): INET/CIDR type has invalid format. ', E_USER_WARNING); return NULL; } else { $ret[$k] = "'$v'"; } break; case 'float': case 'float8': case 'numeric': case 'money': if ($v === '') { $ret[$k] = 'NULL'; } // Check format - Does not support '1.111E+10' notation. elseif (!ereg('^[0-9]*\.[0-9]*$|^[0-9]*$', $v)) { trigger_error('Get_DB_Values(): FLOAT/NUMERIC/MONEY type has invalid value. ', E_USER_WARNING); return NULL; } else { $ret[$k] = $v; } break; case 'interval': // Only allow INTEGER for interval case 'int': case 'int2': case 'int4': case 'int8': if ($v === '') { $ret[$k] = 'NULL'; } // Check format elseif (!ereg('^[0-9]*$',$v)) { trigger_error('Get_DB_Values(): INTEGER/INTERVAL type has invalid value. ', E_USER_WARNING); return NULL; } else { $ret[$k] = $v; } break; case 'bpchar': // char type case 'char': case 'text': case 'varchar': // NO length check - char/varchar will be truncated by DB if it's too long. $ret[$k] = ($v !== '') ? "'".addslashes($v)."'" : 'NULL'; break; case 'timestamp': case 'datetime': case 'date': // Check format - "2001/02/11 14:59:01", "2001/4/1 15:10" or "2001/11/15". if ($v === '') { $ret[$k] = 'NULL'; } elseif (!ereg('^([0-9]{4}[/-][0-9]{1,2}[/-][0-9]{1,2})([ \t]+(([0-9]{1,2}:[0-9]{1,2}){1}(:[0-9]{1,2}){0,1})){0,1}$',$v, $regs)) { trigger_error('Get_DB_Values(): DATE/TIMESTAMP type has invalid value. ', E_USER_WARNING); return NULL; } else { $ret[$k] = "'{$regs[1]} {$regs[3]}'"; } break; case 'oid': // OID type should not be INSERTed nor UPDATEd. (Add oid type if you use oid for BLOD) case 'serial': // Serial type should not be INSERTed nor UPDATEd. So treat as invalid type. default: trigger_error('Get_DB_Values(): INVALID datatype ', E_USER_WARNING); return NULL; break; } } } return $ret; } // Return matadata // return NULL if error // This is the same function // http://www.zend.com/codex.php?id=481&single=1 function Get_MetaData($db, $table) { $rows = 0; // Number of rows $qid = NULL; // Query result resource $meta = array(); // Metadata array - return value // See PostgreSQL developer manual (www.postgresql.org) for system table spec. // Get catalog data from system tables. $query=<<<EOQ SELECT a.attname, a.attnum, t.typname, a.attlen, a.atttypmod, a.attnotNULL, a.atthasdef FROM pg_class as c, pg_attribute a, pg_type t WHERE a.attnum > 0 AND a.attrelid = c.oid AND c.relname = '$table' AND a.atttypid = t.oid ORDER BY a.attnum; EOQ; $qid = pg_Exec($db, $query); // Check error if (!is_resource($qid)) { trigger_error('Get_Metadata(): metadata query fialed ', E_USER_WARNING); return NULL; } $rows = pg_NumRows($qid); // Store meta data for ($i = 0; $i < $rows; $i++) { $field_name = pg_Result($qid,$i,0); // Field Name $meta[$field_name]['id'] = pg_Result($qid,$i,1); // Attrbute ID $meta[$field_name]['type'] = pg_Result($qid,$i,2); // Data type name $meta[$field_name]['len'] = pg_Result($qid,$i,3); // Length: -1 for variable length $meta[$field_name]['modifier'] = pg_Result($qid,$i,4); // Modifier $meta[$field_name]['notnull'] = (pg_Result($qid,$i,5) === 't' ? TRUE : FALSE); // Not NULL? $meta[$field_name]['hasdefault'] = (pg_Result($qid,$i,6) === 't' ? TRUE : FALSE); // Has default value? } pg_FreeResult($qid); return $meta; } ?>
Provides a front end scanner/parser to convert a human formated search into a TSearch2 plus ILIKE components of a SQL WHERE clause. System requirements are as follows: 1. A PostgreSQL database installation with the TSearch2 extension. 2. PHP5 (you could modify the source to run under PHP4 without much difficulty.) Features These are the primary features of the parser: * Simple to use. * Entirely written in PHP5 with no dependencies. * Allows the user to enter search strings in a natural way and can apply a default "AND" between keywords. * Provides error information suitable for user consumption. * Supports unlimited depth of expression nesting using parenthesis. * Supports exact phrase searches using double-quotes. * Returns an array of individual keywords for highlighting the result sets. The code is very basic and could probably be adaped for other uses quite easily. Generally a parser creates a parse-tree, but in this case I didn't need that step and the parser does the actual search clause generation. I code using a MVC paradigm, so I named the class as such (a model component in this case.) Please don't hesitate to contact me if you have questions or find a bug. Enjoy, Matthew <?php /* * Full Text Search Parser for TSearch2 and PostgreSQL * * Converts a human readable search string into a TSearch2 query with * ILIKE phrases. * * Copyright (c) 2006, Digital Stratum, Inc. * All rights reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions * are met: * * 1. Redistributions of source code must retain the above copyright * notice, this list of conditions and the following disclaimer. * * 2. Redistributions in binary form must reproduce the above copyright * notice, this list of conditions and the following disclaimer in * the documentation and/or other materials provided with the * distribution. * * 3. Neither the name of Digital Stratum, Inc. nor the names of its * contributors may be used to endorse or promote products derived * from this software without specific prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE * COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED * AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF * THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH * DAMAGE. */ /* * Parse Class. * * Accepts a string to be parsed and returns the TSearch2 and ILIKE * parts, as well as a list of keywords and any error messages to * the user if invalid input was found. * * Example: * require_once 'parse_model.php'; // Get input into $s_keyword via form post or command line... $s_keyword = 'computer programmer ("c++" or php)'; $o = new parse_model(); $o->b_debug = TRUE; $o->b_default_and = TRUE; if ( $s_keyword != '' ) { $o->parse($s_keyword); print "Message to user: [$o->s_error_msg]\n\n"; } */ class parse_model { // Lexeme of each token. private $s_token_string; // Current and Previous Token private $i_token; private $i_prev_token; // Lookahead array for supporting default values // between tokens. Mostly used for a default "AND" // between to keywords. private $a_lookahead; public $b_default_and; // True if there was a syntax error. private $b_syntax_error; // Stop Words private $a_stopwords; // Last two logical tokens. private $a_expr; // Input text and current position in text. private $s_keyword; private $i_index; private $b_read_eof; // Track parens for the ilike string. private $s_ilike_paren; // Field to use for the ILIKE clause. private $s_ilike_field; // Output search strings. public $a_keywords; public $s_tsearch; public $s_ilike; public $s_error_msg; public $s_error_tok; public $i_error_pos; // Enable debug information. public $b_debug; // Scanner States const START = 0; const INWORD = 1; const DONE = 2; // TOKEN TYPES private $a_tokens = array( 'BEGINFILE', 'ENDFILE', 'ERROR', 'KEYWORD', 'ANDOP', 'OROP', 'NOTOP', 'LPAREN', 'RPAREN', 'DQUOTE' ); // Bookkeeping Tokens const BEGINFILE = 0; const ENDFILE = 1; const ERROR = 2; // Reserved Word Tokens // Multicharacter Tokens const KEYWORD = 3; // Special Symbol Tokens const ANDOP = 4; const OROP = 5; const NOTOP = 6; const LPAREN = 7; const RPAREN = 8; const DQUOTE = 9; // End of File (input data) const EOF = -1; /* * Constructor - initialize the scanner and parser. */ public function __construct($s_ilike_field = 'fulltext') { // Lexeme of each token. $this->s_token_string = ''; // Current and Previous Token $this->i_token = 0; $this->i_prev_token = self::BEGINFILE; // Lookahead array for supporting default values // between tokens. Mostly used for a default "AND" // between to keywords. $this->a_lookahead = array(); $this->b_default_and = FALSE; // True if there was a syntax error. $this->b_syntax_error = FALSE; // Stop Words $this->stop_words(); // Last two logical tokens. $this->a_expr = array('last' => '', 'not' => ''); // Input text and current position in text. $this->s_keyword = ''; $this->i_index = 0; $this->b_read_eof = FALSE; // Track parens for the ilike string. $this->s_ilike_paren = ''; // Field for matching the ILIKE clause. $this->s_ilike_field = $s_ilike_field; // Output search strings. $this->a_keywords = array(); $this->s_tsearch = ''; $this->s_ilike = ''; $this->s_error_msg = ''; $this->s_error_tok = ''; $this->i_error_pos = $this->i_index; $this->b_debug = FALSE; return; } // __construct() /* * Parse the keyword string and generate the output. */ public function parse($s_original) { $this->s_keyword = strtolower($s_original); if ( $this->b_debug ) print "\nIn:\t\t\t[$this->s_keyword]\n"; // Remove everything not a-z 0-9 ( ) " + - // TSearch2 operators must NEVER be allowed to be passed: & | ! $this->s_keyword = preg_replace('/[^a-zA-Z0-9()"+-]/', ' ', $this->s_keyword); if ( $this->b_debug ) print "Strip1 (illegals):\t[$this->s_keyword]\n"; // Remove single characters. (the surrounding SPACE is required here!) if ( preg_match('/ \w{1} /', " $this->s_keyword ") ) { $this->s_error_msg = "Syntax Error: No single characters allowed in keyword search."; if ( $this->b_debug ) print "\n$this->s_error_msg\n\n"; else return FALSE; } $this->s_keyword = preg_replace('/ \w{1} /', ' ', " $this->s_keyword "); if ( $this->b_debug ) print "Strip2 (singles):\t[$this->s_keyword]\n"; if ( strlen($this->s_keyword) < 2 ) { $this->s_error_msg = "Syntax Error: Keyword search is too small."; if ( $this->b_debug ) print "$this->s_error_msg\n"; return FALSE; } // Convert word operators to TSearch2 operators. $this->s_keyword = preg_replace('/\band\b/', '& ', $this->s_keyword); $this->s_keyword = preg_replace('/\bor\b/', '| ', $this->s_keyword); $this->s_keyword = preg_replace('/\bnot\b/', '! ', $this->s_keyword); if ( $this->b_debug ) print "Converted Operators:\t[$this->s_keyword]\n"; $this->s_keyword = trim($this->s_keyword); if ( $this->b_debug ) { print "\nTokens Found:\n"; while ( ($this->i_token = $this->get_token()) != self::ENDFILE ) print 'Type: ' . $this->a_tokens[$this->i_token] . "\t$this->s_token_string\n"; } // Make sure scanner is reset. $this->i_index = 0; $this->b_read_eof = FALSE; // Initial attachement of ILIKE clause. $this->s_ilike = 'AND ('; if ( $this->b_debug ) print "\nParse Results:\n"; // Prime the parser. $this->i_token = $this->get_token(); // Possible starting tokens. switch ( $this->i_token ) { case self::KEYWORD : $this->expr(); break; case self::LPAREN : $this->expr(); break; case self::DQUOTE : $this->expr(); break; case self::NOTOP : $this->expr(); break; } if ( $this->i_token != self::ENDFILE ) { $this->syntax_error(); return FALSE; } else if ( $this->b_syntax_error == TRUE ) return FALSE; // Close the ILIKE clause if there was at least one phrase. if ( strpos($this->s_ilike, $this->s_ilike_field) == FALSE ) $this->s_ilike = ''; else $this->s_ilike .= ')'; if ( $this->b_debug ) { print "TSearch2:\t[$this->s_tsearch]\n"; print "ILIKE:\t\t[$this->s_ilike]\n"; print "Keywords:\t"; foreach ( $this->a_keywords as $s_word ) print "[$s_word] "; print "\n\n"; } // Check for stop-word only searches. $b_keyword_only = TRUE; foreach ( $this->a_keywords as $s_wordpharse ) { $a_words = explode(' ', $s_wordpharse); foreach ( $a_words as $s_word ) { if ( !isset($this->a_stopwords[$s_word]) ) { $b_keyword_only = FALSE; break 2; } } } if ( $b_keyword_only == TRUE ) { if ( $this->b_debug ) print "Syntax Error: Search string contains only stop words.\n\n"; $this->s_error_msg = "Syntax Error: Keywords used in keyword search are too vague."; return FALSE; } return TRUE; } // parse() /* * Display a syntax error. */ private function syntax_error() { $this->b_syntax_error = TRUE; if ( $this->b_debug ) { print "Syntax Error at Character " . ($this->i_index - strlen($this->s_token_string) + 1) . ": Unexpected Token -> [$this->i_token] Token String: [$this->s_token_string]\n"; print "$this->s_keyword\n" . str_repeat(' ', $this->i_index - strlen($this->s_token_string)) . "^\n"; } // Only save the first error, since most other errors will be caused by this one. if ( $this->s_error_msg == '' ) { if ( $this->i_token == self::ANDOP ) $s_token_string = "and"; else if ( $this->i_token == self::OROP ) $s_token_string = "or"; else if ( $this->i_token == self::NOTOP ) $s_token_string = "not"; else $s_token_string = $this->s_token_string; $this->s_error_msg = "Syntax Error in Keyword Phrase at Character " . ($this->i_index - strlen($this->s_token_string) + 1) . ": Unexpected [$s_token_string]"; $this->s_error_tok = $s_token_string; $this->i_error_pos = $this->i_index - strlen($this->s_token_string) + 1; } return; } // syntax_error() /* * Match an expected token and read the next token. */ private function match($i_expected) { $this->i_prev_token = $this->i_token; if ( $this->i_token == $i_expected ) { // If there is a lookahead value, use that instead of // a new token from the scanner. if ( count($this->a_lookahead) > 0 ) $this->i_token = array_shift($this->a_lookahead); else $this->i_token = $this->get_token(); } else $this->syntax_error(); return; } // match() /* * Match a quoted keyword phrase. */ private function quote_keywords() { $this->s_tsearch .= '('; // If this is the first ILIKE expression, don't include a connecting operator. // Adding open parens has been delayed till now so they can go after the // logical connnection operator (AND or OR). if ( strpos($this->s_ilike, $this->s_ilike_field) == FALSE ) $this->s_ilike .= " {$this->s_ilike_paren} {$this->s_ilike_field} {$this->a_expr['not']} ILIKE '%"; else $this->s_ilike .= " {$this->a_expr['last']} $this->s_ilike_paren {$this->s_ilike_field} {$this->a_expr['not']} ILIKE '%"; $this->s_ilike_paren = ''; $this->match(self::DQUOTE); $s_phrase = ''; do { // If this phrase is has NOT applied to it, invert the tsearch component. if ( $this->a_expr['not'] == 'NOT' ) $this->s_tsearch .= $this->s_token_string . '|'; else $this->s_tsearch .= $this->s_token_string . '&'; $this->s_ilike .= "$this->s_token_string "; $s_phrase .= $this->s_token_string . ' '; $this->match(self::KEYWORD); } while ( $this->i_token == self::KEYWORD ); $this->s_tsearch = substr($this->s_tsearch, 0, -1) . ')'; $this->s_ilike = rtrim($this->s_ilike) . "%' "; $this->a_keywords[] = trim($s_phrase); $this->match(self::DQUOTE); // Check for default values between phrases. if ( $this->b_default_and == TRUE && ($this->i_token == self::KEYWORD || $this->i_token == self::DQUOTE) ) { // Save the keyword and inject a default "AND" token. $this->a_lookahead[] = $this->i_token; $this->i_token = self::ANDOP; } return; } // quote_keywords() /* * Match an expression. */ private function expr() { $this->simple_expr(); // Prevent double NOT. while ( $this->i_token == self::NOTOP && ($this->i_prev_token == self::BEGINFILE || $this->i_prev_token == self::ANDOP || $this->i_prev_token == self::OROP || $this->i_prev_token == self::LPAREN) ) { $this->s_tsearch .= '!'; $this->a_expr['not'] = 'NOT'; $this->match(self::NOTOP); $this->simple_expr(); } return; } // expr() /* * Match a simple expression. */ private function simple_expr() { $this->factor(); while ( ($this->i_token == self::ANDOP) || ($this->i_token == self::OROP) ) { $this->a_expr['not'] = ''; switch ( $this->i_token ) { case self::ANDOP : $this->s_tsearch .= '&'; $this->a_expr['last'] = 'AND'; break ; case self::OROP : $this->s_tsearch .= '|'; $this->a_expr['last'] = 'OR'; break ; } $this->match($this->i_token); $this->factor(); } return; } // simple_expr() /* * Match a factor. */ private function factor() { switch ( $this->i_token ) { case self::KEYWORD : $this->s_tsearch .= $this->s_token_string; $this->a_keywords[] = $this->s_token_string; $this->match(self::KEYWORD); // Check for default-and between keywords or between a // keyword and the start of an expression. if ( $this->b_default_and == TRUE && ($this->i_token == self::KEYWORD || $this->i_token == self::DQUOTE || $this->i_token == self::LPAREN) ) { // Save the keyword and inject a default "AND" token. $this->a_lookahead[] = $this->i_token; $this->i_token = self::ANDOP; } break; case self::LPAREN : $this->s_tsearch .= '('; $this->s_ilike_paren .= '('; $this->match(self::LPAREN); $this->expr(); $this->s_tsearch .= ')'; // If there were no double-quoted values in the expression, prevent empty () // in the ILIKE string. if ( $this->s_ilike_paren == '(' ) $this->s_ilike_paren =''; else $this->s_ilike .= ')'; $this->match(self::RPAREN); // Check for default-and between the close of an expression and a keyword. if ( $this->b_default_and == TRUE && ($this->i_token == self::KEYWORD || $this->i_token == self::DQUOTE) ) { // Save the keyword and inject a default "AND" token. $this->a_lookahead[] = $this->i_token; $this->i_token = self::ANDOP; } break; case self::DQUOTE : $this->quote_keywords(); break; case self::NOTOP : break; default : $this->syntax_error(); $this->i_token = $this->get_token(); break; } return; } // factor() /* * * SCANNER * */ /* * Gets the next token from the input data. */ private function get_token() { $this->s_token_string = ''; $i_state = self::START; while ( $i_state != self::DONE ) { $i_char = $this->get_next_char(); $b_save = TRUE; switch ( $i_state ) { case self::START : if ( $this->is_keyword_char($i_char) ) $i_state = self::INWORD; else if ( $i_char == 32 ) $b_save = FALSE; else { $i_state = self::DONE; switch ( $i_char ) { case self::EOF : $b_save = FALSE; $i_current_token = self::ENDFILE; break; case 38 : // & $i_current_token = self::ANDOP; break; case 124 : // | $i_current_token = self::OROP; break; case 33 : // ! $i_current_token = self::NOTOP; break; case 34 : // " $i_current_token = self::DQUOTE; break; case 40 : // ( $i_current_token = self::LPAREN; break; case 41 : // ) $i_current_token = self::RPAREN; break; default : $i_current_token = self::ERROR; break; } } break; case self::INWORD : if ( !$this->is_keyword_char($i_char) ) { // Backup in the input. $this->unget_next_char(); $b_save = FALSE; $i_state = self::DONE; $i_current_token = self::KEYWORD; } break; case self::DONE : default : // Should never happen if ( $this->b_debug ) print "Scanner Bug: state=[$i_state]\n"; $i_state = self::DONE; $i_current_token = self::ERROR; break; } if ( $b_save == TRUE ) $this->s_token_string .= chr($i_char); } return $i_current_token; } // get_token() /* * Get the next character of the input data. */ private function get_next_char() { if ( $this->i_index < strlen($this->s_keyword) ) $i_char = ord($this->s_keyword[$this->i_index++]); else { // Indicate that the end of file marker was actaully read. $this->b_read_eof = TRUE; $i_char = self::EOF; } return $i_char; } // get_next_char() /* * Backtrack one character of the input data. */ private function unget_next_char() { // If we have not actually *read* the EOF, then backup. if ( $this->b_read_eof == FALSE ) $this->i_index--; return; } // unget_next_char() /* * Determines if a character is a valid keyword character. */ private function is_keyword_char($i_char) { if ( ($i_char >= 97 && $i_char <= 122) || // a - z $i_char == 43 || $i_char == 45 || // + and - ($i_char >= 48 && $i_char <= 57) // 0 - 9 ) return TRUE; return FALSE; } // is_keyword_char() /* * Build an array of stop words. * * Must be the same list as in /usr/local/pgsql/share/contrib/english.stop */ private function stop_words() { $this->a_stopwords = array( 'i' => 'i', 'me' => 'me', 'my' => 'my', 'myself' => 'myself', 'we' => 'we', 'our' => 'our', 'ours' => 'ours', 'ourselves' => 'ourselves', 'you' => 'you', 'your' => 'your', 'yours' => 'yours', 'yourself' => 'yourself', 'yourselves' => 'yourselves', 'he' => 'he', 'him' => 'him', 'his' => 'his', 'himself' => 'himself', 'she' => 'she', 'her' => 'her', 'hers' => 'hers', 'herself' => 'herself', 'it' => 'it', 'its' => 'its', 'itself' => 'itself', 'they' => 'they', 'them' => 'them', 'their' => 'their', 'theirs' => 'theirs', 'themselves' => 'themselves', 'what' => 'what', 'which' => 'which', 'who' => 'who', 'whom' => 'whom', 'this' => 'this', 'that' => 'that', 'these' => 'these', 'those' => 'those', 'am' => 'am', 'is' => 'is', 'are' => 'are', 'was' => 'was', 'were' => 'were', 'be' => 'be', 'been' => 'been', 'being' => 'being', 'have' => 'have', 'has' => 'has', 'had' => 'had', 'having' => 'having', 'do' => 'do', 'does' => 'does', 'did' => 'did', 'doing' => 'doing', 'a' => 'a', 'an' => 'an', 'the' => 'the', 'and' => 'and', 'but' => 'but', 'if' => 'if', 'or' => 'or', 'because' => 'because', 'as' => 'as', 'until' => 'until', 'while' => 'while', 'of' => 'of', 'at' => 'at', 'by' => 'by', 'for' => 'for', 'with' => 'with', 'about' => 'about', 'against' => 'against', 'between' => 'between', 'into' => 'into', 'through' => 'through', 'during' => 'during', 'before' => 'before', 'after' => 'after', 'above' => 'above', 'below' => 'below', 'to' => 'to', 'from' => 'from', 'up' => 'up', 'down' => 'down', 'in' => 'in', 'out' => 'out', 'on' => 'on', 'off' => 'off', 'over' => 'over', 'under' => 'under', 'again' => 'again', 'further' => 'further', 'then' => 'then', 'once' => 'once', 'here' => 'here', 'there' => 'there', 'when' => 'when', 'where' => 'where', 'why' => 'why', 'how' => 'how', 'all' => 'all', 'any' => 'any', 'both' => 'both', 'each' => 'each', 'few' => 'few', 'more' => 'more', 'most' => 'most', 'other' => 'other', 'some' => 'some', 'such' => 'such', 'no' => 'no', 'nor' => 'nor', 'not' => 'not', 'only' => 'only', 'own' => 'own', 'same' => 'same', 'so' => 'so', 'than' => 'than', 'too' => 'too', 'very' => 'very', 's' => 's', 't' => 't', 'can' => 'can', 'will' => 'will', 'just' => 'just', 'don' => 'don', 'should' => 'should', 'now' => 'now'); return; } // stop_words() } // parse_model ?>
Easy way to access database and store fetched results. Database connection and query operation implemented in a separate class to play with multiple query results simultaneously over the same connection. <?php /* ## Simple PostgreSQL Abstraction Layer v1.0 ## by Cem EV K <cemc@linux.org.tr> ## Sturday, January 06, 2001 ## ## Easy way to access database and store fetched results. ## Database connection and query operation implemented in a separate ## class to play with multiple query results simultaneously over ## the same connection. ## ## You can implement your own abstraction layer for other DBMSes that ## PHP supports. ## ## If you find a way/idea to run this software better, ## please send it to author. ## Tanks to my colleagues. ## ## Usage : ## $DB = new PGDB (); ## $sql = new QUERY ($DB); ## $sql->execute ("select name, surname, email from users where " . ## "username = '$username'"); ## if ($sql->getntuples () == 0) { ## echo "No records found !"; ## $DB->close (); ## exit; ## } ## $record = $sql->gofirst (); ## $name = $record['name']; ## echo "Realname of ($username) : " . $name; ## $DB->close (); */ $DBNAME = "database_name"; $DBUSER = "database_user"; $DBPASSWD = "database_user_password"; $DBHOST = "database_host"; $DBPORT = "database_port"; class Abstract_DB { ##PRIVATE## var $db_linkid = 0; ##PUBLIC## function open ($dbname, $user, $pass, $host, $port) { } function close () { } } class PGDB extends Abstract_DB { function PGDB() { global $DBNAME; global $DBUSER; global $DBPASSWD; global $DBHOST; global $DBPORT; $this->open ($DBNAME, $DBUSER, $DBPASSWD, $DBHOST, $DBPORT); } function _PGDB() { $this->close(); } function open ($dbname, $user, $passwd, $host, $port) { $this->db_linkid = pg_connect("dbname=$dbname " . "user=$user password=$passwd port=$port"); return $this->db_linkid; } function close () { return pg_close ($this->db_linkid); } } class QUERY { ##PRIVATE## var $dbobject; var $ntuples; var $operationresult; var $resultset; var $currentrow = 0; ##PUBLIC## function QUERY ($pdbobject) { if ($pdbobject) $this->associate ($pdbobject); } function associate ($pdbobject) { $this->dbobject = $pdbobject; } function execute ($querystring) { $this->operationresult = pg_exec ($this->dbobject->db_linkid, $querystring); return $this->operationresult; } function getntuples () { $this->ntuples = pg_numrows ($this->operationresult); return $this->ntuples; } function getfieldname ($fieldnumber) { return pg_fieldname ($this->operationresult, $fieldnumber); } function readrow () { $this->resultset = pg_fetch_array ($this->operationresult, $this->currentrow); return ($this->currentresultset = $this->resultset); } function gofirst () { $this->currentrow = 0; return $this->readrow (); } function golast () { $this->currentrow = ($this->getntuples ()) - 1; return $this->readrow (); } function gonext () { $this->currentrow++; if ($this->currentrow < $this->getntuples ()) { $this->resultset = $this->readrow (); return $this->resultset; } else return "LAST_RECORD_REACHED"; } function goprevious () { $this->currentrow--; if ($this->currentrow >= 0) { $this->resultset = $this->readrow (); return $this->resultset; } else return "FIRST_RECORD_REACHED"; } function beginTransaction () { if (!$this->execute ("BEGIN")) return false; return true; } function commitTransaction () { if (!$this->execute ("COMMIT")) return false; return true; } function rollbackTransaction () { if (!$this->execute ("ROLLBACK")) return false; return true; } } ?>
This is a very lightweight tool for PostgreSQL, if you used to using psql client than you will be at home with this. It connects to the database, (after you change the connection string), grabs the list of tables, displays their attributes, and lets you enter sql queries and view the output. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" "http://www.w3c.org/TR/REC-html40/loose.dtd"> <HTML> <HEAD><TITLE>Database Browser</TITLE></HEAD> <BODY BGCOLOR="WHITE"> <? // Please enter your connection string below $database=pg_connect("user=postgres dbname=glimpse"); // This is a simple fragment of code which will run just fine as long as // you change the connection string. Copyright 2000, Derek Young, GS Data Design if($action=="usersql") { $usersql=stripslashes($usersql); echo "QUERY: ".htmlspecialchars($usersql)."<BR>\n"; echo "<A HREF=\"$PHP_SELF\">Return to tables</A><BR><BR>\n"; $result=pg_exec($database,$usersql); if($numofrows=pg_numrows($result)) { echo "<TABLE BORDER=\"1\">\n<TR>"; $fields=pg_numfields($result); $fields2=$fields; while($fields2) { echo "<TD>".pg_fieldname($result,$fields-$fields2); echo "</TD>"; $fields2-- ; } echo "</TR>\n"; $click=0; while($numofrows > $click) { $row=pg_fetch_array($result,$click++); echo "<TR>"; $fields2=$fields; while($fields2) { echo "<TD>"; echo htmlspecialchars($row[$fields-$fields2--]); echo "</TD>"; } echo "</TR>\n"; } echo "</TABLE>"; } else $action=""; } if ($action=="") { $sql="SELECT usename, relname, relkind, relhasrules FROM pg_class, pg_user WHERE usesysid = relowner and ( relkind = 'r' OR relkind = 'i' OR relkind = 'S') and relname !~ '^pg_' and (relkind != 'i' OR relname !~ '^xinx') ORDER BY relname"; $result=pg_exec($database,$sql); if(!pg_NumRows($result)) echo "No Tables Found!"; else { echo "<TABLE BORDER=\"1\">\n"; echo "<TR><TD>Owner</TD><TD>Relation</TD><TD>Type</TD></TR>\n"; $click=0; $numofrows=pg_numrows($result); while($click < $numofrows) { $row=pg_fetch_array($result,$click++,1); echo "<TR><TD>".$row[usename]."</TD><TD>"; echo "<A HREF=\"$PHP_SELF?action=viewt&table="; echo rawurlencode($row[relname])."\">".$row[relname]."</TD>"; if($row[relkind]=="S") echo "<TD>sequence</TD></TR>\n"; if($row[relkind]=="r") echo "<TD>table</TD></TR>\n"; if($row[relkind]=="i") echo "<TD>index</TD></TR>\n"; } echo "</TABLE>\n"; } } if($action=="viewt") { $sql="SELECT a.attnum as attnum, a.attname as attname, t.typname as typname, a.attlen as attlen, a.atttypmod as atttypmod, a.attnotnull as attnotnull, a.atthasdef as atthasdef FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = '$table' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY attnum"; $result=pg_exec($database,$sql); $sql2="SELECT viewname, definition FROM pg_views WHERE viewname like '$table'"; $result2=pg_exec($database,$sql2); if (pg_numrows($result2)) { $row=pg_fetch_array($result2,0,1); echo "View = " . $row[viewname] . "<BR>\n"; echo "Query = " . $row[definition] . "<BR>\n"; } else echo "Table = " . $table . "<BR>\n"; echo "<A HREF=\"$PHP_SELF\">Return to Tables</A><BR><BR>\n"; $click=0; $numofrows=pg_numrows($result); echo "<TABLE BORDER=\"1\">\n"; echo "<TR><TD>Name</TD><TD>Length</TD><TD>Type</TD></TR>\n"; while($click < $numofrows) { $row = pg_fetch_array($result, $click++, 1); echo "<TR><TD>".$row[attname]."</TD>"; if ($row[attlen]==-1) $row[attlen]="var"; echo "<TD>".$row[attlen]."</TD><TD>".$row[typname]." "; if($row[attnotnull]=="t") echo "not null "; if($row[atthasdef]=="t") { echo "default = "; $sql2="SELECT d.adsrc as adsrc FROM pg_attrdef d, pg_class c WHERE c.relname = '$table' and c.oid = d.adrelid and d.adnum = ".$row[attnum]; $result2=pg_exec($database,$sql2); $row2=pg_fetch_array($result2,0,1); echo $row2[adsrc]; } echo "</TD></TR>\n"; } echo "</TABLE>\n"; $sql2="SELECT c2.relname as relname FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = '$table' and c.oid = i.indrelid and i.indexrelid = c2.oid ORDER BY c2.relname"; $result2=pg_exec($database,$sql2); if($numofrows=pg_numrows($result2)) { $click=0; while($numofrows > $click) { $row2=pg_fetch_array($result2,$click++,1); echo "<BR>Index: " . $row2[relname] . "<BR>\n"; } } } echo "<BR>\n"; echo "<FORM METHOD=\"POST\" ACTION=\"$PHP_SELF\">\n"; echo "Type SQL Query Here<BR>\n"; echo "<TEXTAREA NAME=\"usersql\" ROWS=\"1\" COLS=\"60\"></TEXTAREA><BR>\n"; echo "<INPUT TYPE=\"SUBMIT\" VALUE=\"Query\" NAME=\"query\"><BR>\n"; echo "<INPUT TYPE=\"HIDDEN\" VALUE=\"usersql\" NAME=\"action\">\n"; echo "</FORM>"; pg_close($database); ?> </BODY> </HTML>