PHP pg_execute函数代码示例

function removeLikeToTable($dbconn, $userId, $msgId)
{
    pg_prepare($dbconn, "deleteLikes", "DELETE FROM yakLikes WHERE userID = \$1 AND msgID = \$2");
    $sql = pg_execute($dbconn, "deleteLikes", array($userId, $msgId));
    pg_prepare($dbconn, "updateLikes", "UPDATE yak SET likes=(likes-1) WHERE id = \$1");
    $sql = pg_execute($dbconn, "updateLikes", array($msgId));
}

 public function execQry()
 {
     $recordString = false;
     switch ($this->connectorStructure['dbType']) {
         case "ORA":
             $connectorString = '(DESCRIPTION = (CONNECT_TIMEOUT=5) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =' . $this->connectorStructure['dbIP'] . ')(PORT = ' . $this->connectorStructure['dbPort'] . ')))(CONNECT_DATA=(SID= ' . $this->connectorStructure['dbName'] . ')))';
             $dbConnector = oci_connect($this->connectorStructure['dbUser'], $this->connectorStructure['dbPassword'], $connectorString);
             if (!$dbConnector) {
                 echo 'Failed connection.......';
             } else {
                 $oraQuery = oci_parse($dbConnector, $this->queryStructure['dbQuery']);
             }
             oci_execute($oraQuery);
             $rowsnum = oci_fetch_all($oraQuery, $recordString);
             if ($rowsnum == 0) {
                 $recordString = array();
             } else {
                 if (!(substr($this->queryStructure['dbQuery'], 1, 6) == 'select')) {
                     $oraQuery = oci_parse($dbConnector, 'commit');
                     oci_execute($oraQuery);
                     $rowsnum = oci_fetch_all($oraQuery, $recordString);
                 }
             }
             oci_close($dbConnector);
             break;
         case "PGS":
             $connectorString = "host=" . $this->connectorStructure['dbIP'] . " port=" . $this->connectorStructure['dbPort'] . " dbname=" . $this->connectorStructure['dbName'] . " user=" . $this->connectorStructure['dbUser'] . " password=" . $this->connectorStructure['dbPassword'];
             $dbConnector = pg_connect($connectorString);
             if (!$dbConnector) {
                 echo 'Failed connection.......';
             } else {
                 pg_prepare($dbConnector, $this->queryStructure['qryName'], $this->queryStructure['dbQuery']);
             }
             $queryResult = pg_execute($dbConnector, $this->queryStructure['qryName'], $this->queryStructure['qryParameters']);
             if (!$queryResult) {
                 $recordString = array();
             } else {
                 $recordString = pg_fetch_all($queryResult);
                 if (!(substr($this->queryStructure['dbQuery'], 1, 6) == 'select')) {
                     pg_prepare($dbConnector, 'commit', 'commit');
                     pg_exec($dbConnector, 'commit');
                 }
             }
             pg_close($dbConnector);
             break;
         default:
             $recordString = false;
             break;
     }
     return $recordString;
 }

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

function 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);
}

 /**
  * Executes a prepared statement.
  *
  * @param $statement The name of the statement to execute.
  * @param $params The parameters to the statement.
  *
  * @return Whether the query was successful.
  */
 public function execute($statement, $params)
 {
     $result = pg_execute($this->connection, $statement, $params);
     $this->result = $result;
     $this->setError($result);
     $this->checkDie($result);
     return pg_fetch_array($result);
 }

 /**
  * prepare query
  * */
 public function execStatement($sql, $params)
 {
     $this->connect();
     $result = pg_prepare($this->connection, 'myStmt', $sql);
     pg_execute($this->connection, 'myStmt', $params);
     $this->disconnect();
     return $result;
 }

 /**
  * @param $sql
  */
 protected function executeQuery($sql)
 {
     $id = 9;
     $name = "BMW";
     $price = 36000;
     $sql = "INSERT INTO cars VALUES(\$1, \$2, \$3)";
     pg_prepare($this->connection, "prepare1", $sql) or die("Cannot prepare statement\n");
     pg_execute($this->connection, "prepare1", array($id, $name, $price)) or die("Cannot execute statement\n");
 }

function inside_allowed_area_check($wkt)
{
    pg_prepare("", "SELECT ST_Within(ST_GeometryFromText(\$1, 25833), klarschiff.klarschiff_stadtgrenze_hro.the_geom) FROM klarschiff.klarschiff_stadtgrenze_hro");
    $result = pg_execute("", array($wkt));
    if ($row = pg_fetch_assoc($result)) {
        return $row['st_within'] === 't';
    }
    return false;
}

 private function executeUpdate($query, $params)
 {
     $result = pg_prepare($this->connection, "query", $query);
     if ($params) {
         $resultSet = pg_execute($this->connection, "query", $params);
     } else {
         $resultSet = pg_execute($this->connection, "query");
     }
     return pg_affected_rows($resultSet);
 }

 function getState($state)
 {
     $ret = array();
     $rs = pg_prepare($this->dbconn, "STATESELECT", sprintf("select *,\n    ST_x(s.geom) as x, ST_y(s.geom) as y, valid at time zone '%s' as lvalid,\n    max_gust_ts at time zone '%s' as lmax_gust_ts,\n    max_sknt_ts at time zone '%s' as lmax_sknt_ts,\n  \t\t\tvalid at time zone 'UTC' as utc_valid,\n    s.name as sname from\n    current c2, summary_%s c, stations s WHERE\n    s.state = \$1 and (s.network ~* 'RWIS' or s.network ~* 'ASOS' or\n  \t\t\ts.network in ('KCCI','KELO','KIMT') or network = 'AWOS') \n    and c.day = 'TODAY'\n    and c2.valid > 'TODAY' and c2.iemid = c.iemid and c.iemid = s.iemid", $this->tzname, $this->tzname, $this->tzname, date("Y")));
     $rs = pg_execute($this->dbconn, "STATESELECT", array($state));
     for ($i = 0; $row = @pg_fetch_array($rs, $i); $i++) {
         $ret[$row["id"]] = new IEMAccessOb($row);
     }
     return $ret;
 }

function place_by_name($name)
{
    global $conn;
    $result = pg_execute($conn, "place_by_name", array($name));
    $place = array();
    while ($row = pg_fetch_assoc($result)) {
        $place = $row;
    }
    pg_free_result($result);
    return $place;
}

 function load_station($id)
 {
     $rs = pg_execute($this->dbconn, "SELECTST", array($id));
     for ($i = 0; $row = @pg_fetch_array($rs, $i); $i++) {
         $this->table[$row["id"]] = $row;
     }
     if (pg_num_rows($rs) < 1) {
         return false;
     }
     return true;
 }

function get_iemprop($propname)
{
    $dbconn = iemdb("mesosite");
    $rs = pg_prepare($dbconn, "SELECT321" . $propname, "SELECT * from properties where\n        propname = \$1");
    $rs = pg_execute($dbconn, "SELECT321" . $propname, array($propname));
    if (pg_num_rows($rs) < 1) {
        return null;
    }
    $row = pg_fetch_array($rs, 0);
    return $row["propvalue"];
}

function getGlobalDefaults($dbconn)
{
    $defaults = array("ms_tol" => 6, "ms2_tol" => 20, "ms_tol_unit" => "ppm", "ms2_tol_unit" => "ppm", "missed_cleavages" => 4, "notes" => "", "customsettings" => "", "acquisitions" => array(), "sequences" => array());
    $getMultiOptions = array("enzyme" => "SELECT id FROM enzyme WHERE is_default = TRUE", "ions" => "SELECT id FROM ion WHERE is_default = TRUE", "crosslinkers" => "SELECT id FROM crosslinker WHERE is_default = TRUE", "losses" => "SELECT id FROM loss WHERE is_default = TRUE", "fixedMods" => "SELECT id FROM modification WHERE is_default_fixed = TRUE", "varMods" => "SELECT id FROM modification WHERE is_default_var = TRUE");
    foreach ($getMultiOptions as $key => $value) {
        pg_prepare($dbconn, $key, $value);
        $result = pg_execute($dbconn, $key, array());
        $defaults[$key] = resultsAsArray($result);
    }
    //error_log (print_r($defaults, TRUE));
    return $defaults;
}

function neighbors($station, $lat, $lon)
{
    $con = iemdb("mesosite");
    $rs = pg_prepare($con, "_SELECT", "SELECT *,\n         ST_distance(ST_transform(geom,3857), \n                     ST_transform(ST_GeomFromEWKT('SRID=4326;POINT(" . $lon . " " . $lat . ")'), 3857)) /1000.0 as dist from stations \n         WHERE ST_point_inside_circle(geom, " . $lon . ", " . $lat . ", 0.25) \n         and id != \$1 ORDER by dist ASC");
    $result = pg_execute($con, "_SELECT", array($station));
    $s = "<table class=\"table table-striped\">\n   <thead><tr><th>Distance [km]</th><th>Network</th><th>Station Name</th></tr></thead>";
    for ($i = 0; $row = @pg_fetch_assoc($result, $i); $i++) {
        $s .= sprintf("<tr><td>%.3f</td><td><a href=\"locate.php?network=%s\">%s</a></td><td><a href=\"site.php?station=%s&network=%s\">%s</a></td></tr>", $row["dist"], $row["network"], $row["network"], $row["id"], $row["network"], $row["name"]);
    }
    $s .= "</table>";
    return $s;
}