The Backend
Before we’ve seen how to render the blog articles from the database, now It’s all about their administration (editing, updating, and deleting) and how to upload them to the database.
Online Business Card and Blog – Post Editor
Submit Form Data to Database
The Editor is a form used to write and add new posts to the Blog. First, we will fill in the content (media, text, youtube links, etc.) and then click submit. The information is now saved (as an associative array) in the $_POST Global.
<?php
// After the form is send a new blogpost is sunmitted to the database table `posts`
if(isset($_POST["senden"])) {
//Access and save each $_POST value in variables
$adminFID = (int)($_POST["adID"]);
$postDate = strip_tags($_POST["datetime"]);
$postTitle = strip_tags($_POST["title"]);
$postBody = $_POST["text"];
$postAlt = strip_tags($_POST["bildalt"]);
$postTube = strip_tags($_POST["btube"]);
$dateiname = $_FILES["bild"]["name"];
$dateiname2 = $_FILES["media"]["name"];
if($dateiname == "") {
$postBild = "default.png";
} else {
$alt=array("ö","Ö","ä","Ä","ü","Ü","ß"," ");
$neu=array("oe","Oe","ae","Ae","ue","Ue","ss", "_");
$dateiname = str_replace($alt, $neu, $dateiname);
$dateiname = strtolower($dateiname);
move_uploaded_file($_FILES["bild"]["tmp_name"], "$pfad/$ordner/$dateiname");
$postBild = $dateiname;
}
if($dateiname2 == "") {
$postMedia = "";
} else {
$alt2=array("ö","Ö","ä","Ä","ü","Ü","ß"," ");
$neu2=array("oe","Oe","ae","Ae","ue","Ue","ss", "_");
$dateiname2 = str_replace($alt2, $neu2, $dateiname2);
$dateiname2 = strtolower($dateiname2);
move_uploaded_file($_FILES["media"]["tmp_name"], "$pfad/$ordner2/$dateiname2");
$postMedia = $dateiname2;
}
...Now we need to access and save each $_POST value as a variable. After that, we write the SQL statement to insert this data to the table using wildcards. We use prepared statements to bind our new variables as parameters to the wildcards in the SQL statement. In the last step, we execute our query, the post is now saved in the database.
...
//Add these values to database table `posts` via sql statement
$sql = "INSERT INTO posts
( adminFID, postTitle, postDate, postBody, postTube, postBild, postBildAlt, postMedia)
VALUES
( :id, :titel, :datum, :body, :tube, :bild, :bildalt ,:media)";
$abfrage = $db->prepare($sql);
//bind $_POST data as parameter to wildcards
$abfrage->bindParam(":id", $adminFID);
$abfrage->bindParam(":titel", $postTitle);
$abfrage->bindParam(":datum", $postDate);
$abfrage->bindParam(":body", $postBody);
$abfrage->bindParam(":bildalt", $postAlt);
$abfrage->bindParam(":tube", $postTube);
$abfrage->bindParam(":bild", $postBild);
$abfrage->bindParam(":media", $postMedia);
//data is now added to db by executing sql statement
$abfrage->execute();
header("location:a-blogpost.php");
}
?>Render blogposts (Admin Table / Blog )
We have already know how to render the blogposts from DB in the frontend (see previous lessons), the only thing left to render is the admin table:
<?php
//Select blogposts from
$sql="SELECT * FROM posts
ORDER BY postDate DESC";
$abfrage=$db->query($sql);
?>
<div class="container table-responsive bg-dark pl-2" id="abtable">
<table id="btable" class="table-dark table-striped table-hover text-center px-2">
<tr id="brow" >
<th class="p-1">Post ID</th>
<th class="p-1">Admin ID</th>
<th class="p-1">Datum/Uhrzeit</th>
<th class="p-1">Titel</th>
<th class="p-1">Content</th>
<th class="p-1">Bild Alt</th>
<th class="p-1">Bild</th>
<th class="p-1">Video</th>
<th class="p-1">Youtube</th>
<th class="p-1">Ändern</th>
<th class="p-1">Bild ändern</th>
<th class="p-1">Video ändern</th>
<th class="p-1">Löschen</th>
</tr>
<?php
while($row=$abfrage->fetch()){
?>
<tr >
<td class="p-1"><?php echo $row["postID"];?></td>
<td class="p-1"><?php echo $row["adminFID"];?></td>
<td class="p-1"><?php echo $row["postDate"];?></td>
<td class="p-1"><?php echo $row["postTitle"];?></td>
<!-- Modal kommt zur Anwendung damit grosse Texte die Tabelle nicht verzerren -->
<td class="p-1">
<button type="button" class="btn btn-success" data-toggle="modal" data-target="#p<?php echo $row["postID"];?>">
Inhalt anzeigen
</button>
<div class="modal fade text-dark" id="p<?php echo $row["postID"];?>">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">x</button>
</div>
<div class="modal-body">
<?php echo $row["postBody"];?>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-success" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
</td>
<td class="p-1"><?php echo $row["postBildAlt"];?></td>
<td class="p-1">
<img src="bilder/<?php echo $row["postBild"];?>" width="150" height="100" alt="<?php echo $row['postBild'];?>">
</td>
<td class="p-1">
<video width="150" height="100"controls><source src="media/<?php echo $row["postMedia"];?>" type="video/mp4"></video>
</td>
<td class="p-1">
<iframe width="150" height="100" src="<?php echo $row["postTube"];?>" frameborder="0" allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
</td>
<!--Nachträgliche Bearbeitung der Einträge-->
<td class="p-1">
<a href="a-update-blogpost.php?aendern=<?php echo $row['postID'];?>">
<i class="fas fa-edit text-success" style="font-size:50px;"></i>
</a>
</td>
<td class="p-1">
<a href="a-update-bild.php?aendern=<?php echo $row['postID'];?>">
<i class="fas fa-image text-success" style="font-size:50px;"></i>
</a>
</td>
<td class="p-1">
<a href="a-update-video.php?aendern=<?php echo $row['postID'];?>">
<i class="fas fa-film text-success" style="font-size:50px;"></i>
</a>
</td>
<td class="p-1">
<a href="?loeschen=<?php echo $row['postID'];?>">
<i class="fas fa-times text-danger" style="font-size:50px;"></i>
</a>
</td>
</tr>
<?php
}
?>
</table>
</div>We have now covered most of the essential parts of my blog. I had to let out some pieces of the code due to security reasons because this blog remains online. Once I take it offline I will certainly share the whole code in this post. Nevertheless, I hope you enjoyed letting me guide you through the code. If you’re seeking completes projects to experiment on or to work with just look trough my portfolio. Thank you very much for reading.
Your Web Artisan
Hempel Joël
