PHP PDO::Query方法代码示例

 /**
  * The SQL Query funtion
  *
  * @version 1
  * @author Rick de Man <rick@rickdeman.nl>
  *        
  * @param string $Query
  *        	The Query to be executed
  * @param string $Comment
  *        	The Comment for the Query, for Debug use
  * @param bool $Fetchall
  *        	When a single result is found return a multi-dimension Array
  * @return integer|false|array
  */
 public function Query($Query, $Comment, $Fetchall = false)
 {
     // The SQL must be free to continue
     if ($this->SQL_Free !== true) {
         $this->Query_Next();
     }
     // SQL is occupied
     $this->SQL_Free = false;
     // process table name, Place prefix
     $Query = $this->Query_Table_Preg($Query);
     try {
         // Create The Query and Execute
         $this->STH = $this->DBH->Query($Query);
         // Load the Backtrace
         $Trace = debug_backtrace();
         // Create a SQL Log Event
         $this->Log = array('F' => $Trace[0]['file'] . ':' . $Trace[0]['line'] . '(' . $Trace[1]['function'] . ')', 'Q' => $Query, 'C' => $Comment);
     } catch (PDOException $e) {
         // Duplicate Error
         if (preg_match('/Duplicate entry/i', $e->getMessage()) === 1) {
             return -1;
         }
         // Oops, Something went wrong...
         PDO_Error($e->getMessage(), $Query, $_SESSION['WMS-Debug']);
     }
     // Finish the SQL Query
     $this->Query_Next();
     // Return the processed Data
     return $this->Result($Fetchall);
 }

 public function query($query, $model = 'StdClass')
 {
     /* @var $result \PDOStatement */
     // echo "*** Model in Query: " . $model . "\n";
     try {
         $exec_time_start = microtime(true);
         $result = parent::Query($query, \PDO::FETCH_CLASS, $model);
         #\Kint::dump($query, $model, $result, "There are {$result->rowCount()} elements.", parent::errorInfo());
         $exec_time_end = microtime(true);
         $exec_time = $exec_time_end - $exec_time_start;
         if (DatabaseLayer::getInstance()->getLogger()) {
             DatabaseLayer::getInstance()->getLogger()->addDebug("{$exec_time} sec: {$query}");
         }
         if (ActiveRecord::$showSql) {
             echo "{$exec_time} sec: {$query}\n";
         }
         $this->query_log[] = new Log($query, $exec_time);
         return $result;
     } catch (\PDOException $e) {
         #\Kint::dump($e->getMessage(), $query, $result);
         if (DatabaseLayer::getInstance()->getLogger()) {
             DatabaseLayer::getInstance()->getLogger()->addDebug("Query() Caught Exception: {$e->getMessage()}");
         }
         $this->handleError($model, $query, $e);
     }
 }

 public static function SetUpDatabase()
 {
     $db = new \PDO('sqlite::memory:');
     $db->SetAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
     $db->Query("\n            CREATE TABLE test_table\n            (\n                id integer PRIMARY KEY AUTOINCREMENT,\n                title varchar(100),\n                description text,\n                value text,\n                is_hidden boolean,\n                reference_id integer\n            );\n        ");
     return $db;
 }

function generate_reserve($bookbag_id, $course_code, $instructor)
{
    require '../settings.php';
    // Open sqlite db, then insert values
    $db_session = new PDO('sqlite:../' . $SETTINGS['dbfile']);
    $query = "SELECT count(*) from reserve where bookbag_id = " . $bookbag_id;
    $res = $db_session->Query($query);
    if ($res->fetchColumn() == 0) {
        $query = "INSERT into reserve (course_code, instructor, bookbag_id) values ('" . $course_code . "', '" . $instructor . "', " . $bookbag_id . ")";
        $results = $db_session->Query($query);
        if (!$results) {
            die('Query Failed... Please go back and retry');
        }
        return true;
    }
    return false;
}

 /**
  * Load data from the database by ID
  *
  * @version 1
  * @author Rick de Man <rick@rickdeman.nl>
  *
  * @param string $Table
  * @param string $ID
  * @return mixed
  */
 private function GetDefinitionsById($Table, $ID)
 {
     // Create Query
     $Query = "SELECT * FROM `{$Table}` WHERE id = {$ID}";
     try {
         // Execute the query
         $STH = $this->DBH->Query($Query);
     } catch (PDOException $e) {
         // Oops, Something went wrong...
         $this->_Result(array('message' => $e->getMessage(), 'line' => __LINE__), false);
     }
     // Debug Database Qeury
     $this->DebugLocalEndpoint("<font style='color:green'>{$Query} (" . $STH->RowCount() . ")</font>");
     // Get the results
     $STH = $STH->fetch();
     if ($STH !== false) {
         // Return parsed JSON string
         return json_decode($STH['json'], true);
     }
 }

<div class="breadcrumbs">
<a href="/">Home</a> &rsaquo; 
<a href="vieworderbook.php">Order Book</a> &rsaquo;
Order <?php echo $id; ?>
</div>

  <h2>#bitcoin-otc order id <?php echo $id; ?></h2>

<?php
     	try { $db = new PDO('sqlite:./otc/OTCOrderBook.db'); }
	catch (PDOException $e) { die($e->getMessage()); }
?>
  <table class="datadisplay">
<?php
  if (!$query = $db->Query('SELECT id, created_at, refreshed_at, buysell, nick, host, amount, thing, price, otherthing, notes FROM orders WHERE id=' . $id ))
    echo "   <tr><td>No matching orders found</td></tr>\n";
  else {
    if( $entry = $query->fetch(PDO::FETCH_BOTH)){
      $entry["indexedprice"] = index_prices($entry["price"]);
?>
   <tr>
    <th>id</th>
    <td><?php echo $entry["id"]; ?></td>
   </tr>
   <tr>
    <th>created at</th>
    <td><?php echo gmdate("Y-m-d H:i:s", $entry["created_at"]); ?></td>
   </tr>
   <tr>
    <th>refreshed at</th>

 // ------------------------------------------------
 //libxml_use_internal_errors(false);
 $doxyData = simplexml_load_file($xmlindex);
 // -------------
 // Connect to db
 // -------------
 $db = new PDO(MYSQL_DSN, MYSQL_USR, MYSQL_PWD, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_BOTH));
 $db->Exec("set character set utf8");
 $db->Exec("set names utf8 collate utf8_general_ci");
 // -------------------------------
 // Prefetch all documnetation data
 // -------------------------------
 $schema_doc = array();
 $table_doc = array();
 $column_doc = array();
 $result = $db->Query("SELECT `schema`, `brief`, `details` FROM `documentation`.`schema`")->fetchAll();
 foreach ($result as $row) {
     $schema_doc[$row["schema"]] = $row;
     $table_doc[$row["schema"]] = array();
     $column_doc[$row["schema"]] = array();
 }
 $result = $db->Query("SELECT `schema`, `table`, `brief`, `details` FROM `documentation`.`table`")->fetchAll();
 foreach ($result as $row) {
     $table_doc[$row["schema"]][$row["table"]] = $row;
     $column_doc[$row["schema"]][$row["table"]] = array();
 }
 $result = $db->Query("SELECT `schema`, `table`, `column`, `brief`, `details`, `reference` FROM `documentation`.`column`")->fetchAll();
 foreach ($result as $row) {
     $column_doc[$row["schema"]][$row["table"]][$row["column"]] = $row;
 }
 // -------------------------------

 /**
  * Setup db connection.
  * Based on defined options, this method connects to db defined in {@link $dsn}
  * and creates a {@link $table} table if {@link $createTable} is true.
  * @return boolean true if all ok.
  * @throws a PDOException if the attempt to connect to the requested database fails.
  */
 public function activateOptions()
 {
     try {
         if ($this->user === null) {
             $this->db = new PDO($this->dsn);
         } else {
             if ($this->password === null) {
                 $this->db = new PDO($this->dsn, $this->user);
             } else {
                 $this->db = new PDO($this->dsn, $this->user, $this->password);
             }
         }
         $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         // test if log table exists
         try {
             $result = $this->db->Query('SELECT * FROM ' . $this->table . ' WHERE 1 = 0');
             $result->closeCursor();
         } catch (PDOException $e) {
             // It could be something else but a "no such table" is the most likely
             $result = false;
         }
         // create table if necessary
         if ($result == false and $this->createTable) {
             // The syntax should at least be compatible with MySQL, PostgreSQL, SQLite and Oracle.
             $Query = "CREATE TABLE {$this->table} (" . "timestamp varchar(32)," . "logger varchar(64)," . "level varchar(32)," . "message varchar(9999)," . "thread varchar(32)," . "file varchar(255)," . "line varchar(6))";
             $result = $this->db->Query($Query);
         }
     } catch (PDOException $e) {
         $this->canAppend = false;
         throw new LoggerException($e);
     }
     $this->layout = new LoggerLayoutPattern();
     //
     // Keep compatibility to legacy option $sql which already included the format patterns!
     //
     if (empty($this->sql)) {
         // new style with prepared Statment and $InsertSql and $InsertPattern
         // Maybe the tablename has to be substituted.
         $this->InsertSql = preg_replace('/__TABLE__/', $this->table, $this->InsertSql);
         $this->preparedInsert = $this->db->prepare($this->InsertSql);
         $this->layout->setConversionPattern($this->InsertPattern);
     } else {
         // Old style with format strings in the $sql Query should be used.
         $this->layout->setConversionPattern($this->sql);
     }
     $this->canAppend = true;
     return true;
 }

	tr.even {
		background-color: #dbdfff;
	}
  --></style>
  <title>#bitcoin-otc order book</title>
 </head>
 <body>
  <h2>#bitcoin-otc order book</h2>
  <p>[<a href="/">home</a>]</p>
  <h3>Summary statistics on outstanding orders</h3>
  <ul>
<?php
	try { $db = new PDO('sqlite:./otc/OTCOrderBook.db'); }
	catch (PDOException $e) { die($e->getMessage()); }

	if (!$query = $db->Query('SELECT count(*) as ordercount, sum(btcamount) as ordersum FROM orders'))
		echo "   <li>No outstanding orders found</li>";
	else {
		$entry = $query->fetch(PDO::FETCH_BOTH);
		echo "   <li>" . number_format($entry['ordercount']) . " outstanding orders, for a total of " . number_format($entry['ordersum'], 1) . " BTC.</li>\n";
	}

	if (!$query = $db->Query("SELECT count(*) as ordercount, sum(btcamount) as ordersum FROM orders WHERE buysell='BUY'"))
		echo "   <li>No outstanding BUY orders found</li>\n";
	else {
		$entry = $query->fetch(PDO::FETCH_BOTH);
		echo "   <li>" . number_format($entry['ordercount']) . " outstanding BUY orders, for a total of " . number_format($entry['ordersum'], 1) . " BTC.</li>\n";
	}

	if (!$query = $db->Query("SELECT count(*) as ordercount, sum(btcamount) as ordersum FROM orders WHERE buysell='SELL'"))
		echo "   <li>No outstanding SELL orders found</li>\n";

<?php

set_time_limit(0);
$DBH = new PDO("mysql:host=127.0.0.1;dbname=c2go", 'c2go', 'zRyJJ5xzKN7sMb3b');
$DBH->Query('SET NAMES utf8');
$handle = fopen('cities5000.txt', "r");
$i = 0;
$STH = $DBH->prepare("INSERT INTO site_cities (geonameid,name,asciiname,alternatenames,latitude,longitude,feature_class,feature_code,country_code,cc2,admin1_code,admin2_code,admin3_code,admin4_code,population,elevation,dem,timezone,ru_name) \r\n    values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
while (!feof($handle)) {
    $buffer = fgets($handle, 1000000);
    $data = explode("\t", $buffer);
    $data[18] = '';
    if (preg_match_all('/^(.+,|)([-`\\s0-9а-я]+)(,.+|)$/msiu', $data[3], $matches)) {
        $data[18] = $matches[2][0];
    }
    $STH->execute($data);
    ++$i;
}
fclose($handle);

Last updated on: <?php echo $lastupdated; ?>. <br>
Current OTC time: <?php echo date("F d Y H:i:s T"); ?>.
</p>

<?php
try { $db = new PDO('sqlite:./OTCQuotes.db'); }
catch (PDOException $e) { die($e->getMessage()); }
?>
  <table class="datadisplay">
     <tr>
    <th>currency</th>
    <th>bid</th>
    <th>ask</th>
   </tr>
<?php
  if (!$query = $db->Query('SELECT currency, bid, ask FROM quotes ORDER BY currency ASC'))
    echo "   <tr><td>No data found</td></tr>\n";
  else {
   while ($entry = $query->fetch(PDO::FETCH_BOTH)) {
    if ($color++ % 2) $class="even"; else $class="odd";
?>
   <tr class="<?php echo $class; ?>"> 
    <td><a href = "http://bitcoin-otc.com/vieworderbook.php?sortby=buysell&eitherthing=<?php echo $entry['currency']; ?>"><?php echo $entry['currency']; ?></a></td>
    <td><?php echo $entry['bid']; ?></td>
    <td><?php echo $entry['ask']; ?></td>
   </tr>
<?
  }
}
?>

		border: 1px solid gray;
		padding: 10px;
	}
	tr.even {
		background-color: #dbdfff;
	}
  --></style>
 </head>
 <body>
  <h2>OTC web of trust</h2>
  <p>[<a href="/">home</a>]</p>
  <h3>Summary statistics on web of trust</h3>
  <ul><?php
	try { $db = new PDO('sqlite:./otc/RatingSystem.db'); }
	catch (PDOException $e) { die($e->getMessage()); }
	if (!$query = $db->Query('SELECT count(*) as usercount, sum(total_rating) as ratingsum FROM users'))
		echo "<li>No outstanding orders found</li>\n";
	else {
		$entry = $query->fetch(PDO::FETCH_BOTH);
		echo "<li>" . $entry['usercount'] . " users in database, with a total of " . $entry['ratingsum'] . " net rating points.</li>\n";
	}

	if (!$query = $db->Query("SELECT count(*) as ratingcount, sum(rating) as ratingsum FROM ratings WHERE rating > 0"))
		echo "<li>No positive ratings found</li>\n";
	else {
		$entry = $query->fetch(PDO::FETCH_BOTH);
		echo "<li>" . $entry['ratingcount'] . " positive ratings sent, for a total of " . $entry['ratingsum'] . " points.</li>\n";
	}

	if (!$query = $db->Query("SELECT count(*) as ratingcount, sum(rating) as ratingsum FROM ratings WHERE rating < 0"))
		echo "<li>No negative ratings found</li>\n";

 * 3 - discounts - пїЅпїЅпїЅпїЅпїЅ пїЅ пїЅпїЅпїЅпїЅпїЅпїЅ
 * 4 - gifts for him
 * 5 - gifts for her
 * 6 - gifts for family
 * 7 - gifts for children
 * 8 - wareinfo + ids
 */
list($dev_id, $dev_name) = split(",", isset($_GET['device']) ? $_GET['device'] : ',');
if ($appdb = new PDO('sqlite:/www/sites/newmvideo/data/iphone_app.sqlite')) {
    $appdb->Query("insert into app_stats values(datetime('now'), '{$_SERVER['REMOTE_ADDR']}', '{$dev_id}', '{$dev_name}')");
}
error_log("" . date("Y/m/d H:i:s") . "\t" . $_SERVER['REMOTE_ADDR'] . "\t" . $_GET['device'] . "\n", 3, '/tmp/iphone_data_device.log');
if (isset($_GET['token'])) {
    error_log("" . date("Y/m/d H:i:s") . "\t" . $_SERVER['REMOTE_ADDR'] . "\t" . $_GET['device'] . "\t" . $_GET['token'] . "\n", 3, '/tmp/token.log');
    if ($appdb = new PDO('sqlite:/www/sites/newmvideo/data/iphone_app.sqlite')) {
        $appdb->Query("insert into notification values(datetime('now'), '{$_SERVER['REMOTE_ADDR']}', '{$dev_id}', '{$dev_name}', '{$_GET[token]}')");
    }
    exit;
}
// пїЅпїЅпїЅпїЅпїЅпїЅпїЅ , пїЅпїЅпїЅ пїЅпїЅпїЅ xml
// пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅ пїЅпїЅпїЅпїЅпїЅ
$db = new DB_Mvideo();
// пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ - 1
$GlobalConfig["RegionID"] = 1;
if (isset($_GET['region'])) {
    $GlobalConfig["RegionID"] = (int) $_GET['region'];
}
// пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ - 1
$category = 1;
if (isset($_GET['category'])) {
    $category = (int) $_GET['category'];

	$validkeys = array('id', 'nick', 'created_at', 'keyid', 'total_rating', 'pos_rating_recv_count', 'neg_rating_recv_count', 'pos_rating_sent_count', 'neg_rating_sent_count');

	$sortorder = "DESC";
?>

<div class="breadcrumbs">
<a href="/">Home</a> &rsaquo;
<a href="trust.php">Web of Trust</a> &rsaquo;
Web of Trust Data
</div>

  <h3>Summary statistics on web of trust</h3>
  <ul><?php
	try { $db = new PDO('sqlite:./otc/RatingSystem.db'); }
	catch (PDOException $e) { die($e->getMessage()); }
	if (!$query = $db->Query('SELECT count(*) as usercount, sum(total_rating) as ratingsum FROM users'))
		echo "<li>No outstanding orders found</li>\n";
	else {
		$entry = $query->fetch(PDO::FETCH_BOTH);
		echo "<li>" . $entry['usercount'] . " users in database, with a total of " . $entry['ratingsum'] . " net rating points.</li>\n";
	}

	if (!$query = $db->Query("SELECT count(*) as ratingcount, sum(rating) as ratingsum FROM ratings WHERE rating > 0"))
		echo "<li>No positive ratings found</li>\n";
	else {
		$entry = $query->fetch(PDO::FETCH_BOTH);
		echo "<li>" . $entry['ratingcount'] . " positive ratings sent, for a total of " . $entry['ratingsum'] . " points.</li>\n";
	}

	if (!$query = $db->Query("SELECT count(*) as ratingcount, sum(rating) as ratingsum FROM ratings WHERE rating < 0"))
		echo "<li>No negative ratings found</li>\n";

<h2>OTC Order Book <sup>[<a href="orderbook.json">json</a>]</sup></h2>

<?php
if (sizeof($queryfilter) != 0) {
  echo '<div class="filter">Filtered results. <a href="vieworderbook.php">Clear filter</a></div>';
}
?>

<table class="datadisplay" style="width: 100%;">
<tr>
  <?php
    try { $db = new PDO('sqlite:./otc/OTCOrderBook.db'); }
    catch (PDOException $e) { die($e->getMessage()); }

    echo ' <td style="text-align: center;">Total orders<br>';
    if (!$query = $db->Query('SELECT count(*) as ordercount FROM orders'))
      echo "0";
    else {
      $entry = $query->fetch(PDO::FETCH_BOTH);
      echo number_format($entry['ordercount']);
    }
    echo "</td>\n";

    echo ' <td style="text-align: center;">Buy orders<br>';
    if (!$query = $db->Query("SELECT count(*) as ordercount FROM orders WHERE buysell='BUY'"))
      echo "0";
    else {
      $entry = $query->fetch(PDO::FETCH_BOTH);
      echo number_format($entry['ordercount']);
    }
    echo "</td>\n";