PHP mysql-database-class

• https://packagist.org/packages/joshcam/mysqli-database-class

Installation

composer require joshcam/mysqli-database-class:dev-master

$db = new MysqliDb ('host', 'username', 'password', 'databaseName');
// $db->setPrefix ('my_');

# load Date
$opt = [
    "fieldChar" => ';',     // 데이터 구분
    "lineChar" => '\r\n',   // 라인구분
    "linesToIgnore" => 1,   // 데이터 시작 라인
    "loadDataLocal" => true,    // localdata 사용여부
];
$db->loadData("users", '/home/username/file.csv', $opt);

# load XML Data
$opt = [
    "linesToIgnore" => 0,   // 데이터 시작 라인
    "rowTag" => "<user>" // The tag which marks the beginning of an entry 항목 시작을 나타내는 태그
];
$db->loadXML("users", '/home/username/file.xml', $opt);

# Insert
$u = [
    "active" => true,
    "username" => "username",
    "nickname" => "nicknam",
    "password" => hash("sha256", "password"."salt"),
    // 'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")),
	// 'password' => SHA1('secretpassword+salt')
    "createdAt" => $db->now(),
    // createdAt => NOW()
    "updatedAt" => NULL,
	'expires' => $db->now('+1Y')
	// expires => NOW() + interval 1 year
	// Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
];
$id = $db->insert("users", $u);
echo $id;
// 1

# update
$u = [
    "active" => false,
    "useanme" => "username_false",
    "password" => "",
    "updatedAt" => $db->now()
];
if( $db->where('id', 1)->update("users", $u) )
{
    echo $db->count()." row updated";
}else{
    echo "update faild : ".$db->getLastError();
    echo $db->getLastQuery();
}

# Select
$u = $db->get("users");

# where
$u = $db->where('isLogin', 1)->get("users");

# select one
$u = $db->getOne("users"); // 가장 최근 결과 하나를 가져온다.
echo $u['id'];

# select Object
$u = $db->ObjectBuilder()->getOne("users");
echo $u->id;

$json = $db->JsonBuilder()->getOne("users");
echo $json;

# select raw Query
$users = $db->rawQuery('SELECT * from users where id >= ?', [10]);
print_r( $users );

//$users = $db->rawQueryOne('SELECT * from users where id >= ?', [10])//$users = $db->rawQueryOne('SELECT * from users where id >= ? limit 10');
//$password = $db->rawQueryValue('SELECT * from users where id >= ? limit 1', [10]);

# select Pagination
$page = 1;
$db->pageLimit = 10;
$users = $db->paginate('users', $page);

# where
$users = $db->where("username =! nickname")->get("users");
$users = $db->where("DATE(lastLogin) =! DATE(createAt)")->get("users");
$users = $db->where("lastLogin = createdAt")->get("users");
$users = $db->where("id", 50, ">=")->get("users");
$users = $db->where("id", [4, 20], "BETWEEN")->get("users");
$users = $db->where("id", [4, 20], "IN")->get("users");
$users = $db->where("id", ["IN" => [4, 20]])->get("users");
$users = $db->where("id", [4, 20])->orWhere("active", true)->get("users");
$users = $db->where("username", "%user%", "like")->get("users");
$users = $db->where("updateAt", NULL, "IS NOT")->get("users");

# total count
$offset = 10;
d$count = 15;
$users = $db->withTotalCount()->get("users", [$offset, $count]);
echo "Get {$count} from {$db->totalCount}";

# Order by
$db->orderBy("id", "asc");
$db->orderBy("id", "desc");
$db->orderBy("RAND()");
$db->orderBy('userGroup', 'ASC', array('superuser', 'admin', 'users'));
// ORDER BY FIELD (userGroup, 'superuser', 'admin', 'users') ASC

# setPrefix Select
$db->setPrefix ("t_");
$users = $db->orderBy ("users.id","asc")->get("users");
// 실패 : SELECT * FROM t_users ORDER BY users.id ASC;
$users = $db->orderBy ("`users`.id","asc")->get("users");
// 성공 : SELECT * FROM t_users ORDER BY t_users.id ASC;

# gropu By
$users = $db->groupBy('name')->get("users");

# Join
$products = $db->join("users u", "p.tenantId=u.tenantId", "LEFT")
            ->joinWhere("users u", "u.tenantId", 5)
            ->get("products p", NULL, "u.username, p.productname");
// SELECT u.name, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID AND u.tenantID = 5)

# Delete
if( $db->where('id', 1)->delete('users') )
{
    echo "success deleted";
}

그 외 제법 많은 예제가 있은니, 한 번 둘러보는것도 좋을 듯 하다.