Articles » Integrating FCKeditor With Your MySQL Database

Written by: Michael

This article is intended to address the lack of documentation in integrating the FCKeditor with your MySQL database. Their tutorials show how to configure and use the editor, but how to get it to save to your database isn't addressed at all. While this article will utilize the MySQL DBMS, the concepts are the same for any DBMS. On the same token, all my examples will assume your webserver is running a flavor of Linux and PHP 4.3.0 or higher, but the concepts will be the same on any webserver.

Step 1 - Install FCKeditor

We'll start off with a brief tutorial to get FCKeditor up and running on your website. First, download FCKeditor. After it finishes downloading, the quickest way to install it is to upload the .tar.gz file to your webserver and then unpack it there rather than upacking it on your computer and uploading the entire folder to your server which is several times larger and consists of hundreds of files. Once you upload the file, run the following commands:

cd /path/to/file
tar -xvzf FCKeditor_2.4.2.tar.gz

Next, move the folder to where you want all the files to be located. I always put them in my includes directory.

mv FCKeditor_2.4.2 /path/to/www/includes/FCKeditor

Step 2 - Put FCKeditor On A Webpage

Create the following skeleton webpage in your webroot directory (/path/to/www from the examples above):

File: index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Test FCKeditor</title>
</head>
<body>

<h1>Test FCKeditor</h1>

</body>
</html>

Now that we have our basic webpage up and running, we need to add the editor to it. First, we have to include the class file for the editor. Place this code at the very beginning of index.php:

<?php
include_once "includes/FCKeditor/fckeditor.php";
?>

Then, below the <h1> tag, insert the following code:

<?php
  $oFCKeditor = new FCKeditor('fcktext');
  $oFCKeditor->BasePath = "/includes/FCKeditor/";
  $oFCKeditor->Value    = "";
  $oFCKeditor->Width    = 540;
  $oFCKeditor->Height   = 400;
  echo $oFCKeditor->CreateHtml();
?>

Now refresh index.php in your web browser and you should have something that looks like this:

Article_fckeditor_image01

Voila, you've successfully installed and setup your very own WYSIWYG editor. Feel free to play around with it and test out the various options. The image browser won't work because we haven't set that up. For more information on how to configure it, see FCKeditor's website.

Step 3 - Saving To A Database

I'm sure you've noticed the save icon (

) in the editor. Sure would be nice if it actually did something like save the text you've typed in the editor, huh? Well that's actually really easy to do. First, we need a database. For this example, we'll use a very simple table. I'm going to assume you are able to create a MySQL database and that your PHP installation has the proper libraries installed to connect to it. If not, see MySQL's website for more information on that. To create the table, simply run the following query against your database:

CREATE TABLE `fck_data` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`data` TEXT NOT NULL
) ENGINE = MYISAM ;

That query will create a table called fck_data with 2 fields:

Field Name	Type	Description
id	integer	primary key
data	text	data from fckeditor

Next, we need to enclose the editor in a basic form so the data we enter can be submitted to the server:

<form action="index.php" method="post">
<?php
  $oFCKeditor = new FCKeditor('fcktext');
  $oFCKeditor->BasePath = "/includes/FCKeditor/";
  $oFCKeditor->Value    = "";
  $oFCKeditor->Width    = 540;
  $oFCKeditor->Height   = 400;
  echo $oFCKeditor->CreateHtml();
?>
<br />
<input type="hidden" name="submit_form" value="1" />
<input type="submit" value="Save Form" />
</form>

Now we need to handle the POST the form will make to the server. Since the action of our form is the same page that the form is on, we can just insert the following at the top of index.php:

<?php
  if ($_POST['submit_form'] == 1)  {
    // Save to the database

    // Redirect to self to get rid of the POST
    header("Location: index.php");
  }
?>

This if statement will be executed each time the form is submitted. The hidden attribute called submit_form in the form gets the value of 1 each time the user submits the form. The if statement looks for it in the $_POST array and if it's there, then we know the user submitted the form and we need to do something. At the end of the if statement, we use a call to the php function header() to redirect the page to get rid of the POST. What this accomplishes is that if the user refreshes the page after clicking submit, they are not prompted that the page has expired and they need to resubmit the form. The page will simply refresh as expected.

As for the actual saving to the database, it isn't quite this simple. There are a couple of other things I need to mention. First, when saving the data to the database, there are two possible actions: either we're inserting a new record or we're updating an existing one. Each of these has its own unique SQL query. Moreover, the first time we use this form and click save, we will need to insert the record and then every time after that we will need to fetch the record's contents, display them in the editor, update the record in the database if the user clicks submit, and then repeat.

So how do we accomplish this? Well there's actually numerous ways. For the sake of simplicity and to keep this article on course, I'm going to just manually create the record and then we only have to worry about updating the record. To create the first record, run this query on your database:

INSERT INTO fck_data SET id = 1, data = "";

Now that the database and table are setup, let's make a connection to it in our file. Place this code at the beginning of the file before the if statement:

<?php
  $cnx = mysql_connect("localhost", "username", "password")
         OR die("Unable to connect to database!");
  mysql_select_db("database_name", $cnx);
?>

And then at the end of the file, add this code:

<?php
mysql_close($cnx);
?>

Now we've got our script connected to the database and ready to start doing some updating. Before that, though, let's make sure our editor is automatically populated with data from the database. Modify the code that displays the editor to the following:

<?php
  $query = mysql_query("SELECT data FROM fck_data WHERE id = 1");
  $data = mysql_fetch_array($query);

  $oFCKeditor = new FCKeditor('fcktext');
  $oFCKeditor->BasePath = "/includes/FCKeditor/";
  $oFCKeditor->Value    = $data["data"];
  $oFCKeditor->Width    = 540;
  $oFCKeditor->Height   = 400;
  echo $oFCKeditor->CreateHtml();
?>

Two changes have been made. The first was the addition of the functions mysql_query and mysql_fetch_array. The first executes a query and returns a resource ID for the result set. The second fetches the first row from the result set and returns it as an array. This array is stored in the $data variable. The other change is to the line $oFCKeditor->Value = $data["data"];. That is where we populate the editor with the value from the database. The key "data" in the array is the field name from the table that stores the data from the editor.

Finally, we need a way to update the database with any modifications we make to the text in the editor. Since we already have our if statement ready to capture the POST data from the form, we just need to insert the following code into that if statement:

<?php
  $data = mysql_real_escape_string(trim($_POST['fcktext']));
  $res = mysql_query("UPDATE fck_data SET data = '".$data."' WHERE id = 1");

  if (!$res)
    die("Error saving the record!  Mysql said: ".mysql_error());
?>

The first line takes the text typed in the editor, trims it (which means it removes all leading and trailing white spaces), then adds any necessary escape characters so it can be safely inserted into the database without fear of sql injection. Now, assuming you've done everything right, you should have a working FCKeditor that saves your text to the database. To test it, type something into the editor and click save. Then close the browser and reopen it and your text should still be there.

Remember the save icon (

) from earlier? Clicking that icon generates a form submit, so you can also click that icon instead of clicking the save button below the editor. I know we've done a lot of work to the file, so below I have the full index.php file with all the updated code:

File: index.php

<?php

  // Connect to the database
  $cnx = mysql_connect("localhost", "username", "password")
         OR die("Unable to connect to database!");
  mysql_select_db("database_name", $cnx);


  if ($_POST['submit_form'] == 1)  {
    // Save to the database
    $data = mysql_real_escape_string(trim($_POST['fcktext']));
    $res = mysql_query("UPDATE fck_data SET data = '".$data."' WHERE id = 1");

    if (!$res)
      die("Error saving the record!  Mysql said: ".mysql_error());

    // Redirect to self to get rid of the POST
    header("Location: index.php");
  }

  include_once "includes/FCKeditor/fckeditor.php";
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Test FCKeditor</title>
</head>
<body>

<h1>Test FCKeditor</h1>

<form action="index.php" method="post">
<?php
  // Get data from the database
  $query = mysql_query("SELECT data FROM fck_data WHERE id = 1");
  $data = mysql_fetch_array($query);

  // Configure and output editor
  $oFCKeditor = new FCKeditor('fcktext');
  $oFCKeditor->BasePath = "/includes/FCKeditor/";
  $oFCKeditor->Value    = $data["data"];
  $oFCKeditor->Width    = 540;
  $oFCKeditor->Height   = 400;
  echo $oFCKeditor->CreateHtml();
?>
<br />
<input type="hidden" name="submit_form" value="1" />
<input type="submit" value="Save Form" />
</form>

</body>
</html>

<?php
  // Close the database connection
  mysql_close($cnx);
?>

Conclusion

This article was a very basic overview of how to save your text in the FCKeditor to your database. We assumed you would only be working with one record in the database the entire time, but that isn't very practical. With some additional coding, you could have a page that listed all the records in that table, and allowed the user to select one to modify or just add a whole new one. The possibilities are endless, but hopefully this article has given you the necessary foundation to expand these concepts into more complicated applications.

Comments

Pedro said on Feb 19, 2009 - 10:00 AM...

hello,
its helps me a lot. :)

i´m sorry my mistakes in type, because i´m from portugal

but i have a doubt.
i implemented in my website this script but just can edit one page, the ones who have the variable $data on database.

this tutorial shows how to work only with one record in the database.
how can i work with more records/pages?

Thanks,
Pedro

Robin said on Mar 15, 2009 - 12:20 AM...

Hello, thanks for your help.

i am in front of one error due to :

if ($_POST['submit_form'] == 1) {

it says : "Notice: Undefined index: submit_form in I:\Program Files\EasyPHP 2.0b1\www\index.php on line 9"

Do you know where it comes from???

thanks

Robin said on Mar 15, 2009 - 12:23 AM...

After some researches thanks google i found the solution to fix it,

just replace the line by :

if (isset($_POST['submit_form'])) {

siddharth said on Mar 16, 2009 - 2:33 AM...

Hi.. Thanks for your help.

This is working fine for inserting and retrieving data from database.
But one problem I am facing is, its not displaying the edited text color after saving. Its displaying the default black color of the text.

Please help me how to fix this..

Thanks & Regards,
Siddharth

Ahmad Abubakr said on Apr 20, 2009 - 3:27 AM...

Hey,

Thanks for this article it is very practical and get thing up and running. Thank you again

Yours

amber gifts said on May 4, 2009 - 11:25 AM...

informstive and helpful article

faizan said on May 5, 2009 - 7:00 AM...

for colur in out put put this code in where u make file after fopen

$string = str_replace("\"","", $string);
$string = str_replace("\\","\"", $string);

this code solve your problem...

Nihat said on May 19, 2009 - 8:22 PM...

Hi Michael, Thank you for a very usefull article. Would you mind of explaning of the colur issue more clearly.

Thank you.

4l3x said on Jun 10, 2009 - 2:44 PM...

Thanks Michael for this great howto!

To Nihat: What faizan means (i guess) is :

$data["data"] = str_replace("\"","", $data["data"]);
$data["data"] = str_replace("\\","\"", $data["data"]);

added after the line :

$data = mysql_fetch_array($query);

It worked for me!

Pedro said on Jun 19, 2009 - 8:34 AM...

413x and nihat:
thanks alot for solving this problem My problem was not only the color letters, it whas basicly everything that wasn't a simple text. I needed to put pictures, links, etc.. and it gave me he code full of "/" and never showed up like it was ment to, but whith this fix, everything is working fine.

thanks guys.....

Salim said on Jun 25, 2009 - 6:22 AM...

Oh it was very much helpful for me.

Is there any way to hide some component

gary_h said on Jun 25, 2009 - 1:12 PM...

Rather than str_replace, try stripslashes().
As in:

$data['data'] = stripslashes($data['data']);

gary_h said on Jun 25, 2009 - 1:23 PM...

Salim, what component would you like to hide, the Toolbar icons?

If so then edit the fckeditor/fckconfig.js file by adding a ToolbarSets with a unique name (under the Default and Basic ones):

FCKConfig.ToolbarSets["Salim"] = [
    ['Source','-','Save','NewPage','Preview'],
    ['Cut','Copy','Paste','PasteText','PasteWord','-','Print','SpellCheck'],
    ['Undo','Redo','-','Find','Replace','-','SelectAll','RemoveFormat'],
    '/',
    ['Bold','Italic','Underline','StrikeThrough','-','Subscript','Superscript'],
    ['OrderedList','UnorderedList','-','Outdent','Indent','Blockquote'],
    ['JustifyLeft','JustifyCenter','JustifyRight','JustifyFull'],
    ['Rule','Smiley'],
    '/',
    ['FontName','FontSize'],
    ['TextColor','BGColor'],
    ['FitWindow']      // No comma for the last row.
] ;

You can add/substract whatever features suit you.
Then add a line to the script like this:

$oFCKeditor = new FCKeditor('fcktext');
  $oFCKeditor->BasePath = "/includes/FCKeditor/";
  $oFCKeditor->Value    = $data["data"];
  $oFCKeditor->Width    = 540;
  $oFCKeditor->Height   = 400;
  $oFCKeditor->ToolbarSet = 'Salim';
  echo $oFCKeditor->CreateHtml();

If that's not what you're talking about, then nevermind.

Ady65 said on Jul 8, 2009 - 2:57 AM...

Great tutorial. I'm currently building a news section to my site and need the function to add, edit and delete an article. Is there a way to adapt this code so I can add new articles and then have access to edit them. Also is it possible to have more than one entry field but only one submit button that uploads all the entry fields. I have a form with four fields but don't want a button at the end of each one. If been trying without success to achieve this. Any help or pointers in the right direction would be gratefully recieved. Thanks

symon00 said on Aug 13, 2009 - 9:03 AM...

Hi!
Very good article! Thank you very much!
symon00

ns31 said on Oct 19, 2009 - 11:18 AM...

really useful article - have been browsing the web for a while now trying to find something so clear. superb thanks

cancel said on Oct 19, 2009 - 2:05 PM...

great one :)

cheers ;)

nju said on Oct 22, 2009 - 8:29 PM...

Anyone knows how to remove the fckeditor and display the content we typed in on the same web page?

Thanks in advance.

Ray Komar said on Apr 1, 2010 - 6:34 PM...

Thanks for the info.. I was able to create an update page for a jobs site for a friend with the site info.

I just pulled it straight from the database with a select * query and used this

$oFCKeditor->Value = $myrow["Jobpost"] ;

hope that helps someone...