示例代码

ZendFramework-1.x 示例

SQL

CREATE TABLE foo (Id INT AUTO_INCREMENT PRIMARY KEY,bar VARCHAR(32), baz VARCHAR(32));
CREATE TABLE bar (Id INT AUTO_INCREMENT PRIMARY KEY,bar VARCHAR(32), baz VARCHAR(32));
CREATE TABLE "round_table" (Id INT AUTO_INCREMENT PRIMARY KEY,noble_title VARCHAR(32), first_name,VARCHAR(32), favorite_color VARCHAR(32));
CREATE TABLE LobTab (Id INT AUTO_INCREMENT PRIMARY KEY,data BLOB,text CLOB);

Query 示例

<?php
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

$params = array('host'=>'127.0.0.1',
    'username'=>'sysdba',
    'password'=>'szoscar55',
    'port'=>'2003',
    'dbname'=>'osrdb'
    );
$db = Zend_Db::factory('PDO_Aci',$params);

// 创建一个$db对象,然后查询数据库
// 使用完整的sql语句直接进行查询.
$result = $db->query('SELECT * FROM LOBTAB');

//print_r($result);

// // 使用PDOStatement对象$result将所有结果数据放到一个数组中
$rows = $result->fetchAll();

print_r($rows);

?>

SELECT WHERE 示例

<?php
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

$params = array('host'=>'127.0.0.1',
    'username'=>'sysdba',
    'password'=>'szoscar55',
    'port'=>'2003',
    'dbname'=>'osrdb'
    );
$db = Zend_Db::factory('PDO_Aci',$params);

$select = $db->select();

//
// SELECT *
//     FROM round_table
//     WHERE noble_title = "Sir"
//     AND favorite_color = "yellow"
//
$select->from('round_table', '*');
$select->where('noble_title = \'Sir\''); // embedded value
$select->where('favorite_color = ?', 'yellow'); // quoted value

$select->orWhere('id IN(?)', array(1, 2, 3));

$result = $db->fetchAll($select);
?>

SELECT JOIN 示例

<?php
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

$params = array('host'=>'127.0.0.1',
    'username'=>'sysdba',
    'password'=>'szoscar55',
    'port'=>'2003',
    'dbname'=>'osrdb'
    );
$db = Zend_Db::factory('PDO_Aci',$params);

$select = $db->select();

//
// SELECT foo.*, bar.*
//     FROM foo
//     JOIN bar ON foo.id = bar.id
//

$select->from('foo', '*');
$select->join('bar', 'foo.id = bar.id', '*');

$result = $db->fetchAll($select);

echo "<p>";
    foreach($result as $key=>$val){
        echo $key;
        echo "值为：";
        echo $val;
        echo "<p>";
    }
?>

SELECT GROUP 示例

<?php
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

$params = array('host'=>'127.0.0.1',
    'username'=>'sysdba',
    'password'=>'szoscar55',
    'port'=>'2003',
    'dbname'=>'osrdb'
    );
$db = Zend_Db::factory('PDO_Aci',$params);

$select = $db->select();
$select->from('USER','COUNT(ID)');
$select->group('ID');
$result = $db->fetchAll($select);
echo "<p>";
foreach($result as $key=>$val){
    echo $key;
    echo "值为：";
    print("<pre>"); // 格式化输出数组
    print_r($val);
    print("</pre>");
}
echo "</p>";
?>

FECTH* 示例

<?php
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

$params = array('host'=>'127.0.0.1',
    'username'=>'sysdba',
    'password'=>'szoscar55',
    'port'=>'2003',
    'dbname'=>'osrdb'
    );
$db = Zend_Db::factory('PDO_Aci',$params);

// 创建一个 $db对象, 然后...

// 取回结果集中所有字段的值,作为连续数组返回
$result = $db->fetchAll(
    "SELECT * FROM \"round_table\" WHERE noble_title = :title",
    array('title' => 'King')
);

echo "<p>";
foreach($result as $key=>$val){
    echo $key;
    echo "值为：";
    print("<pre>"); // 格式化输出数组
    print_r($val);
    print("</pre>");
}
echo "</p>";

// 取回结果集中所有字段的值,作为关联数组返回
// 第一个字段作为码
$result = $db->fetchAssoc(
    "SELECT * FROM \"round_table\" WHERE noble_title = :title",
    array('title' => 'King')
);

print_r($result);

// 取回所有结果行的第一个字段名
$result = $db->fetchCol(
    "SELECT first_name FROM \"round_table\" WHERE noble_title = :title",
    array('title' => 'King')
);

print_r($result);

// 只取回第一个字段值
$result = $db->fetchOne(
    "SELECT COUNT(*) FROM \"round_table\" WHERE noble_title = :title",
    array('title' => 'King')
);

print_r($result);

// 取回一个相关数组,第一个字段值为码
// 第二个字段为值
$result = $db->fetchPairs(
    "SELECT first_name, favorite_color FROM \"round_table\" WHERE noble_title = :title",
    array('title' => 'King')
);

print_r($result);

// 只取回结果集的第一行
$result = $db->fetchRow(
    "SELECT * FROM \"round_table\" WHERE first_name = :name",
    array('name' => 'Arthur')
);

print_r($result);

?>

INSERT 示例

<?php
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

$params = array('host'=>'127.0.0.1',
    'username'=>'sysdba',
    'password'=>'szoscar55',
    'port'=>'2003',
    'dbname'=>'osrdb'
    );
$db = Zend_Db::factory('PDO_Aci',$params);

// 创建一个 $db对象, 然后...
// 以"列名"=>"数据"的格式格式构造插入数组,插入数据行
$row = array (
    'data'    => '0x612612667176166616671622',
    'text'     => 'Arthur23'
);

// 插入数据的数据表
$table = 'LOBTAB';

// i插入数据行并返回行数
$rows_affected = $db->insert($table, $row);
$last_insert_id = $db->lastInsertId();

echo 'insert_id:' . $last_insert_id . ',rows_affected:' . $rows_affected;

echo '<br/>';

?>

Update 示例

<?php
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

$params = array('host'=>'127.0.0.1',
    'username'=>'sysdba',
    'password'=>'szoscar55',
    'port'=>'2003',
    'dbname'=>'osrdb'
    );
$db = Zend_Db::factory('PDO_Aci',$params);

//
// UPDATE round_table
//     SET favorite_color = "yellow"
//     WHERE first_name = "Robin";
//

// 创建一个 $db对象, 然后...
// 以"列名"=>"数据"的格式构造更新数组,更新数据行
$set = array (
    'FAVORITE_COLOR' => 'yellow',
);

// 更新的数据表
$table = 'round_table';

// where语句
$where = $db->quoteInto('ID = ?', 2);

// 更新表数据,返回更新的行数
$rows_affected = $db->update($table, $set, $where);

echo $rows_affected;

?>

LOB 示例

<?php
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

$params = array('host'=>'127.0.0.1',
    'username'=>'sysdba',
    'password'=>'szoscar55',
    'port'=>'2003',
    'dbname'=>'osrdb'
    );
$db = Zend_Db::factory('PDO_Aci',$params);

$path = '1.jpg';

$file=fopen($path,'rb');
//$r = fread($file,filesize($path));

// 插入数据的数据表
$table = 'LOBTAB';
#$db->beginTransaction();
// i插入数据行并返回行数
$stmt = $db->prepare('insert into LOBTAB (data,text) values (empty_blob(),:text) returning data into :data');
//$stmt = $db->prepare('insert into LOBTAB (data,text) values (:data,:text)');
$stmt->bindValue(':data', $file,PDO::PARAM_LOB);
$stmt->bindValue(':text', 'ss111d');

$stmt->execute();
$db->commit();

fclose($file);


$row = $db->fetchRow('SELECT * FROM LOBTAB WHERE ID = 2');

print_r($row);

$data = $row['DATA'];

$myfile = fopen("2.jpg", "wb");
fwrite($myfile, $data);
fclose($myfile);
?>

事务示例

<?php
require_once 'Zend/Db.php';
require_once 'Zend/Db/Select.php';

$params = array('host'=>'127.0.0.1',
    'username'=>'sysdba',
    'password'=>'szoscar55',
    'port'=>'2003',
    'dbname'=>'osrdb'
    );
$db = Zend_Db::factory('PDO_Aci',$params);

// 创建一个 $db对象, 然后开始做一个事务处理.
$db->beginTransaction();

$row = array (
    'data'    => '0x612612667176166616671622',
    'text'     => 'Arthur23'
);

// 插入数据的数据表
$table = 'LOBTAB';

// 尝试数据库操作.
// 假如成功,commit该操作;
// 假如, roll back.
try {
    // i插入数据行并返回行数
    $rows_affected = $db->insert($table, $row);
    $db->commit();
} catch (Exception $e) {
    $db->rollBack();
    echo $e->getMessage();
}

?>

Table 增删改查操作示例

<?php

require_once 'Zend/Db.php';
$params = array('host'=>'127.0.0.1',
    'username'=>'sysdba',
    'password'=>'szoscar55',
    'port'=>'2003',
    'dbname'=>'osrdb'
    );
$db = Zend_Db::factory('PDO_Aci',$params);

// 为所有的Zend_Db_Table对象设定默认的adapter
require_once 'Zend/Db/Table.php';
Zend_Db_Table::setDefaultAdapter($db);

class RoundTable extends Zend_Db_Table {
    protected $_name = 'round_table';

}

$table = new RoundTable();

// insert

$data = array(
    'NOBLE_TITLE' => 'King11',
    'FIRST_NAME'  => 'Arthur11',
    'FAVORITE_COLOR' => 'blue11',
);

$id = $table->insert($data);

echo $id;

// select

// SELECT * FROM round_table WHERE id = "1"
$row = $table->find(1);

print_r($row);

// SELECT * FROM round_table WHERE id IN("1", "2", 3")
// $rowset = $table->find(array(1, 2, 3));

// print_r($rowset);
// echo $row->id;


// update

$db = $table->getAdapter();

$set = array(
    'favorite_color' => 'yellow'
);

$where = $db->quoteInto('first_name = ?', 'Robin');

$rows_affected = $table->update($set, $where);

// delete

$where = $db->quoteInto('first_name = ?', 'Patsy');

$rows_affected = $table->delete($where);

print_r($rows_affected);
?>

ZendFramework-2.x 示例

SQL

DROP TABLE "pdoaci_tab1";
CREATE TABLE "pdoaci_tab1" ("id" INT AUTO_INCREMENT PRIMARY KEY, "name" VARCHAR(32));
INSERT INTO "pdoaci_tab1" ("name") values ('name1'),('name2');

Query 示例

<?php

require '../load.php';

use Zend\Db\Adapter\Adapter;

$adapter = new Adapter(array(
    'driver'   => 'pdo_aci',
    'host'     => '127.0.0.1',
    'port'     => '2003',
    'database' => 'osrdb',
    'username' => 'sysdba',
    'password' => 'szoscar55',
    'charset'  => 'utf8'
));

$users = $adapter->query("select version", Adapter::QUERY_MODE_EXECUTE);

$size = $users->count();

print_r($size);
print("\n");

$cur = $users->current();
print_r($cur);
print("\n");

SELECT 示例

<?php

require '../load.php';

use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;

$adapter = new Adapter(array(
    'driver'   => 'pdo_aci',
    'host'     => '127.0.0.1',
    'port'     => '2003',
    'database' => 'osrdb',
    'username' => 'sysdba',
    'password' => 'szoscar55',
    'charset'  => 'utf8'
));

class Pdoaci_Tab1
{
    public $Id;
    public $Name;

    public function exchangeArray($data)
    {
        $this->Id     = (!empty($data['id'])) ? $data['id'] : null;
        $this->Name   = (!empty($data['name'])) ? $data['name'] : null;
    }
}

$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Pdoaci_Tab1());
$table_gateway = new TableGateway('pdoaci_tab1', $adapter, null, $resultSetPrototype);

$rowset = $table_gateway->select(array('id' => 1));
$row = $rowset->current();
print_r($row);

$rows = $table_gateway->select();

foreach($rows as $item)
{
    print_r($item);
}

INSERT 示例

<?php

require '../load.php';

use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;

$adapter = new Adapter(array(
    'driver'   => 'pdo_aci',
    'host'     => '127.0.0.1',
    'port'     => '2003',
    'database' => 'osrdb',
    'username' => 'sysdba',
    'password' => 'szoscar55',
    'charset'  => 'utf8'
));

class Pdoaci_Tab1
{
    public $Id;
    public $Name;

    public function exchangeArray($data)
    {
        $this->Id     = (!empty($data['id'])) ? $data['id'] : null;
        $this->Name   = (!empty($data['name'])) ? $data['name'] : null;
    }
}

$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Pdoaci_Tab1());
$table_gateway = new TableGateway('pdoaci_tab1', $adapter, null, $resultSetPrototype);

$state = $table_gateway->insert(array('name' => 'name3'));

print_r($state);
print("\n");

$rows = $table_gateway->select();

foreach($rows as $item)
{
    print_r($item);
}

UPDATE 示例

<?php

require '../load.php';

use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;

$adapter = new Adapter(array(
    'driver'   => 'pdo_aci',
    'host'     => '127.0.0.1',
    'port'     => '2003',
    'database' => 'osrdb',
    'username' => 'sysdba',
    'password' => 'szoscar55',
    'charset'  => 'utf8'
));

class Pdoaci_Tab1
{
    public $Id;
    public $Name;

    public function exchangeArray($data)
    {
        $this->Id     = (!empty($data['id'])) ? $data['id'] : null;
        $this->Name   = (!empty($data['name'])) ? $data['name'] : null;
    }
}

$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Pdoaci_Tab1());
$table_gateway = new TableGateway('pdoaci_tab1', $adapter, null, $resultSetPrototype);

$state = $table_gateway->update(array('name' => 'newname1'),array('id'=>1));

print_r($state);
print("\n");

$rows = $table_gateway->select();

foreach($rows as $item)
{
    print_r($item);
}

DELETE 示例

<?php

require '../load.php';

use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;

$adapter = new Adapter(array(
    'driver'   => 'pdo_aci',
    'host'     => '127.0.0.1',
    'port'     => '2003',
    'database' => 'osrdb',
    'username' => 'sysdba',
    'password' => 'szoscar55',
    'charset'  => 'utf8'
));

class Pdoaci_Tab1
{
    public $Id;
    public $Name;

    public function exchangeArray($data)
    {
        $this->Id     = (!empty($data['id'])) ? $data['id'] : null;
        $this->Name   = (!empty($data['name'])) ? $data['name'] : null;
    }
}

$resultSetPrototype = new ResultSet();
$resultSetPrototype->setArrayObjectPrototype(new Pdoaci_Tab1());
$table_gateway = new TableGateway('pdoaci_tab1', $adapter, null, $resultSetPrototype);

$state = $table_gateway->delete(array('id' => 2));

print_r($state);
print("\n");

$rows = $table_gateway->select();

foreach($rows as $item)
{
    print_r($item);
}