getting data

매개변수 없을때

$sql = "SELECT forename, surname
          FROM member;";
$members = pdo($pdo, $sql)->fetchAll();

매개변수 있을때

$sql = "SELECT forename, surname
          FROM member
         WHERE id = :id;";
$member = pdo($pdo, $sql, ['id' => $id])->fetch();

---

header

<ul id="menu">
    <?php foreach ($navigation as $link) { ?>
    <li>
        <a href="category.php?id=<?= $link['id'] ?>" 
          <?= ($section == $link['id'] ) ? 'class="on" aria-current="page"' : '' ?>>
          <?= html_escape($link['name']) ?>
        </a>
    </li>
    <?php } ?>
    <li>
    	<a href="search.php">
      	  <span class="icon-search"></span><span class="search-text">Search</span>
    	</a>
    </li>
</ul>

---

category

$sql = "SELECT id, name, description FROM category WHERE id=:id;";
$category = pdo($pdo, $sql, [$id])->fetch();
if (!$category) {   
    include 'page-not-found.php';   
}

$sql = "SELECT a.id, a.title, a.summary, a.category_id, a.member_id, 
               c.name AS category,
               CONCAT(m.forename, ' ', m.surname) AS author,
               i.file AS image_file,
               i.alt  AS image_alt 
          FROM article    AS a
          JOIN category   AS c   ON a.category_id  = c.id
          JOIN member     AS m   ON a.member_id    = m.id
          LEFT JOIN image AS i   ON a.image_id     = i.id
         WHERE a.category_id = :id AND a.published = 1
         ORDER BY a.id DESC;";    
$articles = pdo($pdo, $sql, [$id])->fetchAll();  

$sql = "SELECT id, name FROM category WHERE navigation = 1;"; 
$navigation  = pdo($pdo, $sql)->fetchAll(); 
$section     = $category['id']; 
$title       = $category['name'];    
$description = $category['description'];

<main class="container" id="content">
  <section class="header">
    <h1><?= html_escape($category['name']) ?></h1>
    <p><?= html_escape($category['description']) ?></p>
  </section>
  <section class="grid">
  <?php foreach ($articles as $article) { ?>
    <article class="summary">
      <a href="article.php?id=<?= $article['id'] ?>">
        <img src="uploads/<?= html_escape($article['image_file'] ?? 'blank.png') ?>"
             alt="<?= html_escape($article['image_alt']) ?>">
        <h2><?= html_escape($article['title']) ?></h2>
        <p><?= html_escape($article['summary']) ?></p>
      </a>
      <p class="credit">
        Posted in <a href="category.php?id=<?= $article['category_id'] ?>">
        <?= html_escape($article['category']) ?></a>
        by <a href="member.php?id=<?= $article['member_id'] ?>">
        <?= html_escape($article['author']) ?></a>
      </p>
    </article>
  <?php } ?>
  </section>
</main>

---

article 

$sql = "SELECT a.title, a.summary, a.content, a.created, a.category_id, a.member_id, 
               c.name      AS category,
               CONCAT(m.forename, ' ', m.surname) AS author,
               i.file AS image_file,
               i.alt  AS image_alt 
          FROM article     AS a
          JOIN category    AS c  ON a.category_id = c.id
          JOIN member      AS m  ON a.member_id   = m.id
          LEFT JOIN image  AS i  ON a.image_id    = i.id
         WHERE a.id = :id  AND a.published = 1;";

$article = pdo($pdo, $sql, [$id])->fetch();  
if (!$article) {    
    include 'page-not-found.php';  
}

$sql = "SELECT id, name FROM category WHERE navigation = 1;"; 
$navigation  = pdo($pdo, $sql)->fetchAll(); 
$section     = $article['category_id']; 
$title       = $article['title']; 
$description = $article['summary'];

<main class="article container" id="content">
<section class="image">
    <img src="uploads/<?= html_escape($article['image_file'] ?? 'blank.png') ?>" 
        alt="<?= html_escape($article['image_alt']) ?>">
</section>
<section class="text">
    <h1><?= html_escape($article['title']) ?></h1>
    <div class="date"><?= format_date($article['created']) ?></div>
    <div class="content"><?= html_escape($article['content']) ?></div>
    <p class="credit">
        Posted in 
        <a href="category.php?id=<?= $article['category_id'] ?>">
         <?= html_escape($article['category']) ?>
        </a> 
        by <a href="member.php?id=<?= $article['member_id'] ?>">
       	 <?= html_escape($article['author']) ?>
        </a>
    </p>
</section>
</main>

---

member

$sql = "SELECT forename, surname, joined, picture FROM member WHERE id = :id;";
$member = pdo($pdo, $sql, [$id])->fetch(); 
if (!$member) {
    include 'page-not-found.php';
}

$sql = "SELECT a.id, a.title, a.summary, a.category_id, a.member_id, 
               c.name     AS category,
               CONCAT(m.forename, ' ', m.surname) AS author,
               i.file     AS image_file,
               i.alt      AS image_alt 
          FROM article    AS a
          JOIN category   AS c   ON a.category_id = c.id
          JOIN member     AS m   ON a.member_id   = m.id
          LEFT JOIN image AS i   ON a.image_id    = i.id
         WHERE a.member_id = :id AND a.published  = 1
         ORDER BY a.id DESC;";     
$articles = pdo($pdo, $sql, [$id])->fetchAll(); 

$sql = "SELECT id, name FROM category WHERE navigation = 1;"; 
$navigation  = pdo($pdo, $sql)->fetchAll(); 
$section     = '';   
$title       = $member['forename'] . ' ' . $member['surname']; 
$description = $title . ' on Creative Folk';

<section class="header">
    <h1><?= html_escape($member['forename'] . ' ' . $member['surname']) ?></h1>
    <p class="member"><b>Member since:</b> <?= format_date($member['joined']) ?></p>
    <img src="uploads/<?= html_escape($member['picture'] ?? 'member.png') ?>"
        alt="<?= html_escape($member['forename']) ?>" class="profile"><br>
</section>

<section class="grid">
<?php foreach ($articles as $article) { ?>
    <article class="summary">
    <a href="article.php?id=<?= $article['id'] ?>">
        <img src="uploads/<?= html_escape($article['image_file'] ?? 'blank.png') ?>"
            alt="<?= html_escape($article['image_alt']) ?>">
        <h2><?= html_escape($article['title']) ?></h2>
        <p><?= html_escape($article['summary']) ?></p>
    </a>
    <p class="credit">
        Posted in <a href="category.php?id=<?= $article['category_id'] ?>">
        <?= html_escape($article['category']) ?></a>
        by <a href="member.php?id=<?= $article['member_id'] ?>">
        <?= html_escape($article['author']) ?></a>
    </p>
    </article>
<?php } ?>
</section>

---

home

$sql = "SELECT a.id, a.title, a.summary, a.category_id, a.member_id, 
               c.name AS category,
               CONCAT(m.forename, ' ', m.surname) AS author,
               i.file     AS image_file,
               i.alt      AS image_alt 
          FROM article    AS a
          JOIN category   AS c ON a.category_id = c.id
          JOIN member     AS m ON a.member_id   = m.id
          LEFT JOIN image AS i ON a.image_id    = i.id
         WHERE a.published = 1
      ORDER BY a.id DESC
         LIMIT 6;";          
$articles = pdo($pdo, $sql)->fetchAll(); 

$sql = "SELECT id, name FROM category WHERE navigation = 1;"; 
$navigation  = pdo($pdo, $sql)->fetchAll();

$section     = ''; 
$title       = 'Creative Folk';  
$description = 'A collective of creatives for hire';

<main class="container grid" id="content">
<?php foreach ($articles as $article) { ?>
    <article class="summary">
    <a href="article.php?id=<?= $article['id'] ?>">
        <img src="uploads/<?= html_escape($article['image_file'] ?? 'blank.png') ?>"
            alt="<?= html_escape($article['image_alt']) ?>">
        <h2><?= html_escape($article['title']) ?></h2>
        <p><?= html_escape($article['summary']) ?></p>
    </a>
    <p class="credit">
        Posted in <a href="category.php?id=<?= $article['category_id'] ?>">
        <?= html_escape($article['category']) ?></a>
        by <a href="member.php?id=<?= $article['member_id'] ?>">
        <?= html_escape($article['author']) ?></a>
    </p>
    </article>
<?php } ?>
</main>

---

search, pageing 

$term  = filter_input(INPUT_GET, 'term'); 
$show  = filter_input(INPUT_GET, 'show', FILTER_VALIDATE_INT) ?? 6;
$from  = filter_input(INPUT_GET, 'from', FILTER_VALIDATE_INT) ?? 0;
$count = 0;     
$articles = []; 

if ($term) {     
    $arguments['term1'] = '%' . $term .'%';
    $arguments['term2'] = '%' . $term .'%';  
    $arguments['term3'] = '%' . $term .'%';

    $sql = "SELECT COUNT(title) FROM article
             WHERE title   LIKE :term1
                OR summary LIKE :term2
                OR content LIKE :term3
               AND published = 1;"; 
    $count = pdo($pdo, $sql, $arguments)->fetchColumn();

    if ($count > 0) {  
        $arguments['show'] = $show;
        $arguments['from'] = $from; 
        $sql = "SELECT a.id, a.title, a.summary, a.category_id, a.member_id, 
                       c.name      AS category,
                       CONCAT(m.forename, ' ', m.surname) AS author,
                       i.file      AS image_file,
                       i.alt       AS image_alt 
                  FROM article     AS a
                  JOIN category    AS c    ON a.category_id = c.id
                  JOIN member      AS m    ON a.member_id   = m.id
                  LEFT JOIN image  AS i    ON a.image_id    = i.id
                 WHERE a.title   LIKE :term1
                    OR a.summary LIKE :term2
                    OR a.content LIKE :term3
                   AND a.published = 1
              ORDER BY a.id DESC
                 LIMIT :show 
                OFFSET :from;"; 
        $articles = pdo($pdo, $sql, $arguments)->fetchAll();
    }
}

if ($count > $show) {
    $total_pages  = ceil($count / $show); 
    $current_page = ceil($from / $show) + 1;  
}

$sql = "SELECT id, name FROM category WHERE navigation = 1;"; 
$navigation  = pdo($pdo, $sql)->fetchAll(); 
$section     = ''; 
$title       = 'Search results for ' . $term; 
$description = $title . ' on Creative Folk';

<section class="header">
  <form action="search.php" method="get" class="form-search">
    <label for="search"><span>Search for: </span></label>
    <input type="text" name="term" value="<?= html_escape($term) ?>" 
           id="search" placeholder="Enter search term"  
    /><input type="submit" value="Search" class="btn btn-search" />
  </form>
  <?php if ($term) { ?><p><b>Matches found:</b> <?= $count ?></p><?php } ?>
</section>

<section class="grid">
  <?php foreach ($articles as $article) { ?>
  <article class="summary">
    <a href="article.php?id=<?= $article['id'] ?>">
      <img src="uploads/<?= html_escape($article['image_file'] ?? 'blank.png') ?>"
           alt="<?= html_escape($article['image_alt']) ?>">
      <h2><?= html_escape($article['title']) ?></h2>
      <p><?= html_escape($article['summary']) ?></p>
    </a>
    <p class="credit">
      Posted in <a href="category.php?id=<?= $article['category_id'] ?>">
      <?= html_escape($article['category']) ?></a>
      by <a href="member.php?id=<?= $article['member_id'] ?>">
      <?= html_escape($article['author']) ?></a>
    </p>
  </article>
  <?php } ?>
</section>

<?php if ($count > $show) { ?>
<nav class="pagination" role="navigation" aria-label="Pagination Navigation">
  <ul>
  <?php for ($i = 1; $i <= $total_pages; $i++) { ?>
    <li>
      <a href="?term=<?= $term ?>&show=<?= $show ?>&from=<?= (($i - 1) * $show) ?>"
        class="btn <?= ($i == $current_page) ? 'active" aria-current="true' : '' ?>">
        <?= $i ?>
      </a>
    </li>
  <?php } ?>
  </ul>
</nav>
<?php } ?>