Website/ajax/bericht.php

<?php #ajax/bericht.php

error_reporting(E_ALL);
ini_set("display_errors", 1);

require_once "../conf.php";

$years = array();
$query = "select distinct year(created_time) y from `facebook_posts` order by y desc";
if ($res = $mysqli->query($query)) {
  while ($row = $res->fetch_assoc()) {
    $years[] = $row["y"];
  }
} else {
  echo $mysqli->error."<br />".$query;
}
$year = $_POST["section"]["year"] ?? ($years[0] ?? date("Y"));

$prev = array("", "", "");
$next = array("", "", "");
$article = $_POST["article"] ?? "Empty";

$title = "";
$content = "";
$additional = "";

// Get post ID
$postID = 0;
if (isset($_POST["section"]["postID"]) && $_POST["section"]["postID"]!="") {
  $postID = $_POST["section"]["postID"];
} else {
  $res = $mysqli->query("select ID from facebook_posts where catid=1 and year(created_time) = ".$year." order by created_time desc limit 1");
  if ($res->num_rows==1) {
    $postID = $res->fetch_assoc()["ID"];
  }
}

// Get post content to ID
$created_time = "";
$message = "";
$query = "select created_time,title,message from facebook_posts where ID = ?";
if ($stmt = $mysqli->prepare($query)) {
  $stmt->bind_param("i", $postID);
  $stmt->execute();
  $stmt->bind_result($created_time, $title, $message);
  $stmt->fetch();
  $stmt->close();
}
$header = $title." (".$created_time.")";
$content = nl2br($message);

// Get next and previous posts
$limit = 1;
$prev = array();
$query = "select ID, created_time, year(created_time) `year`, title, '<' `dir` from facebook_posts "
        ."where catid=1 and created_time>'".$created_time."' order by created_time asc limit ".$limit;
$res = $mysqli->query($query);
while ($row = $res->fetch_assoc()) {
  $prev[] = $row;
}
if (count($prev)>1) {
  $tmp = $prev[0];
  $prev[0] = $prev[1];
  $prev[1] = $tmp;
  $prev[0]["dir"] = "<<";
}
$query = "select ID, created_time, year(created_time) `year`, title, '>' `dir` from facebook_posts where catid=1 and created_time<'".$created_time."' order by created_time desc limit ".$limit;
$res = $mysqli->query($query);
$next = array();
$first = true;
while ($row = $res->fetch_assoc()) {
  $next[] = $row;  
}
if (count($next)>1) {
  $next[1]["dir"] = ">>";
}

// Get all posts of selected year
$all = array();
$query = "select ID, date_format(created_time, '%d.%m.') datum, title from facebook_posts "
        ."where catid=1 and year(created_time)='".$year."' order by created_time desc";
$res = $mysqli->query($query);
while ($row = $res->fetch_assoc()) {
  $all[] = $row;
}

// Get shared links associated with the post
$query = "select unshimmed_url,caption from facebook_links where postID = ?";
if ($stmt = $mysqli->prepare($query)) {
  $stmt->bind_param("i", $postID);
  $stmt->execute();
  $stmt->bind_result($unshimmed_url, $caption);
  while ($stmt->fetch()) {
    $additional.= "<p>Geteilter Link: <a href='".$unshimmed_url."' target='_blank'>".$caption."</a></p>";
    $content = trim(str_replace($unshimmed_url, "", $content));
  }
  $stmt->close();
}

// Get attached images and videos
$query = "select fbid,mimetype,ext,width,height,thumbext,twidth,theight from facebook_media where postID = ?";
if ($stmt = $mysqli->prepare($query)) {
  $stmt->bind_param("i", $postID);
  $stmt->execute();
  $res = $stmt->get_result();
  if ($res->num_rows>0) {
    $additional.= "<div id='gallery_".preg_replace('/\s+/', '', $article)."' class='gallery'>";
    while ($row = $res->fetch_assoc()) {
      switch (explode("/", $row["mimetype"])[0]) {
        case "youtube":
          $additional.= "<iframe type='text/html' src='".$row["ext"]."'></iframe>";
          break;
        case "video":
          $additional.= "<video height='".$row["height"]."' width='".$row["width"]."' poster='/videos/fb/of/thumbs/"
                       .$row["fbid"].".".$row["thumbext"]."' preload='none' muted controls><source src='/videos/fb/of/".$row["fbid"]."."
                       .$row["ext"]."' type='".$row["mimetype"]."'></video>";
          break;
        case "image":
          $additional.= "<a href='/images/fb/of/".$row["fbid"].".".$row["ext"]."' target='_blank'><img src='/images/fb/of/";
          if (""!=$row["thumbext"]) {
            $additional.= "thumbs/".$row["fbid"].".".$row["thumbext"];
          } else {
            $additional.= $row["fbid"].".".$row["ext"];
          }
          $additional.= "'></a>";
          break;
      }
    }
    $additional.= "</div>";
  }
  $stmt->close();
}

$id = preg_replace('/\s+/', '', $article."_".$postID);

?>
<footer>
  <h1><?=$article;?></h1>
  <ul>
    <select onchange="javascript:loadArticle('<?=basename(__FILE__);?>', '<?=$article;?>', {'year': this.options[this.selectedIndex].value})">
      <?php foreach ($years as $y) { ?>
      <option<?=($year==$y ? " selected" : "");?>><?=$y;?></option>
      <?php } ?>
    </select>
    <select onchange="javascript:loadArticle('<?=basename(__FILE__);?>', '<?=$article;?>', {'year': <?=$year;?>, 'postID': this.options[this.selectedIndex].value})">
      <?php foreach ($all as $a) { ?>
      <option value='<?=$a["ID"];?>'<?=($postID==$a["ID"] ? " selected" : "");?>>
        <?=$a["datum"].(""==$a["title"] ? "" : ": ".$a["title"]);?>
      </option>
      <?php } ?>
    </select>
  </ul>
</footer>
<section id='<?=$id;?>'>
  <p><?=$content;?></p><?=$additional;?>
</section>
<footer>
  <ul>
    <?php foreach ($prev as $link) { ?>
    <a href="javascript:loadArticle('<?=basename(__FILE__);?>', '<?=$article;?>', {'year': <?=$link["year"];?>, 'postID': <?=$link["ID"];?>})"><li>
      <?=$link["dir"];?> <?=(""==$link["title"] ? $link["created_time"] : $link["title"]);?>
    </li></a>
    <?php } ?>
    <?php foreach ($next as $link) { ?>
    <a href="javascript:loadArticle('<?=basename(__FILE__);?>', '<?=$article;?>', {'year': <?=$link["year"];?>, 'postID': <?=$link["ID"];?>})"><li>
      <?=(""==$link["title"] ? $link["created_time"] : $link["title"]);?> <?=$link["dir"];?>
    </li></a>
    <?php } ?>
  </ul>
</footer>