PHP Classes

File: examples/sqlsrv/testdb.php

Recommend this page to a friend!
  Classes of Jorge Castro   PHP PDO One   examples/sqlsrv/testdb.php   Download  
File: examples/sqlsrv/testdb.php
Role: Example script
Content type: text/plain
Description: Example script
Class: PHP PDO One
Access to database with PDO and run common queries
Author: By
Last change: Update of examples/sqlsrv/testdb.php
Date: 4 years ago
Size: 3,620 bytes
 

Contents

Class file image Download
<?php /** @noinspection SqlDialectInspection */
/** @noinspection SqlNoDataSourceInspection */

/** @noinspection SqlResolve */

use eftec\PdoOne;

include
"../../vendor/autoload.php";


// connecting to database sakila at 127.0.0.1 with user root and password abc.123
$dao=new PdoOne("sqlsrv","127.0.0.1","sa","abc.123","sakila","");
$dao->logLevel=2;
try {
    echo
"<h1>connection. The instance 127.0.0.1, base:sakila user:root and password:abc.123 must exists</h1>";
   
$dao->connect();
    echo
"Connected A-OK!<br>";
} catch (
Exception $e) {
    echo
"<h2>connection error:</h2>";
    echo
$dao->lastError()."-".$e->getMessage()."<br>";
    die(
1);
}

$sql="set nocount on;
    CREATE TABLE dbo.product
    (
    idproduct int NOT NULL IDENTITY (1, 1),
    name varchar(50) NULL
    ) ON [PRIMARY]
;
ALTER TABLE dbo.product ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED
    (
    idproduct
    ) ON [PRIMARY]
"
;

$now=new DateTime();

if (
$dao->tableExist('product')) {
    echo
"<h1>Table product exist!</h1>";
} else {
    try {
        echo
"<h1>Table creation:</h1>";

       
$dao->runRawQuery($sql);
    } catch (
Exception $e) {
        echo
"<h2>Table creation error:</h2>";
        echo
$dao->lastError()."-".$e->getMessage()."<br>";
    }

}


// running a raw query (unprepared statement)


// running a prepared statement
try {
    echo
"<h1>Inserting Cocacola (prepared)</h1>";
   
$sql="insert into product(name) values(?)";
   
$stmt=$dao->prepare($sql);
   
$productName="Cocacola";
   
$stmt->bindParam(1,$productName,PDO::PARAM_STR); // s stand for string. Also i =integer, d = double and b=blob
   
$dao->runQuery($stmt);
   
$dao->runQuery($dao->prepare('select 1'));
    echo
"Last id inserted :".$dao->insert_id()."<br>";
} catch (
Exception $e) {
    echo
"<h2>Insert Cocacola (prepared) error:</h2>";
    echo
$dao->lastError()."-".$e->getMessage()."<br>";
}

try {
    echo
"<h1>Inserting O'Hara (prepared)</h1>";
   
$sql="insert into product(name) values(?)";
   
$stmt=$dao->prepare($sql);
   
$productName="O'Hara";
   
$stmt->bindParam(1,$productName,PDO::PARAM_STR); // s stand for string. Also i =integer, d = double and b=blob
   
$dao->runQuery($stmt);
    echo
"Last id inserted :".$dao->insert_id()."<br>";
} catch (
Exception $e) {
    echo
"<h2>Insert (prepared) error:</h2>";
    echo
$dao->lastError()."-".$e->getMessage()."<br>";
}


// returning data (using a prepared statement)
try {
    echo
"<h1>select (prepared)</h1>";
   
$sql="select * from product order by name";
   
$stmt=$dao->prepare($sql);
   
$dao->runQuery($stmt);



    echo
"<table><tr><th>Id</th><th>Name</th></tr>";

   
// first method
   
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo
"<tr><td>".$row['idproduct']."</td><td>".$row['name']."</td></tr>";
    }
    echo
"</table><br>";
   
// second method (fetch all fields)
    //$allRows=$rows->fetch_all(PDO::FETCH_ASSOC);

} catch (Exception $e) {
    echo
"<h1>select (prepared) error:</h1>";
    echo
$dao->lastError()."-".$e->getMessage()."<br>";
}


// running a transaction
try {
    echo
"<h1>Insert transactional (prepared)</h1>";
   
$sql="insert into product(name) values(?)";
   
$dao->startTransaction();
   
$stmt=$dao->prepare($sql);
   
$productName="Fanta";
   
$stmt->bindParam(1,$productName,PDO::PARAM_STR); // s stand for string. Also i =integer, d = double and b=blob
   
$dao->runQuery($stmt);
    echo
"Last id inserted :".$dao->insert_id()."<br>";
   
$dao->commit(); // end transaction
} catch (Exception $e) {
    echo
"<h2>Insert transactional error:</h2>";
    echo
$dao->lastError()."-".$e->getMessage()."<br>";

   
$dao->rollback(); // cancel transaction

}