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

Bud Manz is a strong advocate of the Open Source movement, believing that the way to making one's self the best they can be is most effectively accomplished by helping other people succeed. He enjoys arranging (and singing!) hymns, gardening, and most things geeky :)

22 Comments

  1. gloworm 4 years ago

    Thanks Bud. Nice work in your solution and bringing your code uptodate with current recommendations.

    I have downloaded the solution and made the changes as required in config.php and have it work as designed – saving data from the editor instance into the database ckeditor.messages table by pressing the ‘unidentified’ Ajaxsave button. I also notice that the saved data is echoed to the screen. I see that all messages are retrieved and echoed to the screen when loading or reloading the html page.

    Are you working on the next logical step to retrieve a message of interest, load it into the editor instance and then save it back into the database with INSERT … ON DUPLICATE KEY UPDATE …

    cheers

    • Hello,

      I will try to get to that as soon as I can… things are pretty busy here right now, so I won’t say how soon.

      Thanks,
      Bud

  2. hyper1 4 years ago

    Hello Sir,
    Thank you for helping us to get this working. I wrote this comment on your old site, but I see you are here now, so I just copy/ pasted and edited it into here.
    I hope you can help me. I am trying to figure out how to save the content that is entered. I got the button working. It was hard to find at first, but I guess the blank square at the top left corner is the ajax button because that is what the alt text says. It doesn’t save anything, though. I was expecting (the novice that I am) that this was a tutorial to teach me how to do the saving side of this. ckeditor gives us the code for it but the saving of it is up to us they say. But I don’t know how to make a saving application. I also don’t know how to set up a username/ password login application. Is this tutorial supposed to be to teach us how to save the data once it is entered? Or is there still more that I need to do?

    • Hello,

      If you follow the tutorial, it should provide you with a working application that saves and retrieves the data. I will look into the button issue and try to follow up with it. Please make sure you have followed all the instructions in the tutorial and, if it still doesn’t work, please copy/paste your code into PasteBin and share the link so I can see it.

      Thanks,
      Bud

  3. gloworm 4 years ago

    Hey Bud, Thanks for your persistence on this and I recognise that this will get done when it gets done.

    Cheers

  4. hyper1 4 years ago

    Hello again, Sir.
    I have done a lot of work on my site since the last time we corresponded, but I still don’t have it saving yet.
    I set up a MySQL server through Bluehost, and pointed the site to my database from dreamweaver cs6 (wouldn’t work in cc).
    And I filled in the username and password and database name into the result.php file.

    Unfortunately, the editor still behaves no differently for me. It’s as if I didn’t do anything new. It just won’t save.

    I appreciate your help thus far. I did send you a pasted document where you told me to. Any help you can offer would be great.

    Thank you,
    Kris

  5. hyper1 4 years ago

    By the way, I am still trying to do this simply going by this tutorial: Saving CKEditor data to MySQL Database. Will this still work?
    If so, I would like to try what Todd said (the first guy that commented on that tutorial page. He gave the code for a different ajax plug-in, but I tried copy/pasting it, but it dw throws an error message. Do you think if I did what he says, it would fix my problem of my website not being updated when I save and refresh my work?

    Sincerely,
    Kris

  6. hyper1 4 years ago

    Ok, I have tried to implement both the save tutorials. I added the code you said to add from here, and I reduced the code from the results.php page down to only 5 lines of code as per this tutorial.

    Now I have nothing but a blank screen.

  7. hyper1 4 years ago

    I’m so sorry to be filling up all this space, but I am just trying to do this alone. It would be great if you could just look at this code and tell me how do I fill in all the fields? I am getting an error code:[19-Aug-2013 09:01:56] PHP Warning: include() [function.include]: Failed opening ‘includes/config.inc.php’ for inclusion (include_path=’.:/usr/lib64/php:/usr/share/pear’) in /home3/stratfc6/public_html/index_SMKX1RS9R6.php on line 3
    [19-Aug-2013 09:01:56] PHP Warning: mysql_connect() [function.mysql-connect]: Access denied for user ‘stratfc6’@’localhost’ (using password: NO) in /home3/stratfc6/public_html/index_SMKX1RS9R6.php on line 5
    [19-Aug-2013 09:01:56] PHP Fatal error: Access denied for user ‘stratfc6’@’localhost’ (using password: NO) in /home3/stratfc6/public_html/index_SMKX1RS9R6.php on line 6

    And it looks like it is coming from the config.php page.

    Mine looks like this right now, though I’ve tried various ways.

    array(
    ‘local’ => array(
    ‘name’ => ‘ckeditor’,
    ‘host’ => ‘127.0.0.1’,
    ‘user’ => ‘stratfc6_Uncle’,
    ‘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);

    For instance, what do I need to put for
    ‘database’,
    ‘local’,
    ‘name’,
    ‘host’

    etc. I have a feeling about some of them. But I tried what I thought each should be, but I still can’t seem to save anything when I hit the ajax save button. Can you tell me, is the ajax button supposed to be just plain with nothing on it when it is activated?

    Thank you for your help. I know you’re busy. Do you recommend that I try something completely different? Maybe paid support from ckeditor?

    • Hi Kris,

      It looks like the application is not finding the config file, be sure that you have the config file in a folder located in the root of your site named “includes”. Also, you will need to put the username and password that you use to log into cPanel in the config for production. You may also need to change the host to whatever the Hostname (located in the left sidebar of the cPanel main screen under Stats) is set to there as well.

      Also, yes, the ajax button is just plain… When I get a chance, I will add that part to the tutorial.

      I hope this helps!
      Bud

  8. hyper1 4 years ago

    Thank you, Sir.
    I made those changes but still it’s not saving. How about these fields? Can you tell me what I should put for them?
    ‘database’ => array(
    ‘local’ => array(
    ‘name’ => ‘ckeditor’
    production’ => array(
    ‘name’ => ‘ckeditor’,

  9. hyper1 4 years ago

    sorry, one more question: Do you mean that I should make the host name the same as in the cpanel in both the database side as well as the production side? And if I am supposed to replace the array with something else do I write it as:
    ‘database’ => name_of_database(
    ‘local’ => whatever_the_local_is(

    ‘production’ => whatever_the_production_name_is(

    or will those be placed inside of apostrophes and followed by a comma as the other fields below them?T

    Sorry I am attempting to do something I have not learned yet. I plan to learn MySQL and PHP, but I would just like to see if I can get this to work now.

    Kris

    • Hi Kris,

      Let me explain what that config does.

      $config = array( // database config
      ‘database’ => array( // this section uses the credentials for the local computer
      ‘local’ => array(
      ‘name’ => ‘ckeditor’,
      ‘host’ => ‘127.0.0.1’,
      ‘user’ => ”,
      ‘pass’ => ”’
      ),
      ‘production’ => array( // this uses the credentials for the remote server
      ‘name’ => ‘ckeditor’,
      ‘host’ => ‘127.0.0.1’, // you may need to put the hostname from your cPanel Stats column here
      ‘user’ => ”,
      ‘pass’ => ”
      ),
      ),
      );

      If developing this only on Bluehost, then you need not fill in the local account stuff. so your config would look like:

      $config = array( // database config
      ‘database’ => array(
      ‘production’ => array( // this uses the credentials for the remote server
      ‘name’ => ‘ckeditor’,
      ‘host’ => ‘127.0.0.1’, // you may need to put the hostname from your cPanel Stats column here
      ‘user’ => ”,
      ‘pass’ => ”
      ),
      ),
      );

      $host = $config[‘database’][‘production’][‘host’];
      $dbName = $config[‘database’][‘production’][‘name’];
      $dbUser = $config[‘database’][‘production’][‘user’];
      $dbPass = $config[‘database’][‘production’][‘pass’];

      Or, even simpler:

      $host = ‘127.0.0.1’;
      $dbName = ‘ckeditor’;
      $dbUser = ”;
      $dbPass = ”;

      I hope this helps you!

      Thanks,
      Bud

  10. hyper1 4 years ago

    Also, is 127.0.0.1 for the host in the database environment just a generic filler that I need to replace with my SQL IP address, or is that exact number required in the database environment?

  11. hyper1 4 years ago

    Sir,
    May I ask a favor? Could you just tell me what the entire page looks like when the changes are made to plugin.js and where do I find SQL file (ckeditor_db.sql)?

    Thank you very much.

  12. hyper1 4 years ago

    Also, I am a bit confused. This config.php page seems to be a little different than what I am learning about MySQL and php. I am very new to this, but I thought I was supposed to put the username and password of my database, NOT to log into my cpanel. Why do you say to use my user and password for my cpanel? Aren’t I trying to get the info to go into my MySQL database? And I thought the host was supposed to be localhost. These are the things that my host tells me I need to use to access my database.

  13. hyper1 4 years ago

    So, as I understand it you are saying that config should be written out like this:
    $config = array(
    ‘database’ => array(
    ‘local’ => array(
    ‘name’ => ‘ckeditor’, //not my database name?
    ‘host’ => ‘127.0.0.1’,
    ‘user’ => ‘nothing-I’ll just leave this blank?’,
    ‘pass’ => ‘nothing- i’ll just leave this blank?’,
    ),
    ‘production’ => array(
    ‘name’ => ‘ckeditor’,
    ‘host’ => ‘either my hostname from the left column in cpanel or 127.0.0.1’, (NOT localhost?)
    ‘user’ => ‘username that I log into cpanel, NOT to my database,
    ‘pass’ => ‘password that I log into cpanel’
    ),
    ),
    );

  14. hyper1 4 years ago

    Okay, I found the ckeditor_db.sql that you made. Do I need to alter it in any way? And do I just put it in my root folder on the same level as coeditor?

    Thank you for your time.

  15. hyper1 4 years ago

    By the way, I am developing my websites from a local computer. I have a local site folder and a remote site folder. Does this mean that I have to fill in the information for both the database and the production? If so, can you explain what needs to be put in to the database side?

  16. hyper1 4 years ago

    Dreamweaver keeps giving this message for my result.php page:
    Dynamically-related files could not be resolved because of an internal server error. Retry
    I try all sorts of different combinations of usernames and passwords in each environment, but can’t get it to connect to the server. The config.php page doesn’t give any errors, though.

    This is all I have for the result page:

    createMessage($message, $pdo);

    Also I bypassed the ajax button, because it doesn’t seem to be doing anything. I put in a button directly on the index.php page like this:

    and now at least when I click that it takes me to the result.php page. Is that what the ajax button is supposed to be doing? Howbeit, it is just a blank page, I guess because it is not connected to the server.

    I don’t understand how to incorporate the .sql page. I know where my phpmyAdmin section is in Bluehost. Am I supposed to manually create a table there according to the .sql document?

    Also, may I ask how do I incorporate this tutorial into a full already existing website? As it is I just see the editor with no content. Would I write the divs and section open tags before each form open tag and then write each div and/ or close section tag after each form close tag? Like:

    Hello
    This is the paragraph.

    When I do that, though, Dreamweaver highlights the h1 tags and p tags in yellow as if something is wrong with that.

    I apologize for writing so much but I am really trying to make this work.

Leave a reply