From 0fa1dcad656bd664a009561d81a1b3b53363482f Mon Sep 17 00:00:00 2001 From: ilotterytea Date: Thu, 15 May 2025 19:00:27 +0500 Subject: upd: optimized code in emotesets.php --- public/emotesets.php | 232 ++++++++++++++++----------------------------------- src/emote.php | 42 ++++++++++ 2 files changed, 116 insertions(+), 158 deletions(-) diff --git a/public/emotesets.php b/public/emotesets.php index 33f8709..7044091 100644 --- a/public/emotesets.php +++ b/public/emotesets.php @@ -4,169 +4,92 @@ include_once "../src/config.php"; include_once "../src/accounts.php"; include_once "../src/partials.php"; include_once "../src/alert.php"; +include_once "../src/emote.php"; + authorize_user(); $id = $_GET["id"] ?? ""; -$alias_id = $_GET["alias_id"] ?? ""; $db = new PDO(DB_URL, DB_USER, DB_PASS); -$emote_sets = null; +// searching requested emoteset $emote_set = null; -$page = max(1, intval($_GET["p"] ?? "0")); -$total_emotesets = 1; -$total_pages = 1; - +// global emoteset if ($id == "global") { - $stmt = $db->prepare("SELECT * FROM emote_sets WHERE is_global = true"); - $stmt->execute(); - - if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { - $emote_set = $row; - - $stmt = $db->prepare("SELECT - e.*, - CASE - WHEN esc.code IS NOT NULL THEN esc.code - ELSE e.code - END AS code, - CASE - WHEN esc.code IS NOT NULL THEN e.code - ELSE NULL - END AS original_code, - CASE WHEN up.private_profile = FALSE OR up.id = ? THEN e.uploaded_by ELSE NULL END AS uploaded_by - FROM emotes e - JOIN user_preferences up ON up.id = e.uploaded_by - JOIN emote_set_contents esc ON esc.emote_id = e.id - WHERE esc.emote_set_id = ?"); - $stmt->execute([$_SESSION["user_id"] ?? "", $emote_set["id"]]); - - $emote_set["emotes"] = $stmt->fetchAll(PDO::FETCH_ASSOC); + $rows = $db->query("SELECT * FROM emote_sets WHERE is_global = TRUE LIMIT 1", PDO::FETCH_ASSOC); - foreach ($emote_set["emotes"] as &$e) { - if ($uploader_id = $e["uploaded_by"]) { - $stmt = $db->prepare("SELECT id, username FROM users WHERE id = ?"); - $stmt->execute([$uploader_id]); - $e["uploaded_by"] = $stmt->fetch(PDO::FETCH_ASSOC); - } - } - } -} else if (empty($id) && intval($alias_id) <= 0) { - if (!EMOTESET_PUBLIC_LIST) { - generate_alert("/404.php", "The public list of emotesets is disabled", 403); + if ($rows->rowCount()) { + $emote_set = $rows->fetch(); + } else { + generate_alert("/404.php", "Global emoteset is not found", 404); exit; } +} +// featured emoteset +else if ($id == "featured") { + $rows = $db->query("SELECT * FROM emote_sets WHERE is_featured = TRUE LIMIT 1", PDO::FETCH_ASSOC); - $limit = 20; - $offset = ($page - 1) * $limit; - - $stmt = $db->prepare("SELECT * FROM emote_sets LIMIT ? OFFSET ?"); - $stmt->bindParam(1, $limit, PDO::PARAM_INT); - $stmt->bindParam(2, $offset, PDO::PARAM_INT); - $stmt->execute(); - - $emote_sets = $stmt->fetchAll(PDO::FETCH_ASSOC); - - foreach ($emote_sets as &$e) { - $stmt = $db->prepare("SELECT e.*, - CASE - WHEN esc.code IS NOT NULL THEN esc.code - ELSE e.code - END AS code, - CASE - WHEN esc.code IS NOT NULL THEN e.code - ELSE NULL - END AS original_code, - CASE WHEN up.private_profile = FALSE OR up.id = ? THEN e.uploaded_by ELSE NULL END AS uploaded_by - FROM emotes e - JOIN user_preferences up ON up.id = e.uploaded_by - JOIN emote_set_contents esc ON esc.emote_set_id = ? - WHERE e.id = esc.emote_id"); - $stmt->execute([$_SESSION["user_id"] ?? "", $e["id"]]); - - $e["emotes"] = $stmt->fetchAll(PDO::FETCH_ASSOC); - foreach ($e["emotes"] as &$em) { - if ($em["uploaded_by"]) { - $stmt = $db->prepare("SELECT id, username FROM users WHERE id = ?"); - $stmt->execute([$em["uploaded_by"]]); - $em["uploaded_by"] = $stmt->fetch(PDO::FETCH_ASSOC); - } - } + if ($rows->rowCount()) { + $emote_set = $rows->fetch(); + } else { + generate_alert("/404.php", "Featured emoteset is not found", 404); + exit; } - $count_stmt = $db->prepare("SELECT COUNT(*) FROM emote_sets"); - $count_stmt->execute(); - $total_emotesets = intval($count_stmt->fetch()[0]); - $total_pages = ceil($total_emotesets / $limit); -} else if (intval($alias_id) > 0) { - $alias_id = intval($alias_id); +} +// connected emoteset +else if (isset($_GET["alias_id"])) { + $alias_id = $_GET["alias_id"]; + $platform = $_GET["platform"] ?? "twitch"; + $stmt = $db->prepare("SELECT es.* FROM emote_sets es - INNER JOIN connections co ON co.alias_id = ? - WHERE co.user_id = es.owner_id + INNER JOIN connections co ON co.alias_id = ? AND co.platform = ? + INNER JOIN acquired_emote_sets aes ON aes.user_id = co.user_id + WHERE aes.is_default = TRUE "); - $stmt->execute([$alias_id]); + $stmt->execute([$alias_id, $platform]); if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $emote_set = $row; - - $stmt = $db->prepare("SELECT e.*, - CASE - WHEN esc.code IS NOT NULL THEN esc.code - ELSE e.code - END AS code, - CASE - WHEN esc.code IS NOT NULL THEN e.code - ELSE NULL - END AS original_code, - CASE WHEN up.private_profile = FALSE OR up.id = ? THEN e.uploaded_by ELSE NULL END AS uploaded_by - FROM emotes e - JOIN user_preferences up ON up.id = e.uploaded_by - JOIN emote_set_contents esc ON esc.emote_set_id = ? - WHERE esc.emote_id = e.id"); - $stmt->execute([$_SESSION["user_id"] ?? "", $emote_set["id"]]); - - $emote_set["emotes"] = $stmt->fetchAll(PDO::FETCH_ASSOC); - - foreach ($emote_set["emotes"] as &$e) { - if ($e["uploaded_by"]) { - $stmt = $db->prepare("SELECT id, username FROM users WHERE id = ?"); - $stmt->execute([$e["uploaded_by"]]); - $e["uploaded_by"] = $stmt->fetch(PDO::FETCH_ASSOC); - } - } + } else { + generate_alert("/404.php", "Emoteset is not found for alias ID $alias_id ($platform)", 404); + exit; } -} else { - $stmt = $db->prepare("SELECT * FROM emote_sets WHERE id = ?"); +} +// specified emoteset +else if (!empty($id)) { + $stmt = $db->prepare("SELECT es.* FROM emote_sets es WHERE es.id = ?"); $stmt->execute([$id]); - if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { + if ($row = $stmt->fetch()) { $emote_set = $row; + } else { + generate_alert("/404.php", "Emoteset ID $id is not found", 404); + exit; + } +} - $stmt = $db->prepare("SELECT e.*, - CASE - WHEN esc.code IS NOT NULL THEN esc.code - ELSE e.code - END AS code, - CASE - WHEN esc.code IS NOT NULL THEN e.code - ELSE NULL - END AS original_code, - CASE WHEN up.private_profile = FALSE OR up.id = ? THEN e.uploaded_by ELSE NULL END AS uploaded_by - FROM emotes e - JOIN user_preferences up ON up.id = e.uploaded_by - JOIN emote_set_contents esc ON esc.emote_set_id = ? - WHERE esc.emote_id = e.id"); - $stmt->execute([$_SESSION["user_id"] ?? "", $emote_set["id"]]); +$user_id = $_SESSION["user_id"] ?? ""; +$emote_sets = null; - $emote_set["emotes"] = $stmt->fetchAll(PDO::FETCH_ASSOC); - foreach ($emote_set["emotes"] as &$e) { - $e["ext"] = "webp"; - if ($e["uploaded_by"]) { - $stmt = $db->prepare("SELECT id, username FROM users WHERE id = ?"); - $stmt->execute([$e["uploaded_by"]]); - $e["uploaded_by"] = $stmt->fetch(PDO::FETCH_ASSOC); - } - } +// fetching emotes +if ($emote_set) { + $emotes = fetch_all_emotes_from_emoteset($db, $emote_set["id"], $user_id, null); + $emote_set["emotes"] = $emotes; +} elseif (!EMOTESET_PUBLIC_LIST) { + generate_alert("/404.php", "The public list of emotesets is disabled", 403); + exit; +} else { + $emote_sets = []; + foreach ($db->query("SELECT es.* FROM emote_sets es", PDO::FETCH_ASSOC) as $row) { + $emote_set_row = $row; + $emote_set_row["emotes"] = fetch_all_emotes_from_emoteset( + $db, + $emote_set_row["id"], + $user_id, + 5 + ); + array_push($emote_sets, $emote_set_row); } } @@ -200,8 +123,12 @@ if (CLIENT_REQUIRES_JSON) { <?php - echo $emote_sets != null ? (count($emote_sets) . " emotesets") : ('"' . $emote_set["name"] . '" emoteset'); - echo ' - ' . INSTANCE_NAME; + $title = match ($emote_set == null) { + true => count($emote_sets) . ' emotesets', + false => 'Emoteset - ' . $emote_set["name"], + }; + + echo "$title - " . INSTANCE_NAME; ?> @@ -216,24 +143,22 @@ if (CLIENT_REQUIRES_JSON) {
- +
-
+
" class="box">
- ' . $set_row["name"] . '

'; - ?> +

'; + echo ''; } ?>
@@ -241,30 +166,21 @@ if (CLIENT_REQUIRES_JSON) {
'; - - if ($total_pages > 1) { - echo '' ?> -
- - '; - echo '' . $emote_row['; + echo '' . $emote_row['; echo '

' . $emote_row["code"] . '

'; echo '

' . ($emote_row["uploaded_by"] == null ? (ANONYMOUS_DEFAULT_NAME . "*") : $emote_row["uploaded_by"]["username"]) . '

'; echo ''; } } else { - echo 'No emotesets found...'; + echo 'Nothing found...'; } ?> -
+ diff --git a/src/emote.php b/src/emote.php index ce3b930..170a991 100644 --- a/src/emote.php +++ b/src/emote.php @@ -79,6 +79,48 @@ class Emote } } +function fetch_all_emotes_from_emoteset(PDO &$db, string $emote_set_id, string $user_id, int|null $limit = null): array +{ + // fetching emotes + $sql = "SELECT + e.id, e.created_at, + CASE + WHEN esc.code IS NOT NULL THEN esc.code + ELSE e.code + END AS code, + CASE + WHEN esc.code IS NOT NULL THEN e.code + ELSE NULL + END AS original_code, + CASE WHEN up.private_profile = FALSE OR up.id = ? THEN e.uploaded_by ELSE NULL END AS uploaded_by + FROM emotes e + LEFT JOIN user_preferences up ON up.id = e.uploaded_by + INNER JOIN emote_set_contents AS esc + ON esc.emote_set_id = ? + WHERE esc.emote_id = e.id"; + + if ($limit) { + $sql .= " LIMIT $limit"; + } + + $stmt = $db->prepare($sql); + $stmt->execute([$user_id, $emote_set_id]); + + $emotes = $stmt->fetchAll(PDO::FETCH_ASSOC); + + // fetching uploaders + foreach ($emotes as $e) { + if ($e["uploaded_by"]) { + $stmt = $db->prepare("SELECT id, username FROM users WHERE id = ?"); + $stmt->execute([$e["uploaded_by"]]); + + $e["uploaded_by"] = $stmt->fetch(PDO::FETCH_ASSOC); + } + } + + return $emotes; +} + function html_random_emote(PDO &$db) { $stmt = $db->prepare("SELECT id, code FROM emotes WHERE visibility = 1 ORDER BY RAND() LIMIT 1"); -- cgit v1.2.3