แนะนำการใช้งาน ADOdb PHP Database Abstraction Layer

composer require adodb/adodb-php ^5.20

<?php
// include composer autoload
require_once './vendor/autoload.php';

<?php
// include adodb
include './adodb5/adodb.inc.php';

<?php
include './adodb5/adodb.inc.php';

$driver = 'mysqli'; // เลือก driver
$db = newAdoConnection($driver);

// กำหนดค่าสำหรับการเชื่อมต่อฐานข้อมูล
$db->connect('localhost', 'root', '', 'test');

$db->connect('localhost', 'root', '', 'test');
if($db->isConnected()){
    echo "Connected";
}

$db->connect('localhost', 'root', '', 'test');
if(!$db->isConnected()){
    die("Can't connect to Database Server");
}

$db->connect('localhost', 'root', '', 'test');
if(!$db->isConnected()){
    die("Can't connect to Database Server");
}
$db->setCharset('utf8');

$db->close();

$sql = "select province_id,province_name from tbl_provinces";
$result = $db->getAll($sql);
if($result){
    foreach ($result as $row){
        echo $row['province_name']."<br>";
    }
}

Array
(
    [0] => Array
        (
            [0] => 1
            [province_id] => 1
            [1] => กรุงเทพมหานคร
            [province_name] => กรุงเทพมหานคร
        )

)

$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
// $db->setFetchMode(ADODB_FETCH_ASSOC);
$sql = "select province_id,province_name from tbl_provinces LIMIT 1";
$result = $db->getAll($sql);

// ADODB_FETCH_ASSOC
Array
(
    [0] => Array
        (
            [province_id] => 1
            [province_name] => กรุงเทพมหานคร
        )

)
// ADODB_FETCH_NUM
Array
(
    [0] => Array
        (
            [0] => 1
            [1] => กรุงเทพมหานคร
        )

)
// ADODB_FETCH_BOTH 
Array
(
    [0] => Array
        (
            [0] => 1
            [province_id] => 1
            [1] => กรุงเทพมหานคร
            [province_name] => กรุงเทพมหานคร
        )

)

$ADODB_FETCH_MODE = ADODB_FETCH_NUM;  
 $rs1 = $db->getAll('select * from table');  
 
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;  
 $rs2 = $db->getAll('select * from table'); 

include './adodb5/adodb.inc.php';
define('ADODB_ASSOC_CASE', ADODB_ASSOC_CASE_UPPER);
// รองรับค่า ADODB_ASSOC_CASE_LOWER ADODB_ASSOC_CASE_UPPER
// และ ADODB_ASSOC_CASE_NATIVE

$sql = "SELECT * FROM tbl_provinces";
$result = $db->getAssoc($sql);
if($result){
    foreach ($result as $row){
        echo $row['province_name']."<br>";
    }
}

กรุงเทพมหานคร
Array
(
    [1] => Array
        (
            [province_code] => 10
            [province_name] => กรุงเทพมหานคร
            [province_name_eng] => Bangkok
            [geo_id] => 2
            [last_update] => 2018-09-19 12:11:03
        )

)

$sql = "SELECT province_id,province_name FROM tbl_provinces";
$result = $db->getAssoc($sql);

Array
(
    [1] => กรุงเทพมหานคร
    [2] => สมุทรปราการ
)

$sql = "SELECT province_code,province_id,province_name FROM tbl_provinces ";
$result = $db->getAssoc($sql);

Array
(
    [10] => Array
        (
            [province_id] => 1
            [province_name] => กรุงเทพมหานคร
        )

    [11] => Array
        (
            [province_id] => 2
            [province_name] => สมุทรปราการ
        )

)

$sql = "SELECT province_id,province_name FROM tbl_provinces ";
$result = $db->selectLimit($sql,10,0);

$sql = "SELECT province_id,province_name FROM tbl_provinces ";
$result = $db->selectLimit($sql,10,0);
if($result){
    while($row = $result->fetchRow()){
        echo $row['province_name']."<br>";
    }
}

$sql = "SELECT province_id,province_name FROM tbl_provinces WHERE province_id=1 ";
//$sql = "SELECT province_id,province_name FROM tbl_provinces ";
$row = $db->getRow($sql);
if($row){
    echo $row['province_name'];
}

$sql = "SELECT province_id,province_name FROM tbl_provinces ";
$result = $db->execute($sql);
if($result){
    while($row = $result->fetchRow()){ // กรณีต้องการเป็น object ให้ใช้เป็น fetchObj()
        echo $row['province_name'];
    }
}

$sql = "
INSERT INTO tbl_users (name,username,email) 
VALUES ('test','test username','test@localhost.com')
";
if($db->execute($sql)){  // เพิ่มข้อมูลสำเร็จ
    echo "Data Inserted";
    $lastId = $db->insert_Id(); // ได้ค่า id ที่เพิ่มล่าสุด
}

$sql = "
UPDATE tbl_users SET 
name='edit test'
WHERE id=1
";
if($db->execute($sql)){ // แก้ไขข้อมูลสำเร็จ
    echo "Data Updated";
    echo $db->affected_rows(); // ได้จำนวนแถวที่แก้ไขล่าสุด
}

$bindVars = [1,10];
$sql = "
SELECT province_id,province_name FROM tbl_provinces 
WHERE province_id=? AND province_code=?
";
$result = $db->execute($sql, $bindVars);
if($result){
    while($row = $result->fetchRow()){
        $row['province_name'];
    }
}

$table = 'tbl_users';
$data["name"] = "Smith";
$data["username"]  = "Smith";
$data["email"]  = "Smith@localhost.com";

$result = $db->autoExecute($table,$data,'INSERT'); // เพิ่มข้อมูลใหม่
if($result){
    echo "Data Inserted";
    echo $db->insert_id();
}
// จะได้ชุดคำสั่ง SQL เป็น
// "INSERT INTO tbl_users (name,username,email) 
// values ('Smith',Smith','Smith@localhost.com')";

$table = 'tbl_users';
$data["name"] = "Smith";
$data["username"]  = "SmithEdit";
$data["email"]  = "Smith@localhost.com";
$where = "id = '2' ";

$result = $db->autoExecute($table,$data,'UPDATE',$where);
if($result){
    echo "Data Updated";
    echo $db->affected_rows();
}
// จะได้ชุดคำสั่ง SQL เป็น
// "UPDATE tbl_users SET 
// name = 'Smith',
// username ='Smith',
// email = 'Smith@localhost.com' WHERE id = '2' ";

$ADODB_QUOTE_FIELDNAMES = 'NATIVE';
$table = 'tbl_users';
$data = [
    'name' => 'Smith',
    'username' => 'Smith',
    'email' => 'Smith@localhost.com'
];

$result = $db->autoExecute($table,$data,'INSERT'); // เพิ่มข้อมูลใหม่
// จะได้ชุดคำสั่ง SQL เป็น
// "INSERT INTO `tbl_users` (`name`,`username`,`email`) 
// values ('Smith',Smith','Smith@localhost.com')";

ค่าที่กำหนด	ผลลัพธ์
false		SELECT field_name FROM table
true		SELECT `field_name` FROM table
UPPER		SELECT `FIELD_NAME` FROM table
LOWER		SELECT `field_name` FROM table
NATIVE		SELECT `Field_Name` FROM table

$table = 'tbl_users';
$indexColumn = 'id';
$data = [
    'name' => "'Lisa'",
    'username' => "'Lisa'",
    'email' => "'Lisa@localhost.com'"
];

$result = $db->replace($table,$data,$indexColumn);
if($result==2){ 
    echo "Data Inserted";
    echo $db->insert_id();
}

$table = 'tbl_users';
$indexColumn = 'id';
$data = [
    'id' => 3,
    'name' => "'Lisa Edit'",
    'username' => "'Lisa'",
    'email' => "'Lisa@localhost.com'"
];

$result = $db->replace($table,$data,$indexColumn);
if($result==1){ 
    echo "Data Updated";
    echo $db->affected_rows();
}

$sql = "SELECT province_id,province_name FROM tbl_provinces ";
$total = $result->recordCount();
echo $total; // จำนวนราการทั้งหมด 77 รายการ
if($result){
    while($row = $result->fetchRow()){ // กรณีต้องการเป็น object ให้ใช้เป็น fetchObj()
        echo $row['province_name'];
    }
}

$sql = "SELECT provinceID FROM tbl_provinces";
$result = $db->execute($sql);
if(!empty($e = $db->errorMsg())){
    die($e);  // Unknown column 'provinceID' in 'field list'
};
if($result){
    while($row = $result->fetchRow()){ // กรณีต้องการเป็น object ให้ใช้เป็น fetchObj()
        echo $row['province_name'];
    }
}