Save and Retrieve CKEditor Data using PDO and MySQL

Welcome again!  As promised on my other posts dealing with this subject, I am finally publishing this tutorial!  This tutorial is written using the HTML5 Boilerplate so it should responsive-ready. 🙂

I will not repeat all the detail that I put in the other tutorials… you can go look at them to get that: Saving and Posting  I will, however, explain all the new stuff and I welcome questions and comments!

The complete tutorial can be downloaded here.  Enjoy!  If you use github, you can also fork it here.

Here is the list of files that I will be talking about:

• /index.php
• /config.php
• /Message.php
• /result.php
• /ckeditor/plugins/ajaxsave/plugin.js
• /js/main.js
• /css/main.css
• /ckeditor_db.sql

So, the code that we need to look at in the file, index.php, begins at line 25:

<form action="result.php" method="post">
    <textarea class="editor" id="editor" name="editor"></textarea>
    <div id="messages"></div>
 </form>

<section id="message-container">
 <?php
    require_once __DIR__ . '/config.php';
    require_once __DIR__ . '/Message.php';
    $Message = new Message;
    try {
        $messages = $Message->fetchMessages($pdo);
        foreach($messages as $message) {
             echo '<div class="message">' . $message['message'] . '</div>';
        }
    } catch(Exception $e) {
        throw new Exception($e->getMessage());
    }
 ?>
 </section>

Here we are putting a section below the CKEditor for our messages to show up and, in that section, some PHP code that will connect to the database and provide class methods to save and retrieve the messages we save.  So let’s look at the config.php file next before we discuss the rest of this code.

The config.php file’s only purpose is to provide a PDO (PHP Data Object) variable that we will use to access and save the data.  Here is the code:

<?php
$config = array(
        'database' => array(
            'local' => array(
            'name' => 'ckeditor',
            'host' => '127.0.0.1',
            'user' => '',
            'pass' => ''
        ),
        'production' => array(
            'name' => 'ckeditor',
            'host' => '127.0.0.1',
            'user' => '',
            'pass' => ''
        ),
    ),
);

if($_SERVER['SERVER_ADDR'] == '127.0.0.1') {
    $host = $config['database']['local']['host'];
    $dbName = $config['database']['local']['name'];
    $dbUser = $config['database']['local']['user'];
    $dbPass = $config['database']['local']['pass'];
} else {
    $host = $config['database']['production']['host'];
    $dbName = $config['database']['production']['name'];
    $dbUser = $config['database']['production']['user'];
    $dbPass = $config['database']['production']['pass'];
}

$pdo = new PDO("mysql:host={$host};dbname={$dbName}", $dbUser, $dbPass);

You will notice there is no closing ?> tag, this is because this file is a pure PHP file with no other code types in it and is now the accepted (and recommended) way.

This config file provides configuration for 2 server environments; if you are working from your computer via XAMPP or other means, the server will return the local address of 127.0.0.1, otherwise, it will give the IP address of the server you are working from.  Your TODO is to put your own credentials in the user and pass fields for at least one of the two environments; from there, this code should just work.  The last line of the code instantiates a new PDO object and assigns it to a variable named appropriately, $pdo.  This variable will then be available to the file to provide connectivity with your database.

Next, let’s take a peek at the Message class found in Message.php!

<?php
class Message
{
    /**
    * Creates a new record
    *
    * @param $message
    * @param PDO $pdo
    * @return bool
    */
    public function createMessage($message, PDO $pdo)
    {
        /* Table: messages
        * Fields: id (INT, primary key, auto-increment)
        * message (TEXT)
        */
        $sql = <<<EOT
INSERT INTO messages (message) VALUES (:message)
EOT;
        $stmt = $pdo->prepare($sql);
        return $stmt->execute(array(':message' => $message));
    }

    /**
    * Returns all the records
    *
    * @param PDO $pdo PDO connection to use
    * @return array
    */
    public function fetchMessages(PDO $pdo)
    {
        $sql = <<<EOT
SELECT *
FROM messages m
ORDER BY id DESC
EOT;

        $stmt = $pdo->prepare($sql);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

This class includes two methods that allow the message to be created and that retrieves all the messages in the database.  You will notice the code in the index.php file that instantiates the class on line 36 ($Message = new Message;).  This line provides a variable that allows us access to the public methods in our Message class.  You will see on line 38 of index.php how this is used: we pass in the $pdo variable into the fetchMessages() method found in the Message class and places it in a variable named $messages.

Then on lines 37-41, we loop through using a foreach loop retrieving message from the object and displaying it on the page using echo.  This is performed using a try, catch, throw block just in case something happens…. this allows us to fail gracefully; for more information please visit http://php.net/manual/en/language.exceptions.php

Now our result.php file is greatly simplified!  We are now down to 5 lines of code. 🙂

<?php

require_once __DIR__ . '/config.php';
require_once __DIR__ . '/Message.php';

$Message = new Message;
$message = $_POST['data'];
$Message->createMessage($message, $pdo);

By now, you should pretty well know what this code is doing; we bring in the DB configuration and the Message class, create a new instance of the class, then retrieve the POSTed data and create the new message.  Very simple indeed!

This brings us to the changes in our autosave plugin.js file.  These changes happen beginning on line 10 of the file found at “/ckeditor/plugins/ajaxsave/plugin.js”.

success: function () {
    var edata = editor.getSnapshot();
    $("#message-container").prepend('<div class="message">' + edata + '</div>');
 }

Upon successful completion of the post, the new message is displayed below the form, giving you instant notification that the message was successfully posted!

I only mention the main.js and main.css because I moved the inline css from the other posts into them… you can review them at your leisure… the custom css starts at line 96. 🙂

You may want to use the handy SQL file (ckeditor_db.sql) to create the necessary database and table for this tutorial.

Thanks again for reading this!  I hope it is helpful, but please feel free to post comments and questions below and I will do my best to respond!

Bud Manz
Owner – Manz Web Designs, LLC