PHP pg_prepare函数代码示例

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 populate_prepare_queries($dbconn)
{
    // INSERT IN COMMANDES
    pg_prepare($dbconn, "insert_commandes", 'INSERT INTO commandes (per_id,com_date,com_date_expedition,com_date_facturation,com_statut,com_statut_facturation)' . ' VALUES ($1,$2,$3,$4,$5,$6) RETURNING com_id');
    // INSERT IN LIGNES_COMMANDE
    pg_prepare($dbconn, "insert_lignes_commande", 'INSERT INTO lignes_commande (lic_quantite,pro_id,com_id,lic_est_reduction,lic_prix_unitaire)' . ' VALUES ($1,$2,$3,$4,$5) RETURNING lic_id');
}

/** Luo HTML sivustolaajuinen tagilistam\"{a}\"{a}r\"{a}t kysymykselle
 * @param $question_id integer
 */
function create_global_tag_count_box_for_a_question($question_id)
{
    /* $result resource
     * $tags_array_summary array
     * $figure array
     */
    $tags_array_summary = get_tags_for_a_question($question_id);
    $dbconn = pg_connect("host=localhost port=5432 dbname=noaa user=noaa password=123");
    // to get the amout of tags Globally
    $result = pg_prepare($dbconn, "query_tag_amount", 'SELECT count(tag)
        FROM tags 
        WHERE tag = $1');
    echo "<div class='tags_summary'>";
    echo "<p>tagged</p>";
    for ($i = 0; $i < count($tags_array_summary); $i++) {
        echo "<div id='one_tag_line'>";
        $result = pg_execute($dbconn, "query_tag_amount", array($tags_array_summary[$i]['tag']));
        $figure = pg_fetch_all($result);
        for ($j = 0; $j < count($figure); $j++) {
            create_tags($tags_array_summary[$i]);
            echo "<span id='multiplier'> × " . $figure[$j]['count'] . "</span>";
        }
        echo "</div>";
    }
    echo "</div>";
}

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

 /**
  * Prepares a query.
  *
  * @param $name The name of the statement.
  * @param $sql The SQL in the statement.
  *
  * @return Whether the statement preparation was successful.
  */
 public function prepare($name, $sql)
 {
     $result = pg_prepare($this->connection, $name, $sql);
     $this->setError($result);
     $this->checkDie($result);
     return $result ? TRUE : FALSE;
 }

 /**
  * Execute a query and return the result
  *
  * Executes a query with optional parameters (which are automatically
  * escaped). If a name is provided, then the query will be prepared
  * and saved if necessary (or simply executed if it was already
  * prepared). Unnamed queries are prepared and executed each time they
  * are called. Note that the second and later times a named statement
  * is called, the query string is disregarded (as the statement is
  * already prepared). There is no way to reallocate a name for a
  * query.
  *
  * @param string $str The query string to execute
  * @param array $params The query parameters
  * @param string $name The name of the query
  * @throws DatabaseException
  * @return resource
  */
 public static function query($str, $params = array(), $name = null)
 {
     $p =& static::$prepared;
     if ($name && !array_key_exists($name, $p) || !$name) {
         if ($name) {
             $p[$name] = true;
         } else {
             $name = '';
         }
         error_log("Preparing query ({$name}): {$str}");
         $prepped = pg_prepare(Database::connect(), $name, $str);
         if ($prepped === false) {
             $pgerr = pg_last_error();
             if ($name) {
                 $msg = "Could not prepare query {$name}: {$pgerr}";
             } else {
                 $msg = "Could not prepare query: {$pgerr}";
             }
             throw new DatabaseException($msg);
         }
     }
     error_log("Executing query {$name}");
     $resource = pg_execute(Database::connect(), $name, $params);
     if ($resource === false) {
         $pgerr = pg_last_error();
         if ($name) {
             $msg = "Could not execute query {$name}: {$pgerr}";
         } else {
             $msg = "Could not execute query: {$pgerr}";
         }
         throw new DatabaseException($msg);
     }
     return $resource;
 }

 /**
  * 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;
 }

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

 /**
  * @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 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;
 }

 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 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"];
}

 public function prepare($query)
 {
     $id = (string) mktime();
     $res = pg_prepare($this->_connection, $id, $query);
     if ($res) {
         $rs = new pgsqlDbResultSet($res, $id, $this->_connection);
     } else {
         throw new jException('jelix~db.error.query.bad', pg_last_error($this->_connection) . '(' . $query . ')');
     }
     return $rs;
 }