Filter Your Critters
Find exactly what you're looking for.
query("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'adjectives'")->fetch(); if (!$tableExists) { $pdo->exec('CREATE TABLE adjectives ( id INTEGER PRIMARY KEY AUTOINCREMENT, value TEXT NOT NULL )'); $stmt = $pdo->prepare('INSERT INTO adjectives (value) VALUES (:value)'); foreach (['black', 'blue', 'brown', 'crochet', 'cute', 'fluffy', 'fuzzy', 'glittery', 'glowing neon', 'gray', 'green', 'lime green', 'magenta', 'orange', 'pastel', 'pink', 'purple', 'rainbow', 'red', 'sleepy', 'soft', 'sparkly', 'stone', 'teal', 'white', 'wooden', 'yellow'] as $adjective) { $stmt->execute(['value' => $adjective]); } } $tableExists = $pdo->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'nouns'")->fetch(); if (!$tableExists) { $pdo->exec('CREATE TABLE nouns ( id INTEGER PRIMARY KEY AUTOINCREMENT, value TEXT NOT NULL )'); $stmt = $pdo->prepare('INSERT INTO nouns (value) VALUES (:value)'); foreach (['beaver', 'bird', 'bunny rabbit', 'cat', 'chipmunk', 'cow', 'elephant', 'fox', 'frog', 'hamster', 'horse', 'kangaroo', 'koala', 'lemur', 'lion', 'monkey', 'moose', 'mouse', 'panda', 'penguin', 'pig', 'pika', 'puppy', 'quokka', 'raccoon', 'rat', 'sea otter', 'sheep', 'skunk', 'sloth', 'squirrel', 'teddy bear', 'tiger', 'turtle', 'wombat'] as $noun) { $stmt->execute(['value' => $noun]); } } $tableExists = $pdo->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'verbs'")->fetch(); if (!$tableExists) { $pdo->exec('CREATE TABLE verbs ( id INTEGER PRIMARY KEY AUTOINCREMENT, value TEXT NOT NULL )'); $stmt = $pdo->prepare('INSERT INTO verbs (value) VALUES (:value)'); foreach (['runs', 'jumps', 'waves hello', 'snuggles their blanket', 'swims', 'takes a bubble bath', 'wears a chef hat and stirs a baking bowl', 'hula hoops', 'builds with blocks', 'flies a kite', 'skateboards', 'rides their scooter', 'plays chess', 'writes with a pen and pad', 'plays guitar', 'plays the drums', 'plays the piano', 'roller skates', 'plays the saxophone', 'plays basketball', 'plays baseball', 'plays hockey', 'plays soccer', 'reads a book', 'wears pajamas', 'sleeps in bed', 'sings into a microphone', 'jumps rope', 'plays with a yo-yo', 'waters a plant with a watering can', 'sits by a campfire', 'naps in a hammock', 'plays golf', 'plays tennis', 'bakes cookies', 'does yoga'] as $verb) { $stmt->execute(['value' => $verb]); } } $tableExists = $pdo->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'locations'")->fetch(); if (!$tableExists) { $pdo->exec('CREATE TABLE locations ( id INTEGER PRIMARY KEY AUTOINCREMENT, value TEXT NOT NULL )'); $stmt = $pdo->prepare('INSERT INTO locations (value) VALUES (:value)'); foreach (['at the beach', 'on a sunny island', 'in the backyard', 'inside a snow globe', 'inside a Christmas ornament', 'safely inside a hot air balloon up in the sky', 'on the moon', 'in front of a gnomes house', 'in a majestic waterfall', 'on a trampoline', 'inside an igloo', 'on a rainbow', 'underwater inside an aquarium', 'at the pumpkin patch', 'at the north pole', 'in a candy land', 'on a farm', 'in the frontyard', 'in a big grassy field', 'on top of a cloud', 'on a bridge', 'in a forest', 'at the top of a mountain', 'in a boat on a river', 'at the mall', 'on a baseball field', 'on a soccer field', 'on a basketball court', 'on a hockey rink', 'in a swimming pool', 'in a hot tub', 'in the kitchen'] as $location) { $stmt->execute(['value' => $location]); } } $isLoggedIn = isset($_SESSION['username']) && isset($_SESSION['user_id']); if ($isLoggedIn && $_SERVER['REQUEST_METHOD'] === 'POST') { $action = $_POST['action'] ?? ''; $postedCritterId = $_POST['critter_id'] ?? ''; if ( csrf_verify($_POST['csrf_token'] ?? null) && in_array($action, ['toggle_favorite', 'toggle_archived'], true) && ctype_digit((string) $postedCritterId) ) { $stmt = $pdo->prepare('SELECT author_id, isFavorite, isArchived FROM critters WHERE id = :id'); $stmt->execute(['id' => $postedCritterId]); $target = $stmt->fetch(PDO::FETCH_ASSOC); if ($target && (int) $target['author_id'] === (int) $_SESSION['user_id']) { if ($action === 'toggle_favorite') { $newValue = $target['isFavorite'] ? 0 : 1; $update = $pdo->prepare('UPDATE critters SET isFavorite = :value WHERE id = :id'); } else { $newValue = $target['isArchived'] ? 0 : 1; $update = $pdo->prepare('UPDATE critters SET isArchived = :value WHERE id = :id'); } $update->execute(['value' => $newValue, 'id' => $postedCritterId]); } } $redirectQuery = $_SERVER['QUERY_STRING'] ?? ''; header('Location: /' . ($redirectQuery !== '' ? '?' . $redirectQuery : '')); exit; } function renderOptions(array $rows, $selectedId): void { foreach ($rows as $row) { $isSelected = (string) $selectedId === (string) $row['id']; echo ''; } } function buildCritterUrl(array $overrides, array $filters, string $statusFilter, string $search, string $sort, int $page): string { $params = []; foreach ($filters as $key => $value) { if ($value !== '') { $params[$key] = $value; } } if ($statusFilter !== 'any') { $params['status'] = $statusFilter; } if ($search !== '') { $params['q'] = $search; } if ($sort !== 'newest') { $params['sort'] = $sort; } $params['page'] = $page; foreach ($overrides as $key => $value) { if ($value === null) { unset($params[$key]); } else { $params[$key] = $value; } } return '?' . http_build_query($params); } $myCritters = []; $adjectives = []; $nouns = []; $verbs = []; $locations = []; $filters = [ 'filter_adjective' => '', 'filter_noun' => '', 'filter_verb' => '', 'filter_location' => '', ]; $statusFilter = 'any'; $search = ''; $sort = 'newest'; $perPage = 8; $page = 1; $totalPages = 1; $totalCrittersCount = 0; $favoritesCount = 0; $archivedCount = 0; if ($isLoggedIn) { $adjectives = $pdo->query('SELECT id, value FROM adjectives ORDER BY value')->fetchAll(PDO::FETCH_ASSOC); $nouns = $pdo->query('SELECT id, value FROM nouns ORDER BY value')->fetchAll(PDO::FETCH_ASSOC); $verbs = $pdo->query('SELECT id, value FROM verbs ORDER BY value')->fetchAll(PDO::FETCH_ASSOC); $locations = $pdo->query('SELECT id, value FROM locations ORDER BY value')->fetchAll(PDO::FETCH_ASSOC); $filterFields = [ 'filter_adjective' => ['column' => 'adjective_id', 'table' => 'adjectives'], 'filter_noun' => ['column' => 'noun_id', 'table' => 'nouns'], 'filter_verb' => ['column' => 'verb_id', 'table' => 'verbs'], 'filter_location' => ['column' => 'location_id', 'table' => 'locations'], ]; foreach ($filterFields as $field => $info) { $value = $_GET[$field] ?? ''; if ($value !== '' && ctype_digit((string) $value) && rowExistsById($pdo, $info['table'], $value)) { $filters[$field] = (int) $value; } } $allowedStatuses = ['any', 'favorited', 'archived']; $requestedStatus = $_GET['status'] ?? 'any'; if (in_array($requestedStatus, $allowedStatuses, true)) { $statusFilter = $requestedStatus; } $search = trim((string) ($_GET['q'] ?? '')); if (strlen($search) > 100) { $search = substr($search, 0, 100); } $requestedSort = $_GET['sort'] ?? 'newest'; if (in_array($requestedSort, ['newest', 'oldest'], true)) { $sort = $requestedSort; } $requestedPage = $_GET['page'] ?? '1'; if (ctype_digit((string) $requestedPage) && (int) $requestedPage >= 1) { $page = (int) $requestedPage; } $crittersTableExists = $pdo->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'critters'")->fetch(); if ($crittersTableExists) { $countAllStmt = $pdo->prepare('SELECT COUNT(*) FROM critters WHERE author_id = :author_id'); $countAllStmt->execute(['author_id' => $_SESSION['user_id']]); $totalCrittersCount = (int) $countAllStmt->fetchColumn(); $countFavStmt = $pdo->prepare('SELECT COUNT(*) FROM critters WHERE author_id = :author_id AND isFavorite = 1'); $countFavStmt->execute(['author_id' => $_SESSION['user_id']]); $favoritesCount = (int) $countFavStmt->fetchColumn(); $countArcStmt = $pdo->prepare('SELECT COUNT(*) FROM critters WHERE author_id = :author_id AND isArchived = 1'); $countArcStmt->execute(['author_id' => $_SESSION['user_id']]); $archivedCount = (int) $countArcStmt->fetchColumn(); $whereConditions = ['c.author_id = :author_id']; $queryParams = ['author_id' => $_SESSION['user_id']]; foreach ($filters as $field => $value) { if ($value !== '') { $column = $filterFields[$field]['column']; $whereConditions[] = "c.{$column} = :{$field}"; $queryParams[$field] = $value; } } if ($statusFilter === 'favorited') { $whereConditions[] = 'c.isFavorite = 1'; } elseif ($statusFilter === 'archived') { $whereConditions[] = 'c.isArchived = 1'; } if ($search !== '') { $escapedSearch = addcslashes($search, '%_\\'); $whereConditions[] = "(a.value LIKE :search ESCAPE '\\' OR n.value LIKE :search ESCAPE '\\' OR v.value LIKE :search ESCAPE '\\' OR l.value LIKE :search ESCAPE '\\')"; $queryParams['search'] = '%' . $escapedSearch . '%'; } $whereSql = implode(' AND ', $whereConditions); $countStmt = $pdo->prepare(" SELECT COUNT(*) FROM critters c JOIN adjectives a ON a.id = c.adjective_id JOIN nouns n ON n.id = c.noun_id JOIN verbs v ON v.id = c.verb_id JOIN locations l ON l.id = c.location_id WHERE {$whereSql} "); $countStmt->execute($queryParams); $totalCritters = (int) $countStmt->fetchColumn(); $totalPages = max(1, (int) ceil($totalCritters / $perPage)); if ($page > $totalPages) { $page = $totalPages; } $offset = ($page - 1) * $perPage; $orderSql = $sort === 'oldest' ? 'c.id ASC' : 'c.id DESC'; $sql = " SELECT c.id AS id, c.isFavorite AS isFavorite, c.isArchived AS isArchived, a.value AS adjective, n.value AS noun, v.value AS verb, l.value AS location, c.image AS image FROM critters c JOIN adjectives a ON a.id = c.adjective_id JOIN nouns n ON n.id = c.noun_id JOIN verbs v ON v.id = c.verb_id JOIN locations l ON l.id = c.location_id WHERE {$whereSql} ORDER BY {$orderSql} LIMIT {$perPage} OFFSET {$offset} "; $stmt = $pdo->prepare($sql); $stmt->execute($queryParams); $myCritters = $stmt->fetchAll(PDO::FETCH_ASSOC); } } $pageTitle = 'Welcome'; ?>
Find exactly what you're looking for.