Base Directory:
/home/ecedu/public_html/new/Models
db = DB::instance()->getConnection();
}
public function login(string $username, string $password): int
{
$sql = "SELECT user_id, password
FROM dbs_user
WHERE username = :username
LIMIT 1";
$stmt = $this->db->prepare($sql);
$stmt->execute([':username' => $username]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row) {
$user_id = (int) $row['user_id'];
$storedHash = $row['password'];
if (password_verify($password, $storedHash)) {
return $user_id;
}
}
return 0;
}
public function insertUser(string $name, string $username, string $password): string
{
if ($this->existsByUsername($username)) {
return 'username_exists';
}
$email = $username . '@user.com';
$sql = "INSERT INTO dbs_user (type_id, name, username, password, email)
VALUES (2, :name, :username, :password, :email)";
$stmt = $this->db->prepare($sql);
$ok = $stmt->execute([
':name' => $name,
':username' => $username,
':password' => password_hash($password, PASSWORD_DEFAULT),
':email' => $email,
]);
return $ok ? 'ok' : 'insert_failed';
}
public function updatetUser(int $id, string $name, string $username, ?string $password = null): string
{
if (!$this->existsById($id)) {
return 'err2';
}
if ($password === null || $password === '') {
$sql = "UPDATE dbs_user
SET name = :name,
username = :username
WHERE user_id = :id";
$stmt = $this->db->prepare($sql);
$ok = $stmt->execute([
':name' => $name,
':username' => $username,
':id' => $id,
]);
} else {
$sql = "UPDATE dbs_user
SET name = :name,
username = :username,
password = :password
WHERE user_id = :id";
$stmt = $this->db->prepare($sql);
$ok = $stmt->execute([
':name' => $name,
':username' => $username,
':password' => password_hash($password, PASSWORD_DEFAULT),
':id' => $id,
]);
}
return $ok ? 'ok' : 'err1';
}
public function deleteUser(int $id): string
{
if (!$this->existsById($id)) {
return 'err2';
}
$stmt = $this->db->prepare("DELETE FROM dbs_user WHERE user_id = :id");
$ok = $stmt->execute([':id' => $id]);
return $ok ? 'ok' : 'err1';
}
private function existsByUsername(string $username): bool
{
$stmt = $this->db->prepare(
"SELECT 1 FROM dbs_user WHERE username = :username LIMIT 1"
);
$stmt->execute([':username' => $username]);
return (bool) $stmt->fetchColumn();
}
private function existsById(int $id): bool
{
$stmt = $this->db->prepare(
"SELECT 1 FROM dbs_user WHERE user_id = :id LIMIT 1"
);
$stmt->execute([':id' => $id]);
return (bool) $stmt->fetchColumn();
}
public function getUserById($id)
{
try {
$sql = 'SELECT * FROM dbs_user WHERE user_id = :id LIMIT 1';
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':id', $id, \PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
return $rows;
} catch (\PDOException $e) {
error_log('News::getNewsById Error: ' . $e->getMessage());
return [];
}
}
public function listUsers()
{
$sql = "SELECT * FROM dbs_user ORDER BY user_id ASC ";
$stmt = $this->db->prepare($sql);
$stmt->execute();
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}
public function createNews(
string $title,
string $title1,
string $title2,
int $typeId,
string $newsDate,
string $link,
string $description,
string $description1,
string $description2,
string $ext,
int $userId
): int {
try {
$sql = "INSERT INTO dbs_news
(news_title, news_title1, news_title2, type_id, news_status,
news_date, news_link, news_description, news_description1, news_description2,
img_ext, user_id)
VALUES
(:title, :title1, :title2, :type_id, 0,
:news_date, :link, :description, :description1, :description2,
:ext, :user_id)";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':title', $title);
$stmt->bindValue(':title1', $title1);
$stmt->bindValue(':title2', $title2);
$stmt->bindValue(':type_id', $typeId, \PDO::PARAM_INT);
$stmt->bindValue(':news_date', $newsDate);
$stmt->bindValue(':link', $link);
$stmt->bindValue(':description', $description);
$stmt->bindValue(':description1', $description1);
$stmt->bindValue(':description2', $description2);
$stmt->bindValue(':ext', $ext);
$stmt->bindValue(':user_id', $userId, \PDO::PARAM_INT);
$stmt->execute();
return (int)$this->db->lastInsertId();
} catch (\PDOException $e) {
error_log("createNews Error: ".$e->getMessage());
return 0;
}
}
public function updateNews(
int $newsId,
string $title,
string $title1,
string $title2,
string $newsDate,
string $link,
string $description,
string $description1,
string $description2,
?string $newExt = null
): bool {
try {
if ($newExt) {
$sql = "UPDATE dbs_news
SET news_title=:title,
news_title1=:title1,
news_title2=:title2,
news_date=:newsDate,
news_link=:link,
news_description=:descr,
news_description1=:descr1,
news_description2=:descr2,
img_ext=:ext
WHERE news_id=:id";
} else {
$sql = "UPDATE dbs_news
SET news_title=:title,
news_title1=:title1,
news_title2=:title2,
news_date=:newsDate,
news_link=:link,
news_description=:descr,
news_description1=:descr1,
news_description2=:descr2
WHERE news_id=:id";
}
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':title', $title);
$stmt->bindValue(':title1', $title1);
$stmt->bindValue(':title2', $title2);
$stmt->bindValue(':newsDate',$newsDate);
$stmt->bindValue(':link', $link);
$stmt->bindValue(':descr', $description);
$stmt->bindValue(':descr1', $description1);
$stmt->bindValue(':descr2', $description2);
$stmt->bindValue(':id', $newsId, \PDO::PARAM_INT);
if ($newExt) {
$stmt->bindValue(':ext', $newExt);
}
return $stmt->execute();
} catch (\PDOException $e) {
error_log("updateNews Error: ".$e->getMessage());
return false;
}
}
public function deleteNews(int $newsId): bool
{
try {
$sql = "DELETE FROM dbs_news WHERE news_id=:id";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':id', $newsId, \PDO::PARAM_INT);
return $stmt->execute();
} catch (\PDOException $e) {
error_log("deleteNews Error: ".$e->getMessage());
return false;
}
}
public function setNewsStatus(int $newsId, int $status): bool
{
try {
$sql = "UPDATE dbs_news SET news_status=:status WHERE news_id=:id";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':status', $status, \PDO::PARAM_INT);
$stmt->bindValue(':id', $newsId, \PDO::PARAM_INT);
return $stmt->execute();
} catch (\PDOException $e) {
error_log("setNewsStatus Error: ".$e->getMessage());
return false;
}
}
public function getNewsById(int $id): ?array
{
try {
$sql = 'SELECT * FROM dbs_news WHERE news_id = :id LIMIT 1';
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':id', $id, \PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);
return $rows;
} catch (\PDOException $e) {
error_log('News::getNewsById Error: ' . $e->getMessage());
return [];
}
}
public function getSingleNewsById(int $id): ?array
{
try {
$sql = 'SELECT * FROM dbs_news WHERE news_id = :id LIMIT 1';
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':id', $id, \PDO::PARAM_INT);
$stmt->execute();
$row = $stmt->fetch(\PDO::FETCH_ASSOC);
return $row ?: null;
} catch (\PDOException $e) {
error_log('News::getSingleNewsById Error: ' . $e->getMessage());
return null;
}
}
public function getNewsByType($typeId, $limit = 5)
{
$sql = "SELECT * FROM dbs_news
WHERE type_id = :typeId
ORDER BY news_date DESC
LIMIT :limit";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':typeId', (int)$typeId, \PDO::PARAM_INT);
$stmt->bindValue(':limit', (int)$limit, \PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}
public function getLatestNews($limit = 9)
{
$sql = "SELECT *
FROM dbs_news
WHERE type_id = 1
ORDER BY news_date DESC
LIMIT :limit";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':limit', (int)$limit, \PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}
public function getSliderNews(int $limit = 4)
{
$sql = "SELECT * FROM dbs_news WHERE type_id = 1 ORDER BY news_id DESC LIMIT :limit";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}
public function listBasic()
{
$sql = "SELECT * FROM dbs_basic ";
$stmt = $this->db->prepare($sql);
$stmt->execute();
return $stmt->fetchAll();
}
public function listImages()
{
$sql = "SELECT * FROM dbs_image ";
$stmt = $this->db->prepare($sql);
$stmt->execute();
return $stmt->fetchAll();
}
public function listAds()
{
$sql = "SELECT * FROM dbs_ads ";
$stmt = $this->db->prepare($sql);
$stmt->execute();
return $stmt->fetchAll();
}
public function getSocialLinks()
{
$sql = "SELECT * FROM dbs_basic WHERE type_id > 10 AND type_id < 15";
$stmt = $this->db->prepare($sql);
$stmt->execute();
return $stmt->fetchAll();
}
public function getDetails($id)
{
$sql = "SELECT * FROM dbs_basic WHERE type_id = :id";
$stmt = $this->db->prepare($sql);
$stmt->execute([':id' => $id], (int)$typeId, \PDO::PARAM_INT);
return $stmt->fetch();
}
public function increaseViewCount(int $news_id)
{
$sql = "INSERT INTO dbs_view (news_id, num_view)
VALUES (:news_id, 1)
ON DUPLICATE KEY UPDATE num_view = num_view + 1";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':news_id', $news_id, PDO::PARAM_INT);
$stmt->execute();
}
public function getViewCount(int $limit = 5)
{
$sql = "SELECT
n.*,
IFNULL(v.num_view, 0) AS num_view
FROM
dbs_news n
LEFT JOIN
dbs_view v ON n.news_id = v.news_id
WHERE
n.news_status = 0 AND num_view>0
ORDER BY
num_view DESC
LIMIT :limit";
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(\PDO::FETCH_ASSOC);
}
}