PHP PDO::prepare方法代码示例

 /**
  * Returns a PDOStatement
  *
  * This method will return the same object for equal queries.
  *
  * @param string $sql
  * @return \PDOStatement
  * @throws \PDOException
  */
 public function prepare($sql)
 {
     if (!array_key_exists($sql, $this->statements)) {
         $this->statements[$sql] = $this->pdo->prepare($sql);
     }
     return $this->statements[$sql];
 }

 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to turn into a prepared statement.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $isObject = $query instanceof Query;
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query);
     return new PDOStatement($statement, $this);
 }

 public static function moveFile($file)
 {
     $dir = 'upload/' . time() . '_';
     $filename = $dir . $file['name'];
     $result = '';
     if (move_uploaded_file($file['tmp_name'], $filename)) {
         try {
             $bdd = new \PDO('mysql:host=localhost;dbname=ecvd_php', 'root', '');
         } catch (Exception $e) {
             die('Erreur : ' . $e->getMessage());
         }
         try {
             $insert_file = $bdd->prepare("INSERT INTO `ecvd_php`.`files` (`id`, `filename`, `path`, `extension`) VALUES ('', ?, ?, ?)");
             $insert_file->execute(array($file['name'], $dir, $file['type']));
             $result = 'Good !';
         } catch (Exception $e) {
             $result = 'Erreur !';
         }
         try {
             $update = $bdd->prepare("UPDATE `users` SET `image_id`= ? WHERE `username` = ?");
             $update->execute(array($bdd->lastInsertId(), $_SESSION['username']));
             $result = 'Good !';
         } catch (Exception $e) {
             $result = 'Erreur !';
             // die("Some error occured while the updating process : ".$e);
         }
     } else {
         $result = 'Erreur !';
     }
     return $result;
 }

 protected function existRow($query)
 {
     $statement = self::$dbh->prepare($query);
     $statement->execute();
     $result = $statement->fetchAll();
     return isset($result[0]['cnt']) && 1 === (int) $result[0]['cnt'];
 }

 public function it_should_be_able_to_delete_an_object(\PDOStatement $pdoStatement)
 {
     $uuid = Uuid::uuid4();
     $this->pdo->prepare(new TypeToken('string'))->willReturn($pdoStatement);
     $pdoStatement->execute(['uuid' => $uuid->getBytes(), 'type' => 'test'])->shouldBeCalled();
     $this->delete('test', $uuid);
 }

 public function testHydrateAll_行が存在しないとき空配列を返す()
 {
     $hydrator = new Mappa\Hydrator();
     $stmt = $this->conn->prepare("SELECT * FROM books JOIN categories ON categories.id = books.category_id WHERE books.id = ?");
     $stmt->execute([100]);
     $this->assertSame([], $hydrator->hydrateAll($stmt, [Book::class, Category::class]));
 }

 /**
  * Adds a new version to an application.
  *
  * @param array $info  Hash with the version information. Possible keys:
  *                     - application: (string) The name of the application.
  *                     - version: (string) The version string.
  *                     - state: (string) The version state. One of
  *                              "stable", "dev", "three". By default
  *                              automatically detected from "version.
  *                     - date: (DateTime) The release date. Defaults to
  *                             today.
  *                     - pear: (boolean) A PEAR release? Defaults to true.
  *                     - dir: (string) Optional website directory, if not
  *                            "application".
  *
  * @throws Horde_Exception
  */
 public function addNewVersion(array $info = array())
 {
     if (!isset($info['application']) || !isset($info['version'])) {
         throw new LogicException('Missing parameter');
     }
     $info = array_merge(array('state' => $this->_stateFromVersion($info['version']), 'date' => new DateTime(), 'pear' => true, 'dir' => null), $info);
     if (!in_array($info['state'], array('stable', 'dev', 'three'))) {
         throw new LogicException('Invalid state ' . $info['state']);
     }
     $info['date'] = $info['date']->format('Y-m-d');
     $bind = array();
     foreach ($info as $key => $value) {
         $bind[':' . $key] = $value;
     }
     try {
         $stmt = $this->_db->prepare('SELECT 1 FROM versions WHERE application = :application AND state = :state');
         $stmt->execute(array(':application' => $info['application'], ':state' => $info['state']));
         $stmt = $stmt->fetchColumn() ? $this->_db->prepare('UPDATE versions SET version = :version, date = :date, pear = :pear, dir = :dir WHERE application = :application AND state = :state') : $this->_db->prepare('INSERT INTO versions (application, state, version, date, pear, dir) VALUES (:application, :state, :version, :date, :pear, :dir)');
         if (!$stmt->execute($bind)) {
             $error = $stmt->errorInfo();
             throw new Horde_Exception($error[2], $error[1]);
         }
     } catch (PDOException $e) {
         throw new Horde_Exception($e);
     }
 }

 public function findBySlug($slug)
 {
     $sql = "SELECT * FROM sections WHERE slug = :slug";
     $stmt = $this->db->prepare($sql);
     $stmt->execute(['slug' => $slug]);
     return $stmt->fetch(\PDO::FETCH_ASSOC);
 }

 /**
  * Queries the Auth table
  */
 public function query($params = array(), $operator = "OR")
 {
     $page = intval(@$params['page']);
     $size = intval(@$params['size']);
     $sort = @$params['sort'];
     unset($params['page']);
     unset($params['size']);
     unset($params['sort']);
     if (!$size) {
         $size = 20;
     }
     $cond = array();
     $bindings = array();
     foreach ($params as $key => $value) {
         $cond[] = sprintf("`%s` %s :where_%s", str_replace('`', '``', $key), is_null($value) ? 'is' : (strpos($value, '%') !== FALSE ? 'LIKE' : '='), $key);
         $bindings[":where_{$key}"] = $value;
     }
     $query = sprintf(self::SELECT_SEARCH, $this->params['table'], empty($cond) ? '1=1' : implode(" {$operator} ", $cond));
     $query .= sprintf(" LIMIT %d, %d", $page * $size, $size);
     $stmt = $this->pdo->prepare($query);
     $stmt->execute($bindings);
     if (!$stmt) {
         $error = $this->pdo->errorInfo();
         throw new \Exception($error[2]);
     }
     $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
     $stmt = $this->pdo->query("SELECT FOUND_ROWS() as count");
     $count = $stmt->fetch(PDO::FETCH_ASSOC);
     if (!$count) {
         throw new \Exception("Error fetching count");
     }
     $count = intval($count['count']);
     return array('_embedded' => array($this->params['table'] => $data), 'page' => array('size' => $size, 'number' => $page, 'totalElements' => $count, 'totalPages' => ceil($count / $size)));
 }

 /**
  * 	Every method which needs to execute a SQL query uses this method.
  * 	
  * 	1. If not connected, connect to the database.
  * 	2. Prepare Query.
  * 	3. Parameterize Query.
  * 	4. Execute Query.	
  * 	5. On exception : Write Exception into the log + SQL query.
  * 	6. Reset the Parameters.
  */
 private function Init($query, $parameters = "")
 {
     # Connect to database
     if (!$this->bConnected) {
         $this->Connect();
     }
     try {
         # Prepare query
         $this->sQuery = $this->pdo->prepare($query);
         # Add parameters to the parameter array
         $this->bindMore($parameters);
         # Bind parameters
         if (!empty($this->parameters)) {
             foreach ($this->parameters as $param) {
                 $parameters = explode("", $param);
                 $this->sQuery->bindParam($parameters[0], $parameters[1]);
             }
         }
         # Execute SQL
         $this->success = $this->sQuery->execute();
     } catch (\PDOException $e) {
         # Write into log and display Exception
         $this->ExceptionLog($e->getMessage(), $query);
         throw new \Exception($e->getMessage());
     }
     # Reset the parameters
     $this->parameters = array();
 }

 /**
  * Prepares a statement for execution and returns a statement object
  *
  * @param string $statement
  *          A valid SQL statement for the target database server
  * @param array $driver_options
  *          Array of one or more key=>value pairs to set attribute values for the PDOStatement obj
  *          returned
  * @return PDOStatement
  */
 public function prepare($statement, $driver_options = false)
 {
     if (!$driver_options) {
         $driver_options = array();
     }
     return $this->PDOInstance->prepare($statement, $driver_options);
 }

 public function __construct($dsn)
 {
     $db = new \PDO($dsn);
     $db->query(self::ERZEUGE_TABELLE);
     $this->getDay = $db->prepare(self::LESE_TAG);
     $this->getAlleBis = $db->prepare(self::LESE_ALLE_BIS);
 }

 function removeArticle($id)
 {
     $query = "DELETE FROM articles WHERE id = :id";
     $stmt = $this->dbh->prepare($query);
     $stmt->bindParam(":id", $id, PDO::PARAM_INT);
     return $stmt->execute();
 }

/**
 * Migrate CMI Worpress posts to Backdrop CMS blog content type.
 * 0: "ID",
 * 1: "post_author",
 * 2: "post_date",
 * 3: "post_date_gmt",
 * 4: "post_content",
 * 5: "post_title",
 * 6: "post_excerpt",
 * 7: "post_status",
 * 8: "comment_status",
 * 9: "ping_status",
 * 10: "post_password" => NOT USED,
 * 11: "post_name",
 * 12: "to_ping",
 * 13: "pinged",
 * 14: "post_modified",
 * 15: "post_modified_gmt",
 * 16: "post_content_filtered",
 * 17: "post_parent",
 * 18: "guid",
 * 19: "menu_order",
 * 20: "post_type",
 * 21: "post_mime_type",
 * 22: "comment_count"
 */
function run_migrate_posts()
{
    $user = 'root';
    $pass = 'pass';
    $wpdb = new PDO('mysql:host=localhost;dbname=cmi_wp', $user, $pass);
    $bddb = new PDO('mysql:host=localhost;dbname=backdrop_cmi', $user, $pass);
    $sql = $wpdb->prepare("select * from wp_posts where post_type = 'post' and post_content != ''");
    $sql->execute();
    $data = $sql->fetchAll();
    $blog_sql = $bddb->prepare("insert into node (\n   nid,\n   vid,\n   type,\n   langcode,\n   title,\n   uid,\n   status,\n   created,\n   changed,\n   comment,\n   promote,\n   sticky,\n   tnid,\n   translate\n ) values (\n     :nid,\n     :vid,\n     :type,\n     :langcode,\n     :title,\n     :uid,\n     :status,\n     :created,\n     :changed,\n     :comment,\n     :promote,\n     :sticky,\n     :tnid,\n     :translate\n   )");
    $body_query = $bddb->prepare("insert into field_data_body (\n     entity_type,\n     bundle,\n     deleted,\n     entity_id,\n     revision_id,\n     language,\n     delta,\n     body_value,\n     body_summary,\n     body_format\n   ) values (\n     'node',\n     'blog',\n     0,\n     :entity_id,\n     :revision_id,\n     'und',\n     0,\n     :body_value,\n     NULL,\n     'full_html'\n   )");
    //$i = 79;
    foreach ($data as $d) {
        $post_author = $d['post_author'] + 1;
        $post_date = strtotime($d['post_date']);
        $post_changed = strtotime($d['post_modified']);
        $blog_binds = array(':nid' => $d['ID'], ':vid' => $d['ID'], ':type' => 'blog', ':langcode' => 'und', ':title' => $d['post_title'], ':uid' => $post_author, ':status' => 1, ':created' => $post_date, ':changed' => $post_changed, ':comment' => 0, ':promote' => 0, ':sticky' => 0, ':tnid' => 0, ':translate' => 0);
        $blog_sql->execute($blog_binds) or die(print_r($blog_sql->errorInfo(), true));
        print $d['post_author'] + 1 . " " . $post_date . "\n";
        $body_binds = array(':entity_id' => $d['ID'], ':revision_id' => $d['ID'], ':body_value' => $d['post_content']);
        $body_query->execute($body_binds) or die(print_r($body_query->errorInfo(), true));
        //$i++;
    }
    print "CMI wp-posts to Backdrop CMS blog content type complete.\n";
}

 public function flush()
 {
     foreach ($this->clientes as $cliente) {
         $stmt = $this->pdo->prepare("INSERT INTO clientes_poo(\n                    nome,nome_empresa,tipo_cliente, endereco, nvlImportancia, telefone,\n                    endereco_cobranca,cpf,cnpj,filiacao)\n                    VALUES(:nome, :nomeEmpresa,:tipoCliente,:endereco, :nvlImportancia, :telefone,:enderecoCobranca, :cpf, :cnpj,:filiacao)");
         $stmt->bindValue(":tipoCliente", $cliente->getTipoCliente());
         $stmt->bindValue(":endereco", $cliente->getEndereco());
         $stmt->bindValue(":nvlImportancia", $cliente->getNvlImportancia());
         $stmt->bindValue(":telefone", $cliente->getTelefone());
         if ($cliente->getEnderecoCobranca()) {
             $stmt->bindValue(":enderecoCobranca", $cliente->getEnderecoCobranca());
         } else {
             $stmt->bindValue(":enderecoCobranca", null);
         }
         if ($cliente instanceof ClientePessoaFisica) {
             $stmt->bindValue(":cpf", $cliente->getCpf());
             $stmt->bindValue(":cnpj", null);
             $stmt->bindValue(":nome", $cliente->getNome());
             $stmt->bindValue(":nomeEmpresa", null);
             $stmt->bindValue(":filiacao", $cliente->getFiliacao());
         } else {
             $stmt->bindValue(":cnpj", $cliente->getCnpj());
             $stmt->bindValue(":cpf", null);
             $stmt->bindValue(":nomeEmpresa", $cliente->getNomeEmpresa());
             $stmt->bindValue(":nome", null);
             $stmt->bindValue(":filiacao", null);
         }
         $stmt->execute();
     }
 }