PHP PDO::query方法代码示例

function add_member($username, $password)
{
    require 'password.php';
    try {
        define('DB_HOST', getenv('OPENSHIFT_MYSQL_DB_HOST'));
        define('DB_PORT', getenv('OPENSHIFT_MYSQL_DB_PORT'));
        define('DB_USER', getenv('OPENSHIFT_MYSQL_DB_USERNAME'));
        define('DB_PASS', getenv('OPENSHIFT_MYSQL_DB_PASSWORD'));
        define('DB_NAME', getenv('OPENSHIFT_GEAR_NAME'));
        try {
            $dsn = 'mysql:dbname=members;host=' . DB_HOST . ';port=' . DB_PORT;
            $db = new PDO($dsn, DB_USER, DB_PASS);
        } catch (PDOException $ex) {
            echo 'Error!: ' . $ex->getMessage();
            die;
        }
        $password = password_hash($password, PASSWORD_BCRYPT);
        echo $password;
        $query = $db->query("USE members");
        $query = $db->query("INSERT INTO members (username, password) VALUES ('{$username}', '{$password}')");
        header('Location: main_login.php');
        return TRUE;
    } catch (Exception $ex) {
        return FALSE;
    }
}

 public function testEquipmentUpdate()
 {
     $this->delete($this->insert(array(Object::TIME_IN_SECONDS => 3600, Object::DISTANCE => 12, Object::SHOEID => 1)));
     $this->delete($this->insert(array(Object::TIME_IN_SECONDS => 3600, Object::DISTANCE => 12, Object::SHOEID => 2)));
     $this->assertEquals(array('km' => 10, 'time' => 3000), $this->PDO->query('SELECT `km`, `time` FROM `' . PREFIX . 'shoe` WHERE `id`=1 AND `accountid`=0')->fetch(PDO::FETCH_ASSOC));
     $this->assertEquals(array('km' => 0, 'time' => 0), $this->PDO->query('SELECT `km`, `time` FROM `' . PREFIX . 'shoe` WHERE `id`=2 AND `accountid`=0')->fetch(PDO::FETCH_ASSOC));
 }

function init_db($db_file_name)
{
    global $db;
    $need_init_db = !file_exists($db_file_name);
    $dsn = 'sqlite:' . $db_file_name;
    $db = new PDO($dsn);
    if (!$db) {
        die('failed to open sqlite.');
    }
    if ($need_init_db) {
        $sql = <<<SQL
create table pages (
  id integer primary key,
  title varchar(255) unique,
  body text,
  created_at timestamp default current_timestamp,
  updated_at timestamp default current_timestamp
)
SQL;
        $result = $db->query($sql);
        $sql = <<<SQL
insert into pages
    (id, title, body)
  values
    (1, 'index', '...')
SQL;
        $result = $db->query($sql);
    }
}

 /**
  * Setting default values on the PDO instance like errormode, encoding and
  * so on.
  */
 public function setDefaultAttributes()
 {
     $this->_pdo->setAttribute(\PDO::ATTR_CASE, \PDO::CASE_NATURAL);
     $this->_pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
     $this->_pdo->setAttribute(\PDO::ATTR_ORACLE_NULLS, \PDO::NULL_TO_STRING);
     $this->_pdo->query("SET NAMES utf8");
 }

function optimizeDB($text)
{
    global $pathToIndex;
    // SETTING BEGIN
    // Run the task every n days
    $scheduleDays = 10;
    // SETTING END
    $loggixDB = $pathToIndex . Loggix_Core::LOGGIX_SQLITE_3;
    $filename = $loggixDB . '.BAK';
    if (!file_exists($filename) || filemtime($filename) + 86400 * $scheduleDays <= time()) {
        if (copy($loggixDB, $filename)) {
            chmod($filename, 0666);
            $backupDB = 'sqlite:' . $filename;
            $bdb = new PDO($backupDB);
            // Garbage Collection (/admin/delete.php)
            $maxLifeTime = get_cfg_var("session.gc_maxlifetime");
            $expirationTime = time() - $maxLifeTime;
            $sql = 'DELETE FROM ' . SESSION_TABLE . ' ' . 'WHERE ' . "sess_date < '" . $expirationTime . "'";
            $bdb->query($sql);
            // Vacuum DB
            $bdb->query('VACUUM');
            if (rename($loggixDB, $loggixDB . '.OLD')) {
                copy($filename, $loggixDB);
                chmod($loggixDB, 0666);
            }
            if (file_exists($loggixDB)) {
                unlink($loggixDB . '.OLD');
            }
        }
    }
    return $text;
}

 /**
  * Вынимает данные пользователя из БД
  * @param mixed $sourceUserId ИД пользователя или его логин
  */
 public function pickUserData($sourceUserId)
 {
     // Подключаем базу - источник
     $this->_debugMessage("Connecting to source database ...");
     $dsn = "mysql:dbname={$this->_sourceDb['dbname']};";
     $dsn .= "host={$this->_sourceDb['host']}";
     $db = new PDO($dsn, $this->_sourceDb['user'], $this->_sourceDb['pass'], array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''));
     $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $result = $db->query("SELECT id FROM users\n            WHERE id = '{$sourceUserId}' OR user_login = '{$sourceUserId}' LIMIT 1");
     $sourceUserId = $result->fetchColumn();
     $tablesSelected = array();
     // Делаем выборку таблиц и их данных из массива
     $this->_debugMessage("Selecting tables ...");
     foreach ($this->_tablesTemplate as $table => $params) {
         if (!isset($params['userId']) && !isset($params['selectCase'])) {
             die('Can\'t select table without key or `where` statement!');
         }
         $sql = 'select * from `' . $table . '` where ';
         $sql .= isset($params['selectCase']) ? str_ireplace('{$userId}', $sourceUserId, $params['selectCase']) : " `{$params['userId']}`={$sourceUserId};";
         $result = $db->query($sql);
         $tablesSelected[$table] = $result->fetchAll(PDO::FETCH_ASSOC);
         $this->_debugMessage("`{$table}` selected;");
     }
     return $tablesSelected;
 }

    /**
     * @inheritdoc
     */
    public function initialize()
    {
        $createPhotoTable = <<<SQL
CREATE TABLE IF NOT EXISTS "photo" (
    "uuid" TEXT NOT NULL PRIMARY KEY,
    "resourceId" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "httpUrl" TEXT NOT NULL,
    "position" INT NOT NULL,
    "filePath" TEXT
)
SQL;
        $this->pdo->query($createPhotoTable);
        $createAlternativeTextTable = <<<SQL
CREATE TABLE IF NOT EXISTS "alternative_text" (
    "photo_uuid" TEXT NOT NULL,
    "alt" TEXT NOT NULL,
    "lang" TEXT NOT NULL,
    FOREIGN KEY(photo_uuid) REFERENCES photo(uuid)
)
SQL;
        $this->pdo->query($createAlternativeTextTable);
        $this->pdo->query("CREATE INDEX resource ON \"photo\" (resourceId);");
        $this->pdo->query("CREATE INDEX fk_alt_photo_uuid ON \"alternative_text\" (photo_uuid);");
    }

    static function getSQLiteDB()
    {
        if (file_exists(SABRE_TEMPDIR . '/testdb.sqlite')) {
            unlink(SABRE_TEMPDIR . '/testdb.sqlite');
        }
        $pdo = new PDO('sqlite:' . SABRE_TEMPDIR . '/testdb.sqlite');
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->query('
CREATE TABLE calendarobjects ( 
	id integer primary key asc, 
    calendardata text, 
    uri text, 
    calendarid integer, 
    lastmodified integer
);
');
        $pdo->query('
CREATE TABLE calendars (
    id integer primary key asc, 
    principaluri text, 
    displayname text, 
    uri text,
    ctag integer,
    description text,
	calendarorder integer,
    calendarcolor text,
    timezone text,
    components text
);');
        $pdo->query('INSERT INTO calendars (principaluri,displayname,uri,description,calendarorder,calendarcolor,components) 
            VALUES ("principals/user1","user1 calendar","UUID-123467","Calendar description", "1", "#FF0000","VEVENT,VTODO");');
        $stmt = $pdo->prepare('INSERT INTO calendarobjects (calendardata, uri, calendarid, lastmodified) VALUES (?, "UUID-2345", 1, DATETIME("NOW"))');
        $stmt->execute(array(self::getTestCalendarData()));
        return $pdo;
    }

function getItems4Client()
{
    $DB = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USERNAME, DB_PASSWORD);
    $table = DB_TABLE;
    //检查一下是否存在表
    $sql = "SHOW TABLES LIKE '%{$table}%';";
    $rc = $DB->query($sql)->rowCount();
    if (!$rc) {
        $sqlFile = file_get_contents('items.sql');
        $DB->exec($sqlFile);
    }
    $sql = "SELECT * FROM `{$table}` LIMIT 0,10";
    $query = $DB->query($sql);
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $rt = $query->fetchAll();
    if ($rt) {
        $items = array();
        foreach ($rt as $item) {
            $tmp = array();
            $tmp['openid'] = $item['tb_item_id'];
            $tmp['iid'] = $item['tb_iid'];
            $tmp['price'] = $item['price'] ? $item['price'] / 100 : $item['reserve_price'] / 100;
            $tmp['name'] = $item['name'];
            $tmp['pic'] = $item['pic'];
            $tmp['itemType'] = $item['is_mall'] + 1;
            // 客户端判断是 1:集市店,2:天猫店
            $items[] = $tmp;
        }
        Ajax::go($items);
    } else {
        Ajax::error('数据查询失败.');
    }
}

 public function testLongWork()
 {
     // work_test
     $pdo = new PDO_instance('sqlite::memory:', null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
     $pdo->query('CREATE TABLE IF NOT EXISTS `work_test` (
         `key` varchar(128) DEFAULT NULL,
         `value` varchar(256) DEFAULT NULL
     );');
     $pdo->query(" DELETE FROM `work_test`;");
     // clear table data
     for ($i = 1; $i <= 100; $i++) {
         $pdo->query("INSERT INTO `work_test` (`key`, `value`) VALUES ('key" . $i . "', '" . mt_rand(0, 50) . "');");
     }
     $builder = new Builder($pdo);
     $this->assertInstanceOf('\\YepSQL\\Builder', $builder);
     #
     $this->assertEquals(3230, count($builder->loadFromFile(__DIR__ . '/true_long.sql')));
     $count = $pdo->query('SELECT count(*) FROM `work_test` WHERE `value` > 32;')->fetchColumn();
     $re = $builder->workLongMore([':more' => 32])->fetchColumn();
     print_r($re);
     $this->assertEquals($count, $re);
     #
     $count = $pdo->query('SELECT count(*) FROM `work_test` WHERE `value` < 32;')->fetchColumn();
     $re = $builder->workLongLess([':less' => 32])->fetchColumn();
     print_r($re);
     $this->assertEquals($count, $re);
     #
 }

 public static function export(\PDO $database, $databaseName)
 {
     try {
         $stmt = $database->query('SHOW TABLES');
         $db = new Database($databaseName);
         foreach ($stmt as $k => $v) {
             $table = new Table();
             $table->setName($v[0]);
             $query = $database->query('SHOW COLUMNS FROM ' . $table->getName());
             $columns = $query->fetchAll(\PDO::FETCH_ASSOC);
             foreach ($columns as $c) {
                 $column = new Column();
                 $column->setName($c['Field']);
                 $column->setDefault($c['Default']);
                 $column->setExtra($c['Extra']);
                 $column->setKey($c['Key']);
                 $column->setNull($c['Null']);
                 $column->setType($c['Type']);
                 $table->addColumn($column);
             }
             $db->addTable($table);
         }
         $stmt->closeCursor();
     } catch (\PDOException $e) {
         echo "Error: " . $e->getMessage();
         exit;
     }
     return $db;
 }

 /**
  * Connects to the database without initializing contao
  *
  * @param OutputInterface $output = null
  *
  * @throws \Exception
  * @return \PDO
  */
 public function getConnection(OutputInterface $output = null)
 {
     if ($output == null) {
         $output = new NullOutput();
     }
     if ($this->_connection) {
         return $this->_connection;
     }
     $this->detectDbSettings($output);
     if (!extension_loaded('pdo_mysql')) {
         throw new \Exception('pdo_mysql extension is not installed');
     }
     if (strpos($this->dbSettings['host'], '/') !== false) {
         $this->dbSettings['unix_socket'] = $this->dbSettings['host'];
         unset($this->dbSettings['host']);
     } else {
         if (strpos($this->dbSettings['host'], ':') !== false) {
             list($this->dbSettings['host'], $this->dbSettings['port']) = explode(':', $this->dbSettings['host']);
         }
     }
     $this->_connection = new \PDO($this->dsn(), $this->dbSettings['username'], $this->dbSettings['password']);
     /** @link http://bugs.mysql.com/bug.php?id=18551 */
     $this->_connection->query("SET SQL_MODE=''");
     try {
         $this->_connection->query('USE `' . $this->dbSettings['dbname'] . '`');
     } catch (\PDOException $e) {
     }
     $this->_connection->query("SET NAMES utf8");
     $this->_connection->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);
     $this->_connection->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
     return $this->_connection;
 }

 /**
  * Execute une requête SQL (mysql).
  *
  * @todo implémenter le template.
  * @todo eviter de dupliquer les objets PDO, mais maintenir le renvoi d'une collection ouverte sur la BD
  * @param string $sql
  * @param string $template
  * @throws PDOException
  * @throws Afup_Directory_Exception
  * @return integer|PDOStatement insert or select
  */
 private function executeSql($sql, $template = null, $values = null)
 {
     if ($values) {
         $stmt = $this->pdo->prepare($sql);
         if ($stmt) {
             if (isset($values[0]) && is_array($values[0])) {
                 foreach ($values as $value) {
                     $stmt->execute($value);
                 }
             } else {
                 $stmt->execute($values);
             }
         }
     } else {
         $stmt = $this->pdo->query($sql, PDO::FETCH_ASSOC);
     }
     $queryType = strtolower(substr($sql, 0, 6));
     switch ($queryType) {
         case 'select':
             return $stmt;
             // break;
         // break;
         case 'insert':
         case 'update':
         case 'delete':
             unset($stmt);
             return $this->pdo->lastInsertId();
             // break;
         // break;
         default:
             throw new Afup_Directory_Exception("This request do not return values.");
             break;
     }
 }

 /**
  * Drop test table, only on Travis CI
  */
 public function tearDown()
 {
     if (!$this->isTravisCi()) {
         return true;
     }
     $this->pdo->query("DROP TABLE hello");
 }

function like($pid, $how)
{
    // database connection
    $dsn = "mysql:host=localhost;dbname=fundbook";
    //$options = array(PDO::"MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');
    $db = new PDO($dsn, "root", "fundbook");
    $projects = $db->query("SELECT * FROM projects WHERE pid='{$pid}'");
    foreach ($projects as $project) {
        $author = $project["user"];
        $goal = $project["goal"];
        $category = $project["topic"];
        $title = $project["name"];
        $numBackers = $project["numBackers"];
        $raisedAmt = $project["raisedAmt"];
    }
    if (isset($_COOKIE["email"])) {
        $email = $_COOKIE["email"];
        $interested = $db->query("SELECT * FROM topicInterests WHERE person='{$email}' and topic='{$category}'");
        // then add to interest
        foreach ($interested as $interest) {
            if ($how == 'explicit') {
                $data = array('action' => 'like', "msg" => "added already");
                echo json_encode($data);
                exit;
            }
        }
        $db->query("INSERT INTO topicInterests VALUES ('{$email}', '{$category}')");
        if ($how == 'explicit') {
            $data = array('action' => 'like', 'msg' => 'added');
            echo json_encode($data);
        }
    }
    exit;
}