PHP PostgreSQL Query Issue -
i have been trying convert pg_connect query pdo pg_connect give me errors. below query have throwing error.
<?php if (!empty($_get)) { $searchfilters = array( "name" => "data.name || ' ' || data.lastname ilike " . $dbh->quote("%".getvar("name")."%"), "room" => "data.room = " . $dbh->quote(getvar("room")), "activity" => "data.activity = " . $dbh->quote(getvar("activity")), "date1" => "data.dob > " . $dbh->quote(getvar("date1")), "date2" => "data.dob < " . $dbh->quote(getvar("date2")), "date3" => "data.joindate > " . $dbh->quote(getvar("date3")), "date4" => "data.joindate < " . $dbh->quote(getvar("date4")), "date5" => "data.lastseen > " . $dbh->quote(getvar("date5")), "date6" => "data.lastseen < " . $dbh->quote(getvar("date6")), ); $wherequery = array(); foreach ($searchfilters $n => $q) { $v = getvar($n); if (!empty($v)) { $wherequery[] = $q; } } $query = "select distinct data.id, data.name, lastname, paging, room, activity data"; if (count($wherequery) > 0) { $query .= " " . (getvar("invert") == "on" ? " not " : "") . "(" . implode(" , ", $wherequery) . ")"; } $query .= " order lastname;"; $result = pg_query($connection, $query) or die(_("error in query") . ": $query." . pg_last_error($connection)); if (pg_num_rows($result) == 0) { echo '<div class="alert alert-error"> <a class="close" data-dismiss="alert" href="#">×</a> <h4 class=\"alert-heading\">' . _('no results') . '</h4> </div>'; } else { echo '<table class="table"> <thead> <tr> <script language="javascript"> function toggle(source) { checkboxes = document.getelementsbyname(\'foo\'); for(var in checkboxes) checkboxes[i].checked = source.checked; } </script> <th><input type="checkbox" onclick="toggle(this)"></th> <th>' . _("name") . '</th> <th>' . _("activity") . '</th> <th>' . _("room") . '</th> <th>' . _("paging") . '</th> </tr> </thead> <tbody>'; while ($row = pg_fetch_assoc($result)) { echo '<tr><td style="width:30px"><input type="checkbox" name="foo"></td>'; echo "<td><a href=\"details.php?id={$row["id"]}\">{$row["name"]} {$row["lastname"]}</a></td>"; echo '<td>' . $activities[$row["activity"]] . '</td>'; echo '<td>' . $rooms[$row["room"]] . '</td>'; echo '<td>' . $row["paging"] . '</td>'; echo '</tr>'; } echo '</tbody></table>'; } } ?> the line $result = pg_query($connection, $query) or die(_("error in query") . ": $query." . pg_last_error($connection)); part giving me trouble think.
i futilely tried following not work of course.
<?php if (!empty($_get)) { $searchfilters = array( "name" => "data.name || ' ' || data.lastname ilike " . $dbh->quote("%".getvar("name")."%"), "room" => "data.room = " . $dbh->quote(getvar("room")), "activity" => "data.activity = " . $dbh->quote(getvar("activity")), "date1" => "data.dob > " . $dbh->quote(getvar("date1")), "date2" => "data.dob < " . $dbh->quote(getvar("date2")), "date3" => "data.joindate > " . $dbh->quote(getvar("date3")), "date4" => "data.joindate < " . $dbh->quote(getvar("date4")), "date5" => "data.lastseen > " . $dbh->quote(getvar("date5")), "date6" => "data.lastseen < " . $dbh->quote(getvar("date6")), ); $wherequery = array(); foreach ($searchfilters $n => $q) { $v = getvar($n); if (!empty($v)) { $wherequery[] = $q; } } $query = "select distinct data.id, data.name, lastname, paging, room, activity data"; if (count($wherequery) > 0) { $query .= " " . (getvar("invert") == "on" ? " not " : "") . "(" . implode(" , ", $wherequery) . ")"; } $query .= " order lastname;"; //$result = pg_query($connection, $query) or // die(_("error in query") . ": $query." . pg_last_error($connection)); $sth = $dbh->query('$query'); while ($result = $sth->fetch(pdo::fetch_assoc)) { if (pg_num_rows($result) == 0) { echo '<div class="alert alert-error"> <a class="close" data-dismiss="alert" href="#">×</a> <h4 class=\"alert-heading\">' . _('no results') . '</h4> </div>'; } else { echo '<table class="table"> <thead> <tr> <script language="javascript"> function toggle(source) { checkboxes = document.getelementsbyname(\'foo\'); for(var in checkboxes) checkboxes[i].checked = source.checked; } </script> <th><input type="checkbox" onclick="toggle(this)"></th> <th>' . _("name") . '</th> <th>' . _("activity") . '</th> <th>' . _("room") . '</th> <th>' . _("paging") . '</th> </tr> </thead> <tbody>'; while ($row = pg_fetch_assoc($result)) { echo '<tr><td style="width:30px"><input type="checkbox" name="foo"></td>'; echo "<td><a href=\"details.php?id={$row["id"]}\">{$row["name"]} {$row["lastname"]}</a></td>"; echo '<td>' . $activities[$row["activity"]] . '</td>'; echo '<td>' . $rooms[$row["room"]] . '</td>'; echo '<td>' . $row["paging"] . '</td>'; echo '</tr>'; } echo '</tbody></table>'; }} } ?> please let me know if need else. not fluent in php , trying open source app running , there features needed tlc work. appreciated.
edit: here entire page.
the original query was
$sth = $dbh->query('$query'); while ($data = $sth->fetch(pdo::fetch_assoc)) echo "<option value=\"{$data[id]}\"> and have tried changing to:
<!doctype html> <!-- vim: tabstop=2:softtabstop=2 --> <?php require_once "config.php"; require_once 'functions.php'; //internationalisation $domain = "search"; require_once 'locale.php'; function getvar($vname) { return array_key_exists($vname, $_post) ? $_post[$vname] : $_get[$vname]; } $dbh = db_connect(); $connection = pg_connect ("host=$dbhost dbname=$dbname user=$dbuser password=$dbpass"); $page_title = _("advanced search"); require_once "template/header.php"; ?> <!-- sidebar --> <div class="span3"> <div class="well sidebar-nav"> <ul class="nav nav-list"> <li class="nav-header"><?php echo _("search") ?></li> <li><a href="search.php"><i class="icon-search"></i><?php echo _("search") ?></a></li> <li class="active"><a href="#"><i class="icon-filter"></i><?php echo _("advanced") ?></a></li> <li><a href="#"><i class="icon-bookmark"></i><?php echo _("saved searches") ?></a></li> </ul> <ul class="nav nav-list"> <li class="nav-header"><?php echo _("actions") ?></li> <li><a href="register.php"><i class="icon-plus-sign"></i><?php echo _("register") ?></a></li> <li><a href="#"><i class="icon-user"></i><?php echo _("register visitor") ?></a></li> <li><a href="#"><i class="icon-print"></i><?php echo _("print results") ?></a></li> <li><a href="#"><i class="icon-download-alt"></i><?php echo _("download results") ?></a></li> </div> </div> <!-- /sidebar --> <div class="span9"> <!-- search form --> <form class="well form-horizontal" method="get"> <fieldset> <div class="control-group"> <label class="control-label" for="name"><?php echo _("name contains") ?></label> <div class="controls"> <input type="text" class="input" name="name" id="name" placeholder="<?php echo _("name") ?>" value="<?php echo getvar("name"); ?>"> </div> </div> <div class="control-group"> <label class="control-label" for="room"><?php echo _("and room is") ?></label> <div class="controls"> <select name="room" id="room"> <option value="" selected><?php echo _("any room") ?></option> <?php $sth = $dbh->query('select id, name rooms'); while ($data = $sth->fetch(pdo::fetch_assoc)) echo "<option value=\"{$data[id]}\">{$data[name]}</option>\n"; ?> </select> </div> </div> <div class="control-group"> <label class="control-label" for="room"><?php echo _("and class is") ?></label> <div class="controls"> <select name="activity" id="activity"> <option value="" selected><?php echo _("any class") ?></option> <?php echo (is_null($edata["activity"]) ? "<option disabled selected>" . _("class") . "</option>\n" : ""); $sth = $dbh->query('select id, name activities'); while ($data = $sth->fetch(pdo::fetch_assoc)) { echo "<option value=\"{$data["id"]}\"" . ($data["id"] == $edata["activity"] ? " selected" : "") . ">{$data["name"]}</option>\n"; } ?> </select> </div> </div> <div class="control-group"> <label class="control-label" for="medical"><?php echo _("and born") ?></label> <div class="controls"> <?php echo _("after") ?> <input type="text" class="input-small" name="date1" id="date1" value="<?php echo getvar("date1"); ?>"> <?php echo _("and before") ?> <input type="text" class="input-small" name="date2" id="date2" value="<?php echo getvar("date2"); ?>"> </div> </div> <div class="control-group"> <label class="control-label" for="medical"><?php echo _("and joined") ?></label> <div class="controls"> <?php echo _("after") ?> <input type="text" class="input-small" name="date3" id="date3" value="<?php echo getvar("date3"); ?>"> <?php echo _("and before") ?> <input type="text" class="input-small" name="date4" id="date4" value="<?php echo getvar("date4"); ?>"> </div> </div> <div class="control-group"> <label class="control-label" for="medical"><?php echo _("and last seen") ?></label> <div class="controls"> <?php echo _("after") ?> <input type="text" class="input-small" name="date5" id="date5" value="<?php echo getvar("date5"); ?>"> <?php echo _("and before") ?> <input type="text" class="input-small" name="date6" id="date6" value="<?php echo getvar("date6"); ?>"> </div> </div> <div class="control-group"> <label class="control-label" for="medical"><?php echo _("invert query") ?></label> <div class="controls"> <input type="checkbox" name="invert" id="invert" <?php echo (getvar("invert") == "on" ? "checked" : ""); ?>> </div> </div> <div class="form-actions"> <input type="submit" class="btn btn-primary" value="<?php echo _("search") ?>" /> <input type="reset" class="btn" value="<?php echo _("reset") ?>"> </div> </fieldset> </form> <?php if (!empty($_get)) { $searchfilters = array( "name" => "data.name || ' ' || data.lastname ilike " . $dbh->quote("%".getvar("name")."%"), "room" => "data.room = " . $dbh->quote(getvar("room")), "activity" => "data.activity = " . $dbh->quote(getvar("activity")), "date1" => "data.dob > " . $dbh->quote(getvar("date1")), "date2" => "data.dob < " . $dbh->quote(getvar("date2")), "date3" => "data.joindate > " . $dbh->quote(getvar("date3")), "date4" => "data.joindate < " . $dbh->quote(getvar("date4")), "date5" => "data.lastseen > " . $dbh->quote(getvar("date5")), "date6" => "data.lastseen < " . $dbh->quote(getvar("date6")), ); $wherequery = array(); foreach ($searchfilters $n => $q) { $v = getvar($n); if (!empty($v)) { $wherequery[] = $q; } } $query = "select distinct data.id, data.name, data.lastname, data.paging, data.room, data.activity data"; if (count($wherequery) > 0) { $query .= " " . (getvar("invert") == "on" ? " not " : "") . "(" . implode(" , ", $wherequery) . ")"; } $query .= " order data.lastname;"; // $sth = $dbh->query('$query'); // while ($data = $sth->fetch(pdo::fetch_assoc)) // echo "<option value=\"{$data[id]}\">{$data[name]}</option>\n"; $result = pg_query($connection, $query) or die(_("error in query") . ": $query." . pg_last_error($connection)); if (pg_num_rows($result) == 0) { echo '<div class="alert alert-error"> <a class="close" data-dismiss="alert" href="#">×</a> <h4 class=\"alert-heading\">' . _('no results') . '</h4> </div>'; } else { echo '<table class="table"> <thead> <tr> <script language="javascript"> function toggle(source) { checkboxes = document.getelementsbyname(\'foo\'); for(var in checkboxes) checkboxes[i].checked = source.checked; } </script> <th><input type="checkbox" onclick="toggle(this)"></th> <th>' . _("name") . '</th> <th>' . _("activity") . '</th> <th>' . _("room") . '</th> <th>' . _("paging") . '</th> </tr> </thead> <tbody>'; while ($row = pg_fetch_assoc($result)) { echo '<tr><td style="width:30px"><input type="checkbox" name="foo"></td>'; echo "<td><a href=\"details.php?id={$row["id"]}\">{$row["name"]} {$row["lastname"]}</a></td>"; echo '<td>' . $activities[$row["activity"]] . '</td>'; echo '<td>' . $rooms[$row["room"]] . '</td>'; echo '<td>' . $row["paging"] . '</td>'; echo '</tr>'; } echo '</tbody></table>'; } } ?> </div> </div> <script> window.onload = function(){ for(i = 1; <= 6; i++) { new jsdatepick({ usemode:2, target:("date" + i), dateformat:"%y-%m-%d", imgpath:"resources/img/datepicker" }); } }; </script> <?php require_once "template/footer.php" ; ?> log results:
< 2015-06-21 17:48:10.712 cdt >log: database system ready accept connections < 2015-06-21 17:48:10.712 cdt >log: autovacuum launcher started conf file:
#------------------------------------------------------------------------------ # error reporting , logging #------------------------------------------------------------------------------ # - log - log_destination = 'stderr' # valid values combinations of # stderr, csvlog, syslog, , eventlog, # depending on platform. csvlog # requires logging_collector on. # used when logging stderr: logging_collector = on # enable capturing of stderr , csvlog # log files. required on # csvlogs. # (change requires restart) # these used if logging_collector on: log_directory = 'pg_log' # directory log files written, # can absolute or relative pgdata log_filename = 'postgresql-%a.log' # log file name pattern, # can include strftime() escapes #log_file_mode = 0600 # creation mode log files, # begin 0 use octal notation log_truncate_on_rotation = on # if on, existing log file # same name new log file # truncated rather appended to. # such truncation occurs on # time-driven rotation, not on restarts # or size-driven rotation. default # off, meaning append existing files # in cases. log_rotation_age = 1d # automatic rotation of logfiles # happen after time. 0 disables. log_rotation_size = 0 # automatic rotation of logfiles # happen after log output. # 0 disables. # these relevant when logging syslog: #syslog_facility = 'local0' #syslog_ident = 'postgres' # relevant when logging eventlog (win32): #event_source = 'postgresql' # - when log - client_min_messages = notice # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # log # notice # warning # error log_min_messages = error # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic log_min_error_statement = error # values in order of decreasing detail: # debug5 # debug4 # debug3 # debug2 # debug1 # info # notice # warning # error # log # fatal # panic (effectively off) #log_min_duration_statement = -1 # -1 disabled, 0 logs statements # , durations, > 0 logs # statements running @ least number # of milliseconds # - log - #debug_print_parse = off #debug_print_rewritten = off #debug_print_plan = off #debug_pretty_print = on #log_checkpoints = off #log_connections = off #log_disconnections = off #log_duration = off log_error_verbosity = default # terse, default, or verbose messages #log_hostname = off log_line_prefix = '< %m >' # special values: # %a = application name # %u = user name # %d = database name # %r = remote host , port # %h = remote host # %p = process id # %t = timestamp without milliseconds # %m = timestamp milliseconds # %i = command tag # %e = sql state # %c = session id # %l = session line number # %s = session start timestamp # %v = virtual transaction id # %x = transaction id (0 if none) # %q = stop here in non-session # processes # %% = '%' # e.g. '<%u%%%d> ' #log_lock_waits = off # log lock waits >= deadlock_timeout #log_statement = 'none' # none, ddl, mod, #log_temp_files = -1 # log temporary files equal or larger # specified size in kilobytes; # -1 disables, 0 logs temp files log_timezone = 'america/indiana/tell_city' #------------------------------------------------------------------------------ # runtime statistics #------------------------------------------------------------------------------ # - query/index statistics collector - #track_activities = on #track_counts = on #track_io_timing = off #track_functions = none # none, pl, #track_activity_query_size = 1024 # (change requires restart) #update_process_title = on #stats_temp_directory = 'pg_stat_tmp' # - statistics monitoring - #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off #------------------------------------------------------------------------------ # autovacuum parameters #------------------------------------------------------------------------------ #autovacuum = on # enable autovacuum subprocess? 'on' # requires track_counts on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs actions , # durations, > 0 logs # actions running @ least number # of milliseconds. #autovacuum_max_workers = 3 # max number of autovacuum subprocesses # (change requires restart) #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum #autovacuum_analyze_threshold = 50 # min number of row updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum xid age before forced vacuum # (change requires restart) #autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age # before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit # autovacuum, -1 means use # vacuum_cost_limit #------------------------------------------------------------------------------ # client connection defaults #------------------------------------------------------------------------------ # - statement behavior - #search_path = '"$user",public' # schema names #default_tablespace = '' # tablespace name, '' uses default #temp_tablespaces = '' # list of tablespace names, '' uses # default tablespace #check_function_bodies = on #default_transaction_isolation = 'read committed' #default_transaction_read_only = off #default_transaction_deferrable = off #session_replication_role = 'origin' #statement_timeout = 0 # in milliseconds, 0 disabled #lock_timeout = 0 # in milliseconds, 0 disabled #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 #vacuum_multixact_freeze_min_age = 5000000 #vacuum_multixact_freeze_table_age = 150000000 #bytea_output = 'hex' # hex, escape #xmlbinary = 'base64' #xmloption = 'content' # - locale , formatting - datestyle = 'iso, mdy' #intervalstyle = 'postgres' timezone = 'america/indiana/tell_city' #timezone_abbreviations = 'default' # select set of available time zone # abbreviations. currently, there # default # australia (historical usage) # india # can create own file in # share/timezonesets/. #extra_float_digits = 0 # min -15, max 3 #client_encoding = sql_ascii # actually, defaults database # encoding # these settings initialized initdb, can changed. lc_messages = 'en_us.utf-8' # locale system error message # strings lc_monetary = 'en_us.utf-8' # locale monetary formatting lc_numeric = 'en_us.utf-8' # locale number formatting lc_time = 'en_us.utf-8' # locale time formatting # default configuration text search default_text_search_config = 'pg_catalog.english' # - other defaults - #dynamic_library_path = '$libdir' #local_preload_libraries = '' #------------------------------------------------------------------------------ # lock management #------------------------------------------------------------------------------ #deadlock_timeout = 1s #max_locks_per_transaction = 64 # min 10 # (change requires restart) # note: each lock table slot uses ~270 bytes of shared memory, , there # max_locks_per_transaction * (max_connections + max_prepared_transactions) # lock table slots. #max_pred_locks_per_transaction = 64 # min 10 # (change requires restart) #------------------------------------------------------------------------------ # version/platform compatibility #------------------------------------------------------------------------------ # - previous postgresql versions - #array_nulls = on #backslash_quote = safe_encoding # on, off, or safe_encoding #default_with_oids = off #escape_string_warning = on #lo_compat_privileges = off #quote_all_identifiers = off #sql_inheritance = on #standard_conforming_strings = on #synchronize_seqscans = on # - other platforms , clients - #transform_null_equals = off #------------------------------------------------------------------------------ # error handling #------------------------------------------------------------------------------ #exit_on_error = off # terminate session on error? #restart_after_crash = on # reinitialize after backend crash? #------------------------------------------------------------------------------ # config file includes #------------------------------------------------------------------------------ # these options allow settings loaded files other # default postgresql.conf. #include_dir = 'conf.d' # include files ending in '.conf' # directory 'conf.d' #include_if_exists = 'exists.conf' # include file if exists #include = 'special.conf' # include file #------------------------------------------------------------------------------ # customized options #------------------------------------------------------------------------------ # add settings extensions here
Comments
Post a Comment