sql_queries.php

<?php
/**
 * Query layer — every value is BOUND, never concatenated into SQL.
 *
 * The old version built query strings with "..." . $var . "...". Because
 * poem.php, poet.php and year.php pass $_GET straight in, a visitor could end
 * their value with a quote and have the rest read as SQL. See the git history
 * of this file.
 *
 * Each function now takes the mysqli handle, runs a prepared statement, and
 * returns plain arrays. Call sites use foreach instead of ->fetch_assoc()
 * loops.
 *
 * $genre is 'poem' or 'story' and comes from section.php, which is how the
 * poetry site and the prose site at /mer-vi share one implementation.
 */

// ---------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------

function db_all(mysqli $db, string $sql, string $types = '', ...$args): array
{
    $st = $db->prepare($sql);
    if ($st === false) {
        error_log('prepare failed: ' . $db->error . ' :: ' . $sql);
        return [];
    }
    if ($types !== '') {
        $st->bind_param($types, ...$args);
    }
    $st->execute();
    $res = $st->get_result();
    $rows = $res ? $res->fetch_all(MYSQLI_ASSOC) : [];
    $st->close();
    return $rows;
}

function db_row(mysqli $db, string $sql, string $types = '', ...$args): ?array
{
    $rows = db_all($db, $sql, $types, ...$args);
    return $rows[0] ?? null;
}

function db_count(mysqli $db, string $sql, string $types = '', ...$args): int
{
    return count(db_all($db, $sql, $types, ...$args));
}

// ---------------------------------------------------------------------
// poem.php
// ---------------------------------------------------------------------

/** A work plus every translation of it. */
function poem_sql(mysqli $db, string $poem, string $genre): array
{
    return db_all($db,
        "SELECT *, poem.poet AS ogpoet, poem.source AS ogsource,
                trans.source AS tsource, trans.poet AS tpoet,
                trans.translator AS ttranslator
           FROM poem LEFT JOIN trans ON poem.poem = trans.poem
          WHERE poem.poem = ? AND poem.genre = ?
          ORDER BY trans.lang = 'eng' DESC",
        'ss', $poem, $genre);
}

function poet_sql(mysqli $db, string $poet_eng): ?array
{
    return db_row($db, "SELECT * FROM poet WHERE name_e = ?", 's', $poet_eng);
}

function biolinks_sql(mysqli $db, string $poet_eng): array
{
    return db_all($db, "SELECT * FROM bio_links WHERE poet = ?", 's', $poet_eng);
}

function poemlinks_sql(mysqli $db, string $poem): array
{
    return db_all($db, "SELECT * FROM poem_links WHERE poem = ? AND type = 'poem'",
        's', $poem);
}

function contextlinks_sql(mysqli $db, string $poem): array
{
    return db_all($db, "SELECT * FROM poem_links WHERE poem = ? AND type = 'context'",
        's', $poem);
}

// ---------------------------------------------------------------------
// index.php
// ---------------------------------------------------------------------

function poem_list_yid(mysqli $db, string $genre): array
{
    return db_all($db,
        "SELECT title_y, poet, img, poem FROM poem
          WHERE public IS TRUE AND genre = ? ORDER BY title_y",
        's', $genre);
}

function poem_list_eng(mysqli $db, string $genre): array
{
    return db_all($db,
        "SELECT *, poem.poet AS poet_e
           FROM poem LEFT JOIN trans ON poem.poem = trans.poem
          WHERE genre = ? AND public IS TRUE AND trans.lang = 'eng'
          ORDER BY title",
        's', $genre);
}

function poet_yid_sql(mysqli $db, string $poet_eng): ?string
{
    $r = db_row($db, "SELECT name_y FROM poet WHERE name_e = ?", 's', $poet_eng);
    return $r['name_y'] ?? null;
}

function poet_list_yid(mysqli $db): array
{
    return db_all($db, "SELECT name_y, name_e, img FROM poet ORDER BY name_y");
}

function poet_list_eng(mysqli $db): array
{
    return db_all($db, "SELECT name_y, name_e, img FROM poet ORDER BY name_e");
}

function count_poems(mysqli $db, string $poet_eng, string $genre): int
{
    return db_count($db,
        "SELECT poem FROM poem WHERE poet = ? AND public IS TRUE AND genre = ?",
        'ss', $poet_eng, $genre);
}

function poem_year_list(mysqli $db, string $genre): array
{
    return db_all($db,
        "SELECT DISTINCT YEAR(date) AS y FROM poem
          WHERE public IS TRUE AND genre = ? ORDER BY date",
        's', $genre);
}

// ---------------------------------------------------------------------
// year.php
// ---------------------------------------------------------------------

function poems_by_year(mysqli $db, int $year, string $genre): array
{
    return db_all($db,
        "SELECT * FROM poem
          WHERE YEAR(date) = ? AND public IS TRUE AND genre = ?
          ORDER BY title_y",
        'is', $year, $genre);
}

function poem_year_eng(mysqli $db, int $year, string $genre): array
{
    return db_all($db,
        "SELECT trans.title, poem.poet, poem.poem, poem.img
           FROM poem LEFT JOIN trans ON poem.poem = trans.poem
          WHERE YEAR(date) = ? AND genre = ? AND public IS TRUE
            AND trans.lang = 'eng'
          ORDER BY title",
        'is', $year, $genre);
}

// ---------------------------------------------------------------------
// poet.php
// ---------------------------------------------------------------------

function poet_check(mysqli $db, string $poet, string $genre): int
{
    return db_count($db,
        "SELECT poem FROM poem WHERE poet = ? AND public IS TRUE AND genre = ?",
        'ss', $poet, $genre);
}

function poet_poem_yid_list(mysqli $db, string $poet, string $genre): array
{
    return db_all($db,
        "SELECT title_y, poet, img, poem FROM poem
          WHERE poet = ? AND public IS TRUE AND genre = ?
          ORDER BY title_y",
        'ss', $poet, $genre);
}

function poet_poem_eng_list(mysqli $db, string $poet, string $genre): array
{
    return db_all($db,
        "SELECT *, poem.poet AS poet_e
           FROM poem LEFT JOIN trans ON poem.poem = trans.poem
          WHERE genre = ? AND public IS TRUE AND trans.lang = 'eng'
            AND poem.poet = ?
          ORDER BY title",
        'ss', $genre, $poet);
}

// ---------------------------------------------------------------------
// yud_browse.php  (Broderzon's Yud sequence)
// ---------------------------------------------------------------------

/**
 * NOTE: the old $yud_eng filtered on `title_e`, which is not a column on any
 * table — poem has title_y, trans has title. MySQL would have rejected the
 * query outright, so the English half of this page has been failing. Changed
 * to trans.title, which is plainly what was meant. Worth eyeballing the page.
 */
function yud_eng(mysqli $db): array
{
    return db_all($db,
        "SELECT trans.title, poem.poet, poem.poem, poem.img
           FROM poem LEFT JOIN trans ON poem.poem = trans.poem
          WHERE poem.genre = 'poem' AND public IS TRUE AND trans.lang = 'eng'
            AND poem.poet = 'Moyshe Broderzon' AND trans.title LIKE '%Yud%'
          ORDER BY trans.title");
}

function yud_yid(mysqli $db): array
{
    return db_all($db,
        "SELECT title_y, poet, img, poem FROM poem
          WHERE poet = 'Moyshe Broderzon' AND title_y LIKE '%י [%'
            AND public IS TRUE
          ORDER BY title_y");
}