A
Anonymous
Guest
A simple tutorial to help you create a small cms blog using PHP, PDO and MySQL
I always wanted to create a tutorial on how to insert data to a database table, so here it is.
You first must modify the config.php file to set your connection strings in order to get the rest of the files to run properly. Then you must either setup up the database and database table yourself or run the install.php file. Finally the index.php demostrates and informs you how to go about writing the read / write portions of a small CMS. It's my opinion that the code is commented and variables properly named that you should understand how-to-write-read to a MySQL database table. In the future I plan on updating it where one can edit and delete threads that are posted to the database table.
Note: This is how-to-write-and-read-to-a-database-table NOT proper how to write secure code though I do use prepared statements. In the future I might security to this tutorial, but this is not a tutorial on php security. If I did this wouldn't be a simple short tutorial, for security on the web is a big big topic
config.php
install.php
index.php
style.css
I will update from time to time this tutorial so it's best to check here first -> https://github.com/Strider64/PHP_PDO_Tutorial
I always wanted to create a tutorial on how to insert data to a database table, so here it is.
You first must modify the config.php file to set your connection strings in order to get the rest of the files to run properly. Then you must either setup up the database and database table yourself or run the install.php file. Finally the index.php demostrates and informs you how to go about writing the read / write portions of a small CMS. It's my opinion that the code is commented and variables properly named that you should understand how-to-write-read to a MySQL database table. In the future I plan on updating it where one can edit and delete threads that are posted to the database table.
Note: This is how-to-write-and-read-to-a-database-table NOT proper how to write secure code though I do use prepared statements. In the future I might security to this tutorial, but this is not a tutorial on php security. If I did this wouldn't be a simple short tutorial, for security on the web is a big big topic
config.php
Code:
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
define('DATABASE_HOST', 'local_host_name');
define('DATABASE_NAME', 'myCMS');
define('DATABASE_USERNAME', 'username');
define('DATABASE_PASSWORD', 'password');
define('DATABASE_TABLE', 'myBlog');install.php
Code:
<?php
require_once 'config.php'; // Configuration file for turning error reporting and connection strings to database:
/*
* I think the following is pretty self explanatory and the index.php file helps you on how to insert and read
* data into a database table better.
*/
try {
$conn = new PDO('mysql:host=' . DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE DATABASE IF NOT EXISTS myCMS";
$conn->exec($sql);
$sql = "use myCMS";
$conn->exec($sql);
$sql = "CREATE TABLE IF NOT EXISTS myBlog (
ID int(11) AUTO_INCREMENT PRIMARY KEY,
title varchar(30) NOT NULL,
comment text NOT NULL,
date_added datetime NOT NULL DEFAULT '0000-00-00 00:00:00')";
$conn->exec($sql);
echo "DB created successfully";
} catch (PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}index.php
Code:
<?php
require_once 'config.php'; // Configuration file for turning error reporting and connection strings to database:
/*
* The first thing to do is to make sure you have a database named myCMS and a database table named myBlog.
* You can run the install file that will create the database and database table by running install.php if you want
* or you can create the database and database table yourself.
*/
/*
* Establish a database connection.
*/
$db_options = [
/* important! use actual prepared statements (default: emulate prepared statements) */
PDO::ATTR_EMULATE_PREPARES => false
/* throw exceptions on errors (default: stay silent) */
, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
/* fetch associative arrays (default: mixed arrays) */
, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
];
$pdo = new PDO('mysql:host=' . DATABASE_HOST . ';dbname=' . DATABASE_NAME . ';charset=utf8', DATABASE_USERNAME, DATABASE_PASSWORD, $db_options);
/*
* Check to see if user has clicked on the submit button.
*/
$submit = filter_input(INPUT_POST, 'submit', FILTER_SANITIZE_FULL_SPECIAL_CHARS);
if (isset($submit) && $submit === "submit") {
/*
* Grab User's Responses from Form.
*/
$title = htmlspecialchars($_POST['title']);
$comment = htmlspecialchars($_POST['comment']);
/*
* Insert Into Database Table myBlog.
*/
$query = 'INSERT INTO myBlog(title, comment, date_added) VALUES (:title, :comment, NOW())';
$stmt = $pdo->prepare($query);
$result = $stmt->execute([':title' => $title, ':comment' => $comment]);
if ($result) {
header("Location: index.php");
exit();
}
}
/*
* Read from a database table is pretty straight forward and the only real tough part of it is writing the
* query correctly. Visting https://www.mysql.com/ will help you understand MySQl.
* PDO can better be understand by visiting https://phpdelusions.net/pdo and I highly recommend the website for it
* has helped me to understand pdo better. One word of advice and that is to ALWAYS use PREPARED statements for
* security reasons. I also recommend staying up on on PHP, PDO and MYSQL, for all tutorials will eventually become
* outdated (even this one).
*/
function readBlog($pdo = NULL) {
$query = 'SELECT id, title, comment, DATE_FORMAT(date_added, "%W, %M %e, %Y") as display_date, date_added as my_date FROM myBlog ORDER BY my_date DESC';
$stmt = $pdo->prepare($query); // Prepare the query:
$stmt->execute(); // Execute:
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $data;
}
$rows = readBlog($pdo);
//echo "<pre>" . print_r($rows, 1) . "</pre>";
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Comment Tutorial</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<form id="commentForm" action="" method="post">
<fieldset>
<legend>Comment Form</legend>
<label for="title">Title</label>
<input id="title" type="text" name="title" value="">
<label class="textBox" for="comment">Comment</label>
<textarea id="comment" name="comment" placeholder="Enter Comment Here..."></textarea>
<input type="submit" name="submit" value="submit">
</fieldset>
</form>
<div id="articles">
<?php
foreach ($rows as $row) {
echo '<div class="article">' . "\n";
echo "<h1>" . $row['title'] . "</h1>\n";
echo '<span class="date">' . $row['display_date'] . '</span>' . "\n";
echo "<p>" . $row['comment'] . "</p>\n";
echo "</div>\n";
}
?>
</div>
</body>
</html>style.css
Code:
* {
box-sizing: border-box;
}
body {
background-color: #ccc;
padding: 0;
margin: 0;
}
form#commentForm {
float:left;
display: block;
width: 100%;
max-width: 400px;
height: auto;
background-color: #fff;
padding: 15px;
margin: 20px;
}
form#commentForm fieldset { border: 1px solid #336699;}
form#commentForm legend {
font-family: Arial, Helvetica, sans-serif;
font-size: 1.2em;
color: #336699;
padding: 0 5px;
}
form#commentForm label {
float: left;
display: block;
width: 100%;
max-width: 140px;
height: 30px;
font-family: Arial, Helvetica, sans-serif;
font-size: 1.0em;
line-height: 30px;
color: #336699;
text-align: left;
padding: 0;
}
form#commentForm input {
clear: right;
display: block;
width: 100%;
max-width: 280px;
height: 30px;
border: 1px solid #336699;
outline: none;
font-family: Arial, Helvetica, sans-serif;
font-size: 1.0em;
color: #2e2e2e;
padding: 0 5px;
margin-bottom: 10px;
}
form#commentForm label.textBox {
clear: both;
text-align: left;
font-size: 1.2em;
padding: 0;
margin-top: 20px;
}
form#commentForm textarea {
resize: none;
border: 1px solid #336699;
outline: none;
clear: both;
display: block;
width: 100%;
max-width: 360px;
height: 300px;
font-family: Arial, Helvetica, sans-serif;
font-size: 1.0em;
line-height: 1.5;
color: #2e2e2e;
padding: 10px;
}
form#commentForm input[type=submit] {
-moz-border-radius: 8px;
-webkit-border-radius: 8px;
border-radius: 8px;
-moz-box-shadow: 2px 2px 3px rgba(46, 46, 46, 0.4);
-webkit-box-shadow: 2px 2px 3px rgba(46, 46, 46, 0.4);
box-shadow: 2px 2px 3px rgba(46, 46, 46, 0.4);
outline: none;
border: none;
float: right;
display: block;
width: 100%;
max-width: 120px;
height: 40px;
cursor: pointer;
background-color: #4484CE;
font-family: Arial, Helvetica, sans-serif;
font-size: 1.0em;
color: #fff;
text-transform: capitalize;
margin-top: 20px;
}
form#commentForm input[type=submit]:hover {
background-color: #2e2e2e;
color: #ffa;
}
div#articles {
float: left;
display: block;
width: 100%;
max-width: 600px;
height: auto;
background-color: lightblue;
padding: 10px;
margin: 20px 10px;
}
div#articles .article {
position: relative;
display: block;
width: 100%;
max-width: 580px;
height: auto;
background-color: #fff;
padding: 5px 20px;
margin: 10px auto;
}
div#articles .article h1 {
font-family: "Palatino Linotype", "Book Antiqua", Palatino, serif;
font-size: 1.8em;
line-height: 1.0;
}
div#articles span {
position: absolute;
top: 10px;
right: 20px;
font-family: "Palatino Linotype", "Book Antiqua", Palatino, serif;
font-size:0.8em;
font-style: italic;
font-weight: bold;
}
div#articles .article p {
font-family: Arial, Helvetica, sans-serif;
font-size: 1.2em;
line-height: 1.5;
}I will update from time to time this tutorial so it's best to check here first -> https://github.com/Strider64/PHP_PDO_Tutorial