การเชื่อมต่อ และใช้งาน Database ใน CodeIgniter 4

        'hostname' => 'localhost', // DB server ip หรือ โดนเมน ปกติใช้ localhost
        'username' => 'ชื่อผู้ใช้',
        'password' => 'รหัสผ่าน',
        'database' => 'ชื่อฐานข้อมูล',
        'DBDriver' => 'MySQLi',

/**
 * The default database connection.
 *
 * @var array
 */
public $default = [
	'DSN'      => '',
	'hostname' => 'localhost',
	'username' => '',
	'password' => '',
	'database' => '',
	'DBDriver' => 'MySQLi',
	'DBPrefix' => '',
	'pConnect' => false,
	'DBDebug'  => (ENVIRONMENT !== 'production'),
	'cacheOn'  => false,
	'cacheDir' => '',
	'charset'  => 'utf8',
	'DBCollat' => 'utf8_general_ci',
	'swapPre'  => '',
	'encrypt'  => false,
	'compress' => false,
	'strictOn' => false,
	'failover' => [],
	'port'     => 3306,
];

$default['failover'] = [
                [
                        'hostname' => 'localhost1',
                        'username' => '',
                        'password' => '',
                        'database' => '',
                        'DBDriver' => 'MySQLi',
                        'DBPrefix' => '',
                        'pConnect' => TRUE,
                        'DBDebug'  => TRUE,
                        'cacheOn'  => FALSE,
                        'cacheDir' => '',
                        'charset'  => 'utf8',
                        'DBCollat' => 'utf8_general_ci',
                        'swapPre'  => '',
                        'encrypt'  => FALSE,
                        'compress' => FALSE,
                        'strictOn' => FALSE
                ],
                [
                        'hostname' => 'localhost2',
                        'username' => '',
                        'password' => '',
                        'database' => '',
                        'DBDriver' => 'MySQLi',
                        'DBPrefix' => '',
                        'pConnect' => TRUE,
                        'DBDebug'  => TRUE,
                        'cacheOn'  => FALSE,
                        'cacheDir' => '',
                        'charset'  => 'utf8',
                        'DBCollat' => 'utf8_general_ci',
                        'swapPre'  => '',
                        'encrypt'  => FALSE,
                        'compress' => FALSE,
                        'strictOn' => FALSE
                ]
        ];

public $defaultGroup = 'default';

public $default = [
 ...... ค่า key ต่างๆ
];

public $demo = [
 ...... ค่า key ต่างๆ
];

public $defaultGroup = 'demo';

#--------------------------------------------------------------------
# DATABASE
#--------------------------------------------------------------------

# database.default.hostname = localhost
# database.default.database = ci4
# database.default.username = root
# database.default.password = root
# database.default.DBDriver = MySQLi

# database.tests.hostname = localhost
# database.tests.database = ci4
# database.tests.username = root
# database.tests.password = root
# database.tests.DBDriver = MySQLi

 database.default.hostname = localhost
 database.default.database = dbci4
 database.default.username = root
 database.default.password = 
 database.default.DBDriver = MySQLi
 database.default.DBPrefix = mytable_ 

<?php namespace App\Controllers; 

use CodeIgniter\Controller; // เรียกใช้งาน Controller class

class Helloworld extends Controller 
{
    public function index() 
    {
			$db = \Config\Database::connect();
			// หรือ 
			// $db = db_connect();
    }
}

$db = db_connect('demo');
// หรือ
// $db = \Config\Database::connect('demo');

$db = db_connect('demo');
$db2 = db_connect('demo');
var_dump($db === $db2); // true

$db = db_connect('demo');
$db2 = db_connect('demo', false);
var_dump($db === $db2); // false

$db = \Config\Database::connect('demo');
$db2 = \Config\Database::connect('demo', false);
var_dump($db === $db2); // false

$db = db_connect('dbserver1');
$db2 = db_connect('dbserver2');

// จะขอไม่พูดถึงตัวอย่างรูปแบบ $db = \Config\Database::connect() 
// ให้จำไว้ว่า มีรูปแบบการกำหนดการใช้งานเหมือนกัน

$db = db_connect(); 
var_dump($db->database); // string(5) "dbci4" 
$db->setDatabase('db_ci');
var_dump($db->database); // string(5) "db_ci"

$db->setDatabase('db_ci');

$custom = [
			'DSN'      => '',
			'hostname' => 'localhost',
			'username' => '',
			'password' => '',
			'database' => '',
			'DBDriver' => 'MySQLi',
			'DBPrefix' => '',
			'pConnect' => false,
			'DBDebug'  => (ENVIRONMENT !== 'production'),
			'cacheOn'  => false,
			'cacheDir' => '',
			'charset'  => 'utf8',
			'DBCollat' => 'utf8_general_ci',
			'swapPre'  => '',
			'encrypt'  => false,
			'compress' => false,
			'strictOn' => false,
			'failover' => [],
			'port'     => 3306,
	];			
 $db = db_connect($custom); 

$db->reconnect();

$db->close();

$db->query('YOUR QUERY HERE');

<?php namespace App\Controllers; 

use CodeIgniter\Controller; // เรียกใช้งาน Controller class

class Helloworld extends Controller 
{
    public function index() 
    {	
			 $db = db_connect(); 
			$query = $db->query('SELECT * FROM news');
			var_dump($query); // return result object 
    }
}

if ($db->simpleQuery('YOUR QUERY'))
{
        echo "Success!";
}
else
{
        echo "Query failed!";
}

$query = $db->simpleQuery('SELECT * FROM news');
echo $query->num_rows;

$db = db_connect(); 
$table = $db->prefixTable('news');
$query = $db->query('SELECT * FROM '.$table);
$sql = $db->getLastQuery();
echo $sql;//  จะได้ค่าเป็น SELECT * FROM tbl_news

$db = db_connect(); 
$db->setPrefix('mytbl_');
$table = $db->prefixTable('news');
$query = $db->query('SELECT * FROM '.$table);
$sql = $db->getLastQuery();
echo $sql;//  จะได้ค่าเป็น SELECT * FROM mytbl_news

$db = db_connect(); 
$DBPrefix = $db->getPrefix();
echo $DBPrefix; // หากตั้งไว้ ค่าจะแสดงตามที่กำหนด แต่ถ้าไม่ได้กำหนดก็จะเป็นค่าว่าง

SELECT * FROM `news`

$db = db_connect(); 
$table = "news";
$table = $db->protectIdentifiers($table);
$query = $db->query('SELECT * FROM '.$table);
$sql = $db->getLastQuery();
echo $sql;//  จะได้ค่าเป็น SELECT * FROM `news`

$title = "Isn't it";
$hit = 5;
$sql = "INSERT INTO table (title,hit) VALUES(".$title.",".$hit.")";
echo $sql;
echo "<br>";
$sql = "INSERT INTO table (title,hit) VALUES(".$db->escape($title).",".$db->escape($hit).")";
echo $sql;

INSERT INTO table (title,hit) VALUES(Isn't it,5)
INSERT INTO table (title,hit) VALUES('Isn\'t it',5)

$db->escape($title) // จะเป็น String
$db->escape($hit) // จะเป็น int

$db->escapeString($title) // จะเป็น String
$db->escapeString($hit) // จะเป็น String

$title = "Isn't it";
$hit = 5;
$sql = "INSERT INTO table (title,hit) VALUES(".$title.",".$hit.")";
echo $sql;
echo "<br>";
$sql = "INSERT INTO table (title,hit) VALUES(".$db->escapeString($title).",".$db->escapeString($hit).")";
echo $sql;

INSERT INTO table (title,hit) VALUES(Isn't it,5)
INSERT INTO table (title,hit) VALUES(Isn\'t it,5)

$search = '20% raise';
$sql = "
	SELECT id FROM table WHERE column 
	LIKE '%" .$search."%'
";
echo $sql;
echo "<br>";
$sql = "
	SELECT id FROM table WHERE column 
	LIKE '%" .$db->escapeLikeString($search)."%' ESCAPE '!'
";
echo $sql;

SELECT id FROM table WHERE column LIKE '%20% raise%'
SELECT id FROM table WHERE column LIKE '%20!% raise%' ESCAPE '!'

$db = db_connect(); 
$sql = "SELECT * FROM news WHERE title = ? OR body = ? ";
$db->query($sql, ['say','it']);
$query = $db->getLastQuery();
echo (string)$query;

SELECT * FROM news WHERE title = 'say' OR body = 'it'

$sql = "SELECT * FROM some_table WHERE id IN ? AND status = ? AND author = ?";
$db->query($sql, [[3, 6], 'live', 'Rick']);

SELECT * FROM some_table WHERE id IN (3,6) AND status = 'live' AND author = 'Rick'

$db = db_connect(); 
$sql = "SELECT * FROM news WHERE title = :data1: OR body = :data2: ";
$db->query($sql, [
	"data2" => "it",
	"data1" => "say"
]);
$query = $db->getLastQuery();
echo (string)$query;

$db = db_connect(); 
if ( ! $db->simpleQuery('SELECT * FROM `newsss`'))
{
		$error = $db->error(); // Has keys 'code' and 'message'
		var_dump($error);
}

array(2) { ["code"]=> int(1146) ["message"]=> string(34) "Table 'dbci4.newsss' doesn't exist" }

$db = db_connect(); 
$pQuery = $db->prepare(function($db)
{
	return $db->table('news')
			   ->insert([
					'title'    => 'A',
					'slug'   => 'B',
					'body' => 'C'
			   ]);
});
var_dump($pQuery);

<?php namespace App\Controllers; 

use CodeIgniter\Controller; // เรียกใช้งาน Controller class
use CodeIgniter\Database\Query;

class Helloworld extends Controller 
{
    public function index() 
    {	
			$db = db_connect(); 
			$pQuery = $db->prepare(function($db)
			{
				$sql = "INSERT INTO news (title, slug, body) VALUES (?, ?, ?)";

				return (new Query($db))->setQuery($sql);
			});			
			var_dump($pQuery);
    }
}

<?php namespace App\Controllers; 

use CodeIgniter\Controller; // เรียกใช้งาน Controller class
use CodeIgniter\Database\Query;

class Helloworld extends Controller 
{
    public function index() 
    {	
			$db = db_connect(); 
			// Stage 1: prepare
			$pQuery = $db->prepare(function($db)
			{
				$sql = "INSERT INTO news (title, slug, body) VALUES (?, ?, ?)";

				return (new Query($db))->setQuery($sql);
			});		
			
			// Stage 2: bind and execute
			// Collect the Data
			$title    = 'Prepare title test';
			$slug   = 'Prepare-title-test';
			$body = 'Body Here';

			// Run the Query
			$results = $pQuery->execute($title, $slug, $body);			

    }
}

$query = $db->getLastQuery();
echo (string)$query;