Home Articles Portfolio References Contact
 

Articles » Integrating FCKeditor With Your MySQL Database

Written by: Michael Bookmark and Share

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:

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

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

[CODE]mv FCKeditor_2.4.2 /path/to/www/includes/FCKeditor[/CODE]

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
[CODE]<!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>[/CODE]

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:

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

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

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

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



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:

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

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:

[CODE]<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>
[/CODE]

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:

[CODE]<?php
  if ($_POST['submit_form'] == 1)  {
    // Save to the database
       
    // Redirect to self to get rid of the POST
    header("Location: index.php");
  }
?>
[/CODE]

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:

[CODE]INSERT INTO fck_data SET id = 1, data = "";[/CODE]

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:

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

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

[CODE]<?php
  mysql_close($cnx);
?>
[/CODE]

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:

[CODE]<?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();
?>
[/CODE]

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:

[CODE]<?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());
?>
[/CODE]

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
[CODE]<?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);
?>
[/CODE]

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.