資料庫 - kouji6309/SingleMVC GitHub Wiki

SingleMVC ��ѤF�@�t�C��Ʈw�ާ@��k�A�u�ݭn�~�� Model ��O�Y�i�ϥΡC�]�t�F�H�U��k�G

�s�u db_connect
�ǳ� db_prepare
�j�w db_bind
�� db_select
��J db_insert
��s db_update
�� db_query
�}�l�� db_begin
�� db_commit
�_�� db_rollBack
�� db_debug

�t�~��Ʈw�]�w�b FrameworkConfig �� db �ܼƤ��G

��ƨӷ��W�� dsn
�b�� username
�K�X password
PDO �s�u�Ѽ� options

��קK SQL injection ��w��D�A�j�P��ĳ�ϥ� db_prepare �ǳƫ�A�A�j�w�ƭȰ��A�Ӥ��n��s��r�� SQL�C

��U�ӱN��㪺��Ʈw�ާ@�C

�]�w�P�s�u

�ϥθ�Ʈw�e�A��]�w��ѼơA�q�`�O�g�b config.php ��G

SingleMVC::$config->db = [
    'dsn' => 'mysql:host=localhost;dbname=default_db;charset=utf8mb4',
    'username' => 'root',
    'password' => '********',
    'options' => [
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4',
        PDO::ATTR_EMULATE_PREPARES => false,
    ],
];

�b��Ʈw��k�e�A��|��ˬd�O�_�w�s�u�A�Y�S��h�|��ճs�u�A�]��q�`��Φۦ�I�s�C

�b��p�|�ݭn�s�u�h��Ʈw�A�o�ɴN�ݭn�мg��Ӫ��s��k�A�N�ѼƶǤJ��O�� db_connect �öǦ^��G�G

class News extends Model {
    protected function db_connect($config = null) {
        return parent::db_connect([
            'dsn' => 'mysql:host=localhost.;dbname=second_db;charset=utf8mb4',
            'username' => 'root',
            'password' => '********',
            'options' => [
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4',
                PDO::ATTR_EMULATE_PREPARES => false,
            ],
        ]);
    }
}

�s�u��A�ج[�|�O��s�u��T�P PDO ��A��P�˳s�u��T��O��ƨϥΦP�@�ӳs�u�C

�ǳƻP�j�w

�ҿ׷ǳơA�@��O��N�ԭz��ܼơA��Ÿ�(�Ѽ�)�A�M��ǵ� SQL Server �ˬd�A��\��A�N�ƭȥt�~�ǵ� Server�A��@�Ӭd�ߡC

�ǳƮɡA�ϥ� :name ��ܨ�W�ѼơA�ϥ� ? ��ܰΦW�ѼơA��ذѼƤ��V�ΡC

// ��W�Ѽ�
$this->db_prepare('INSERT INTO `users` (`id`, `name`) VALUES (:id, :name);');

// �ΦW�Ѽ�
$this->db_prepare('INSERT INTO `users` (`id`, `name`) VALUES (?, ?);');

�Y�J�줣�w�ƶq��Ѽƻݭn�ǳơA�i�ΰj��s�� SQL�A��̵M�n�O��ϥ� db_prepare �ǳƪ��h�G

$sql = 'INSERT INTO `users` (`id`, `name`) VALUES ';
$sqls = [];
foreach ($data as $d) {
    $sqls[] = '(?, ?)';
}
$sql .= implode(', ', $sqls);
$this->db_prepare($sql);

�`�N�G��W�ѼƤ��i�H��ơA�Y�Q�b�j�餤�ϥΨ�W�ѼơA��n��}�C�ӰѼƦW�١A�Ҧp�ϥάy��C

��ǳƫ�A�N�i�H�}�l�j�w��ơC�ج[��ѤF�@�ӧֳt�j�w��覡 db_bind�A�i�H��A�@��j�w�h�ӼƭȡG

// ��W�Ѽ�
$this->db_bind([':id' => 1, ':name' => 'Kouji']);

// �ΦW�Ѽ�
$this->db_bind([1, 'Kouji']);

�P�W�A�]�|�J�줣�q�ƶq��Ѽƻݭn�j�w�A�u�ݭn�b�j�餤�N�ƭȦs��t�@�}�C��A��A��j�w�G

// �e��
$vals = [];
foreach ($data as $d) {
    $sqls[] = '(?, ?)';

    $vals[] = $d['id'];
    $vals[] = $d['name'];
}
// ����
$this->db_bind($vals);

�Y�ϥΨ�W�ѼơA�b $vals �[�J�ɻݫ��w��ޡA�ϰ}�C��c�p�e�@�d�Ҩ�W�ѼơC

��

��]�t�T�Ӥ�k�G�� db_select�B��J db_insert�P��s db_update�C�D�n�t��b��Ǧ^��Ƥ��P�C�@��ӻ��A��P��J�ϥΦU�۪��k�A��L�p��s�B�R��B�ק��ϥ� db_update�A�y�N�W�|��M��C

��

$this->db_prepare('SELECT * FROM `users` WHERE `name` = :name;');
$this->db_bind(':name', 'Kouji');
if ($user = $this->db_select()) {
    // ���I����
}

��J��ơA�Ǧ^�s�W��s��A�Y��J�h�C�h�Ǧ^�s�W��C��

$this->db_prepare('INSERT INTO `users` (`id`, `name`) VALUES (:id, :name);');
$this->db_bind([':id' => 1, ':name' => 'Kouji']);
if ($id = $this->db_insert()) {
    // ���I����
}

��s��ơA�Ǧ^��ʪ��C��

$this->db_prepare('UPDATE `users` SET `name` = :name WHERE `id` = :id;');
$this->db_bind([':id' => 1, ':name' => 'Tails']);
if ($count = $this->db_update()) {
    // ���I����
}

��

�o�O��`�̫�@��A��i�H�T�O�h�Ӭd�߳��৹��A�Y�䤤�@�ӨS��A�h�^�и�Ʈw��e��A�C

��]�t�T�Ӥ�k�G�}�l�� db_begin�B�� db_commit �M�_�� db_rollBack�A�b�}�l�ǳƫe�I�s db_begin�A�Ҧ� SQL ��榨�\��I�s db_commit�A�Y��楢�ѫh�I�s db_rollBack ��}�C

$this->db_begin();

$this->db_prepare('INSERT INTO `users` (`id`, `name`) VALUES (:id, :name);');
$this->db_bind([':id' => 1, ':name' => 'Kouji']);
if ($this->db_insert() === false) {
    $this->db_rollBack();
    return;
}

$this->db_prepare('UPDATE `users` SET `name` = :name WHERE `id` = :id;');
$this->db_bind([':id' => 1, ':name' => 'Tails']);
if ($this->db_insert() === false) {
    $this->db_rollBack();
    return;
}

$this->db_commit();

��Ʈw��Ш즹��A�i�H�ݨ�L�ج[��Ѫ��\�� cURL �M�K�X��A�Բӥ\��Ь��o��C

資料庫 - kouji6309/SingleMVC GitHub Wiki

�]�w�P�s�u

�ǳƻP�j�w

����

���

��

��