Own project · Stop 8 of 10

Regional event database

A website for browsing events, venues and types, with search and filters, written in PHP on a MySQL database.

Goal

Build a database-backed website where events can be found by category, type, district and audience.

Implementation

The start page has two tabs ("Top events" and "Where to go at the weekend") and a search with filters for category, type, district and audience.
Lists of events, venues, types, categories and audiences, and a detail page with the venue and the price.
The data lives in a MySQL database with about 20 tables. The queries, with joins across several tables, are written in PHP with mysqli.
I started it as a Next.js project and later rebuilt it in PHP.
The pictures show the app with sample data: made-up events and pictures I drew myself instead of the photos.

Code

Search with several filters

search.php

if ($hasFilter) {
    $sql = "SELECT
            v.veranstaltung_id,
            v.titel,
            v.bild,
            o.name AS location_name,
            MIN(t.startzeit) as startzeit,
            MIN(t.ausverkauft) as ausverkauft,
            MIN(t.abgesagt) as abgesagt,
            p.beschreibung AS preis_info
            FROM veranstaltung v
            LEFT JOIN veranstaltungsort o ON v.veranstaltungsort_id = o.veranstaltungsort_id
            LEFT JOIN stadtteil s ON o.stadtteil_id = s.stadtteil_id
            LEFT JOIN veranstaltungskategorie1 k ON v.veranstaltungskategorie1_id = k.veranstaltungskategorie1_id
            LEFT JOIN veranstaltungsart1 a ON v.veranstaltungsart1_id = a.veranstaltungsart1_id
            LEFT JOIN veranstaltung_zielgruppe vz ON v.veranstaltung_id = vz.veranstaltung_id
            LEFT JOIN zielgruppe zg ON vz.zielgruppe_id = zg.zielgruppe_id
            LEFT JOIN preiskategorie p ON v.preiskategorie_id = p.preiskategorie_id
            JOIN termin t ON v.veranstaltung_id = t.veranstaltung_id
            WHERE t.startzeit >= NOW()";

    if ($searchTerm) $sql .= " AND v.titel LIKE '%$searchTerm%'";
    if ($category)   $sql .= " AND k.bezeichnung LIKE '%$category%'";
    if ($type)       $sql .= " AND a.bezeichnung LIKE '%$type%'";
    if ($location)   $sql .= " AND o.name LIKE '%$location%'";
    if ($district)   $sql .= " AND s.bezeichnung LIKE '%$district%'";
    if ($audience)   $sql .= " AND zg.bezeichnung LIKE '%$audience%'";

    $sql .= " GROUP BY v.veranstaltung_id ORDER BY startzeit ASC";

    $searchResults = mysqli_query($link, $sql);

    if (!$searchResults) $errorMsg = "DB Error: " . mysqli_error($link);
}

The query is assembled piece by piece from the filters that were filled in; the results are sorted by the next date.

What I learned

Querying a database with many relations: joins across several tables, grouped dates and search filters.