Putting the site onto a database
I had already created basic HTML pages saved as separate files. Now all I needed to do was copy and paste into tables. I pulled up phpMyAdmin on my local machine (http://localhost/phpmyadmin/) and created a new database called "mycms". If you're using an SQL query, it's simply "CREATE DATABASE 'mycms'". All of my tables for the entire CMS will be created and access in that database.Next I created a table called "page_files" with two columns: "page_name" (with a VARCHAR type) and "file_contents" (TEXT). In query language, that would be:
CREATE TABLE IF NOT EXISTS `page_files` ( `page_name` varchar(255) NOT NULL, `file_contents` text, PRIMARY KEY (`page_name`))The page_name, for this iteration of the project, is the actual URI of the page ("/home", "/about", "/portfolio/packagedesign", etc., including the leading slash). The file_contents is the entire HTML document from <!DOCTYPE html> down to </html>. Notice in the query above I made "page_name" the PRIMARY KEY. This column should always be unique, and later when we query the database we will search by the URI, so this column needs to be indexed.
Testing the CMS
To view the content I setup a simple PHP page that echos the "file_contents" of the requested page. I call this page "viewpage.php". I'm using the object-oriented mysqli method which is supported in PHP5 and newer. Here is some documentation if you need help getting started with mysqli commands. Here's what viewpage.php looks like:<?php
//Defining the main constants of my database:define('HOST','localhost'); //my hostdefine('USER','root'); //my usernamedefine('PASS',''); //my password, on localhost I leave this empty
define('DBNAME','mycms'); //my database I just created
$pageDb = new mysqli(HOST,USER,PASS,DBNAME);
$result = $pageDb->query("SELECT * FROM page_files WHERE page_name='".$_GET['page']."' LIMIT 1"); //notice the $_GET['page']
$row = $result->fetch_array(); //creating an array of results
echo$row['file_contents']; //echo the only result that should be on the row, "file_contents"?>
Now in my address bar I enter "http://localhost/example.com/viewpage.php?page=/home". The html from my /home column is echoed and it renders as normal. All I have to do is change the "page=" parameter in my URL and I can navigate to each of the pages. In my viewpage.php document, I used $_GET to find the "page" parameter and enter it into my query ("...WHERE page_name='".$_GET['page']."'").
Conclusion and Considerations
Make sure you are escaping any input your user might enter to protect your database. In my PHP document, I should use the mysql_real_escape_string procedural: $page = mysql_real_escape_string($_GET['page']).From here you can easily create a content editor to update and create new pages. Remember, the syntax for editing an existing entry is "UPDATE page_files SET file_contents=$mycontent WHERE page_name=$mypage" and creating a new entry is "INSERT INTO page_files VALUES ($mycontent)".
