PHP Mysqli Helper
Install
packagist : https://packagist.org/packages/metalsm7/azlib-php
$ composer require metalsm7/azlib-php
AZData
AZSql
Database ์ฒ๋ฆฌ ํฌํผ (mysql ์ง์)
- AZData, AZList ๊ฐ์ฒด๋ก ๊ฒฐ๊ณผ ๋ฐ์ธ๋ฉ
- Prepared Statement ์ง์
- Stored Procedure ์ง์
๊ธฐ๋ณธ ์ฌ์ฉ๋ฒ
// ์ด๊ธฐํ
$sql = AZLib\AZSql::create($db); // $sql = new AZLib\AZSql($db);
// ๊ธฐ๋ณธ
$col = $sql->set_query('SELECT id FROM users LIMIT 1')->get(); // ๋จ์ผ ๊ฒฐ๊ณผ #1/2
$col = $sql->get('SELECT id FROM users LIMIT 1'); // ๋จ์ผ ๊ฒฐ๊ณผ #2/2
$row = $sql->get_data('SELECT id, name from users LIMIT 1'); // ๋จํ ๊ฒฐ๊ณผ
$rows = $sql->get_data('SELECT id, name from users LIMIT 3'); // ๋คํ ๊ฒฐ๊ณผ
// ๋์ ๋ฐ prepared statement ์ ์ฉ
$query = 'SELECT id, name FROM users WHERE type=@type AND region=@region LIMIT @offset, @length';
$rows = $sql
//->set_prepared(true) // prepared statement ์ ์ฉํ๋ ๊ฒฝ์ฐ true๋ก ์ง์ (๊ธฐ๋ณธ๊ฐ: false)
->set_query($query)
/*
->add_parameter('@type', 'student') // set_parameters ๋์ add_parameter ์ฌ์ฉ์ ๊ฒฝ์ฐ
->add_parameter('@region', 15)
->add_parameter('@offset', 0)
->add_parameter('@length', 10)
*/
->set_parameters(
AZLib\AZData::create()
->add('@type', 'student')
->add('@region', 15)
->add('@offset', 0)
->add('@length', 10)
)
->get_list();
// ๋ฐํ๊ฐ์ ๊ฐ์ง๋ stored procedure ์ ๊ฒฝ์ฐ
$list = $sql
//->set_prepared(true) // prepared statement ์ ์ฉํ๋ ๊ฒฝ์ฐ true๋ก ์ง์ (๊ธฐ๋ณธ๊ฐ: false)
->set_stored_procedure(true) // stored procedure ์ฌ์ฉ์ ๊ฒฝ์ฐ
->set_query('CALL proc_test(@arg1, @arg2, @out1, @out2)') // ์ธ์๊ฐ arg1, arg2 ์ ๋ฐํ๊ฐ out1, out2
->add_parameter('@arg1', 'input1') // ์ ๋ฌ๊ฐ ์
๋ ฅ
->add_parameter('@arg2', 'input2')
->add_return_parameter('@out1') // ๋ฐํ๊ฐ ์
๋ ฅ, ๋ฐํ๊ฐ์ผ๋ก ๋ฑ๋กํ์ง ์๋ ์๋ฃ๋ ์ ๋ฌ๋ฐ์ง ๋ชปํจ
->add_return_parameter('@out2')
->get_multi(); // ํ๋ก์์ ธ ๋ด ์ฟผ๋ฆฌ๋ค์ ๋ํ ๊ฒฐ๊ณผ๊ฐ ์ ์ฅ์ฉ, AZList[] ํ์ ๋ฐํ
echo "out1:".$sql->get_return_parameter('@out1').PHP_EOL; // @out1 ์ผ๋ก ์ง์ ๋ ๋ฐํ๊ฐ ํ์ธ
echo "out2:".$sql->get_return_parameter('@out2').PHP_EOL; // @out2 ์ผ๋ก ์ง์ ๋ ๋ฐํ๊ฐ ํ์ธ
๋จ์ํ ์ฒ๋ฆฌ ์ฌ์ฉ๋ฒ
// ์ด๊ธฐํ
$bql = AZLib\AZSql\Basic::create('users', $db); // $bql = new AZLib\AZSql\Basic('user', $db);
// insert
$bql
//->set_prepared(true) // prepared statement ์ ์ฉํ๋ ๊ฒฝ์ฐ true๋ก ์ง์ (๊ธฐ๋ณธ๊ฐ: false)
->set('id', 'userid1')
->set('name', 'username1')
->set('type', 'teacher')
->set('region', 15)
->do_insert(true); // true ์ฌ์ฉํ๋ ๊ฒฝ์ฐ id๊ฐ ๋ฐํ, ๊ทธ ์ธ์ ๊ฒฝ์ฐ ์ํฅ์ ๋ฐ๋ rows ๊ฐฏ์ ๋ฐํ
// set_prepared(false)์ธ ๊ฒฝ์ฐ -> INSERT INTO users (is, name, type, region) VALUES ('userid1', 'username1', 'teacher', 15)
// set_prepared(true)์ธ ๊ฒฝ์ฐ -> INSERT INTO users (is, name, type, region) VALUES (?, ?, ?, ?)
// update
$bql
//->set_prepared(true) // prepared statement ์ ์ฉํ๋ ๊ฒฝ์ฐ true๋ก ์ง์ (๊ธฐ๋ณธ๊ฐ: false)
->set('type', 'teacher')
->where('name', 'username1')
->where('region', 15)
->do_update(); // ์ํฅ์ ๋ฐ๋ rows ๊ฐฏ์ ๋ฐํ
// set_prepared(false)์ธ ๊ฒฝ์ฐ -> UPDATE users SET type='teacher' WHERE name='username1' AND region=15
// set_prepared(true)์ธ ๊ฒฝ์ฐ -> UPDATE users SET type=? WHERE name=? AND region=?
// delete
$bql
//->set_prepared(true) // prepared statement ์ ์ฉํ๋ ๊ฒฝ์ฐ true๋ก ์ง์ (๊ธฐ๋ณธ๊ฐ: false)
->where('name', 'username2')
->where('region', 11)
->do_delete(); // ์ํฅ์ ๋ฐ๋ rows ๊ฐฏ์ ๋ฐํ
// set_prepared(false)์ธ ๊ฒฝ์ฐ -> DELETE FROM users WHERE name='username2' AND region=11
// set_prepared(true)์ธ ๊ฒฝ์ฐ -> DELETE FROM users WHERE name=? AND region=?