PHPとPDONEを使用したMySQLデータベースへのアクセス

請求書の例。

例データベースの作成

CREATE SCHEMA `example-pdo` ;

CREATE TABLE `customers` (
 `IdCustomer` INT NOT NULL AUTO_INCREMENT,
 `Name` VARCHAR(45) NULL,
 PRIMARY KEY (`IdCustomer`));

CREATE TABLE `invoices` (
 `IdInvoice` INT NOT NULL,
 `Date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
 `IdCustomer` INT NULL,
 PRIMARY KEY (`IdInvoice`),
 INDEX `invoices_fk1_idx` (`IdCustomer` ASC) VISIBLE,
 CONSTRAINT `invoices_fk1`
  FOREIGN KEY (`IdCustomer`)
  REFERENCES `customers` (`IdCustomer`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION);

CREATE TABLE `invoice_detail` (
 `IdInvoiceDetail` INT NOT NULL AUTO_INCREMENT,
 `Product` VARCHAR(45) NULL,
 `UnitPrice` DECIMAL(10,2) NULL,
 `Quantity` INT NULL,
 PRIMARY KEY (`IdInvoiceDetail`));

CREATE TABLE `invoice_details` (
 `IdInvoiceDetail` INT NOT NULL AUTO_INCREMENT,
 `IdInvoice` INT NULL,
 `Product` VARCHAR(45) NULL,
 `UnitPrice` DECIMAL(10,2) NULL,
 `Quantity` INT NULL,
 PRIMARY KEY (`IdInvoiceDetail`),
 INDEX `invoice_detail_fk1_idx` (`IdInvoice` ASC) VISIBLE,
 CONSTRAINT `invoice_detail_fk1`
  FOREIGN KEY (`IdInvoice`)
  REFERENCES `invoices` (`IdInvoice`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION);

データベースへの接続

$pdoOne=new PdoOne('mysql','127.0.0.1','root','abc.123','example-pdo'); // type of database, server, user, password, schema name.
$pdoOne->open(); // we will open the connection
$pdoOne->logLevel=3; // it shows all errors with arguments (it is for debug purpose)

方法1 :生のクエリを使用する。

挿入

$statement=$pdoOne->runRawQuery('insert into customers(name) values (?)',['John Simple'],false);
$statement->closeCursor(); // it is a pdostatement
$statement=null; 
$idCustomer=$pdoOne->insert_id();

$pdoOne->runRawQuery('insert into customers(name) values (?)',['John Simple'],true);
$idCustomer=$pdoOne->insert_id(); // we get the identity.

$pdoOne->runRawQuery('insert into customers(name) values (:name)',['name'=>'John Simple'],true);
$idCustomer=$pdoOne->insert_id();
echo "added user $idCustomer";

リスティング

$customers=$pdoOne->runRawQuery('select * from customers where name=?',['John Simple']);
var_dump($customers);

単一のオブジェクトを取得する

$customers=$pdoOne->runRawQuery('select * from customers where idcustomer',[1]);
var_dump($customers); // [0=>['idcustomer'=>1,'name'=>'john']]
var_dump($customers[0]); // ['idcustomer'=>1,'name'=>'john']

完全請求書の挿入

// invoice header
$invoice=['IdInvoice'=>1,'Date'=> PdoOne::dateSqlNow(true), 'IdCustomer'=>1];
$pdoOne->runRawQuery('insert into invoices(idinvoice,date,idcustomer) values(:IdInvoice,:Date,:IdCustomer)',$invoice);
// Creating the detail of the invoice
$invoiceDetail=[1,'Cocacola',1000,3];
$query='insert into invoice_details(idinvoice,product,unitprice,quantity) values (?,?,?,?)';
$pdoOne->runRawQuery($query, $invoiceDetail);
$invoiceDetail=[1,'Fanta',2000,5];
$query='insert into invoice_details(idinvoice,product,unitprice,quantity) values (?,?,?,?)';
$pdoOne->runRawQuery($query, $invoiceDetail);

方法2 :メソッドチェーンの使用

挿入

$idCustomer=$pdoOne
  ->from('customers')
  ->set(['name'=>'John Chain #1'])
  ->insert(); // $idcustomer gets the identity if any

$idCustomer=$pdoOne->insert('customers',['name'=>'John Chain #2']); // $idcustomer gets the identity if any

リスティング

$customers=$pdoOne->select('*')
  ->from('customers')
  ->toList(); // end of the chain, it returns an associative array with the values.

単一のオブジェクトを取得する

$customer=$pdoOne->select('*')
  ->from('customers')
  ->where('idcustomer=?',[1])
  ->first();

完全請求書の挿入

// invoice header
$invoice=['IdInvoice'=>1,'Date'=> PdoOne::dateSqlNow(true), 'IdCustomer'=>1];
$pdoOne->set($invoice)->from('invoices')->insert();

// Creating the detail of the invoice
$invoiceDetail=['IdInvoice'=>1,'Product'=>'Cocacola','UnitPrice'=>1000,'Quantity'=>3];
$pdoOne->set($invoiceDetail)->from('invoice_details')->insert();

$invoiceDetail=['IdInvoice'=>1,'Product'=>'Fanta','UnitPrice'=>5000,'Quantity'=>5];
$pdoOne->set($invoiceDetail)->from('invoice_details')->insert();

方法3 :リポジトリクラスの使用

クラスの作成

$errors=$pdoOne->generateAllClasses(
  [
​    'customers'=>['CustomerRepo','Customer'], // table => [repo class, model class]
​    'invoice_details'=>['InvoiceDetailRepo','InvoiceDetail'],
​    'invoices'=>['InvoiceRepo','Invoice']
  ]
  ,'BaseRepo' // it a common class
  ,['example\repo','example\model'] // namespaces of the repository class and model class.
  ,[__DIR__.'\repo',__DIR__.'\model']); // folders to where the repositories and model will be generated, they should exist.

$errors=$pdoOne->generateAllClasses(
  [
​    'customers'=>'CustomerRepo', // table => repo class
​    'invoice_details'=>'InvoiceDetailRepo',
​    'invoices'=>'InvoiceRepo'
  ]
  ,'BaseRepo' // it a common class
  ,'example\repo' // namespaces of the repository class.
  ,__DIR__.'\repo'); // folders to where the repositories will be generated, they should exist.

挿入

BaseRepo::$useModel=false; // we will use array instead of objects

// using factory
$cus=CustomerRepo
  ::setRecursive('*')
  ::factory(['Name' => 'John Generated #1 (Array)']);
// using an array (the keys of the arrays must matches the columns of the table, including the case)
$cus=['Name' => 'John Generated #1 (Array)']; // correct
$cus=['name' => 'John Generated #1 (Array)']; // not correct (the column is called 'Name', not 'name')

// using the constructor
$cus=new Customer(['Name' => 'John Generated #1 (Array)']);
// with empty constructor
$cus=new Customer();
$cus->Name='John Generated #1 (Array)';

$identity=CustomerRepo::insert($cus);

リスティング

$r=CustomerRepo::where('IdCustomer',[1])::toList(); // select IdCustomer,Name from customers where IdCustomer=1

単一のオブジェクトを取得する

$invoice=CustomerRepo::where('IdCustomer',[1])::first();

完全請求書の挿入

// header
$invoice=InvoiceRepo::setRecursive('*')::factory(['IdInvoice'=>1,'Date'=> PdoOne::dateSqlNow(true), 'IdCustomer'=>1]);
// InvoiceDetailRepo: IdInvoice islinked automatically, and IdInvoiceDetail is identity.
$invoice['_invoice_details']=[
    InvoiceDetailRepo::factory(['Product' => 'Cocacola','UnitPrice' => 1000,'Quantity' => 3]), 
    InvoiceDetailRepo::factory(['Product' => 'Fanta','UnitPrice' => 5000,'Quantity' => 2])
];

InvoiceRepo::setRecursive('_invoice_details')::insert($invoice);

// header
$invoice=new Invoice();
$invoice->IdInvoice=1;
$invoice->Date=PdoOne::dateSqlNow(true);
$invoice->IdCustomer=1;

// InvoiceDetailRepo: IdInvoice islinked automatically, and IdInvoiceDetail is identity.
$invoice->_invoice_details=[
  new InvoiceDetail(['Product' => 'Cocacola','UnitPrice' => 1000,'Quantity' => 3]),
  new InvoiceDetail(['Product' => 'Fanta','UnitPrice' => 5000,'Quantity' => 2])
];
InvoiceRepo::setRecursive('_invoice_details')::insert($invoice);

insert into invoices(..) values (..);
insert into invoice_details(..) values (..);
insert into invoice_details(..) values (..);
insert into customer(..) values (..); -- however we don't want to insert the customer.

// examples:
// invoices->customers
InvoiceRepo::setRecursive(['_customers']); 
// invoice->customers and invoice->invoice_details
InvoiceRepo::setRecursive(['_customers','_invoice_details']); 
// invoice->customers , invoice->invoice_details and invoice_details->invoices
InvoiceRepo::setRecursive(['_customers','_invoice_details/_invoices']); 

エンドノート.

$invoices=InvoiceRepo::toList();  // select IdInvoice,Date,IdCustomer from invoices
$invoices=InvoiceRepo::base()->runRawQuery('select * from invoices'); // select * from invoices
$invoices=InvoiceRepo::base()->select('*')->from('invoices')->toList(); // select * from invoices