Mudjungle

Well maybe you like me, were trying to store images or raw data like files directly in the database but that is not a good idea because store raw data or binary data inside a database is really expensive in terms of performance and could make the queries to the database's tables really slow.

So you decided like me, just to store a reference to the file in the database and keep the file in some specific folder. Let's see a example:

+--------------+
|   stories    |
+--------------+
|   storyid    |
|  categoryid  |
|    title     |
|   content    |
|   picture    |
|   created    |
|   modified   |
|  published   |
+--------------+

In the above table we have a basic schema that could be useful to store maybe blog entries with title, main story and a picture. Well the picture field could be a VARCHAR(255) and in that field we could store the file data reference like: 'story_images/some_picture.jpg'

In that way we could easily fetch the story and just put in the "src" attribute of the HTML img element the "picture" field of the story that contains the url or path of the file.

Ok, but now the main point of interest of this blog entry is what happens when I wish to delete the story record? well one thing for sure is that I have to delete the record in the database and also delete the file (the actual picture, raw data or binary data) in the server. But now what is first the egg or the chicken? let's see what could happen:

1. The query to delete the record in the db fails and also the delete (unlink) of the file fails (maybe the script that is trying to delete the file doesn't have enough permissions). Well in this case we are like in the beginning it's like nothing have happened (not bad not good).
2. The query to delete the record in the db fails, but now the delete of the file have been successful. This scenario is a bad one because this could lead to inconsistency in the data, because the db record now references a file that doesn't exist anymore.
3. The query to delete the record have been successful, but the delete of the file have failed. This scenario is not that good, but now at least for the end user it's like everything is ok, because he can't see the db record anymore (maybe when listing the whole set of blog stories)
4. Both, the query to delete the record and the delete of the file have been successful. Everybody is happy here :)

So in conclusion, try to delete the db record first, if the query execution was successful then delete the file (referenced data).
 

May 13, 2008 - Update: I added a fully functional example. In that way you can use the example files instead of copying text from the previous links (Like Peter Briers said in a comment, copy and paste results in not readable scripts). I hope it could be useful.

When I started this blog I said that I wasn't sure about what I'm going to write, and one of the things that I have in mind is well...Web Development my current job, but I wasn't sure that my job or some of my code were really helpful beyond what you can find in some programming language manual like the excellent PHP manual, but maybe this time I can help or improve in some way the work started for other guys.

Well before I begin I would like to say that this JavaScript file is in 99.9% not mine, I just add some things that hopefully would make it better. Some things that I added are:

• Added a custom div container to store all the upload functionality (label, input button, upload button)
• Keep the style separated in a external CSS file
• Added a YUI button to make the upload
• Added YUI JSON utility to parse the server response
• Added custom status messages (errors)
• Change the name of some variables to suit the examples in the YUI documentation

And so the script is: yui_image_uploader (remember to copy the code and paste in a new file, name the file yui-image-uploader.js)

I also modified the PHP script that handles the upload in the server side, this one is very different to the original one and I think that probably you will need PHP 5.2.0 and up to run it well.

Here it is: yui_image_uploader (remember to copy the code and paste in a new file, name the file yui_image_uploader.php)

Finally the CSS that you need to make the upload extension look nice is: yui_image_uploader

And here is some example that you can use: yui_image_uploader_example (remember that you need to include the JavaScript YUI libraries like yahoo-dom-event, element, container-core, menu, button, connection, json, editor and our upload extension: yui-image-uploader.js)

Finally remember that I said that this work is 0.01% mine, well the 99.9% is by Dennis Muhlestein, Thanks a lot Dennis.

Here is a picture of the Yui editor (with upload extension) in action:

* tested in IE 7 and Mozilla Firefox 2

Fully functional example: yui_image_uploader

Let's see a basic pagination in PHP:

Download Source Code: pagination

Adapted from the book: MySQL Cookbook, Second Edition by Paul DuBois

It's a natural and popular way to search for information and it's easy to use: enter keywords and get what you need. Let's see some features:

• Available only for MyISAM tables.
• Natural language search and boolean search.

Creating a Full Text Index

CREATE TABLE articles (
    articleid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content)
) engine=MyISAM;

The articles table is a normal MySQL table, but the SQL in bold is what really matter now. Between the round brackets you specify the fields that you want the full text index applied. The fields data type have to be VARCHAR or TEXT. Also make sure which fields you want to apply the full text index, because later when you do the actual search query you must specify the same number of fields in the query, if you specify two fields (like the example) you make the search query with two fields. Let's see another example:

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('$searchterm')

Notice the text in purple is the actual text (maybe submitted from a form and processed by a PHP script).

I hope it could be useful.

DB SCHEMA (Updated: June 15, 2008)

Here it's the Mudjungle Database Schema: (FIGURE 1)

FIGURE 1  The Mudjungle Db schema.

There is some thoughts about this design, let me explain a few ones:

• After try and use some excellent PHP Projects like Gallery and Wordpress, I noticed that their name convention for the tables is adding a prefix like g2_ for Gallery and wp_ for Wordpress. I think this is important because in that way you can embed your application with another one and you'll avoid conflict between table's names. In that fashion the Mudjungle db tables start with the prefix mj_, this prefix reminds me a lot Mary Jane Watson from Spiderman, LOL.
• Like you probably know our RDBMS is MySQL, then the engine used for the tables is MyISAM, mainly because we use Full text search functionalities in the project.
• We used MySQL Workbench to make this DB Design.

RECENT UPDATES

June 15, 2008

Added some changes to the table mj_users:

• the username field length was reduced to 16 chars
• the password field type and length was changed to char and 40 chars respectively
• the email field length was changed to 100 chars. Also now the email field is required (Not Null)

June 06, 2008

We added two new tables: mj_appearance and mj_menu, also the table mj_users_courses has been modified and now it has a new field called: last_lesson. With this field we are trying to implement a way of storing the last lesson read or studied by the user.

May 20, 2008

The thumbnail field has been added to the table mj_courses. This field will help us to reduce the load at the time of displaying the courses thumbnails. This field and the companion reduced image is suggested to be created in admin time or at the time of create or modify the course.

May 08, 2008

• Change the name of the table mj_resources to mj_lesson_resources. Also removed the image item in the type ENUM field.
• Added the table mj_course_images, this table will allow us to share images and pictures between the different courses. Also it was necessary to add it, because at the time of adding a new lesson we need to store the uploaded images in some place.

SOURCE FILES (Updated: June 06, 2008)

DB Schema image: mudjungle_db_schema

DB Create SQL: mudjungle_db

We were working for a few days in the Mudjungle course organization. We've taken ideas from Juliux courses and adapted some for our own needs. We think the following organization fulfill the project needs (FIGURE 1).

FIGURE 1 Mudjungle project organization.

Feel free to comment on this, because we are not sure at all.

I post this a little time ago in another Web page, but just to have everything in one place. I think that maybe some people out there are thinking in develop the next Web 2.0 Application but they just don't know what software to use or to learn, well maybe this could be a list of some of the nicest programs out there that if you master those programs you'll be one of the best Web developers out there, everybody in your neighborhood would like to be like you. Here is the list:

• The Web Server
• The Database
• The Programming Language
• The Text Editor

And for the aesthetics of the Web site, we recommend:

• The Bitmap Graphics Program
• The Vector Graphics Program

After spending some time trying to figure it out the right xhtml structure for the project we came with the following (FIGURE 1).

FIGURE 1 XHTML Skeleton.

We think that this skeleton solves our needs and offers enough semantic meaning in the id's of the div containers. It was achieved using the Yahoo User Interface (YUI) CSS library (so you know now why some of the id or class attributes start with "yui"). Some things important to mention are:

• The main container or wrapper haves an id attribute with value doc3 (defined by YUI) this special value means that the layout is going to use the 100% of the width of the browser window, so we easily have a liquid layout. If we wanted to use a fixed layout like 750px width, changing the value of the id attribute will solve that.
• The main peaces of the skeleton are the header, the body and the footer, denoted with hd, bd, ft respectively. These id's are defined by YUI too.
• In the body container (bd) we have two columns defined by #yui-main .yui-b and .yui-b so here we can add or own custom containers.
• In the first div container with class yui-b, I added my custom container with id my_container, so in successive web pages or PHP scripts we can change this container to suit our needs, maybe changing the id so it makes more sense in what the web page or PHP script is about.
• I also added another container with id my_menu, like you may think this is where I could add some custom menus or something in the narrow left column. I was thinking in add here a new container that will hold the authentication form.

See ya next time.

We were just trying to add search capabilities so the people could search through the list of courses already in the LMS. First in MySQL the tables in which the full text search had to be done, needs to be of a specific engine called MyISAM, MyISAM is the default engine when you don't specify any in the creation of the table, but is that true...

Well I don't know if I mis-configured something in my Windows MySQL installation, but when I tried to add the Full text search indexes to the courses table, it throws an error. So the solution for that was to specify the engine type in the script SQL that create the tables (see it on the column of the right in the section "important files").

I think the search functionality in the LMS needs to be done in that way, just to accelerate the development of it. And I also think that the search functionality could be described in just a few words:

SELECT ... FROM table_name WHERE MATCH(column1, column2) AGAINST ('search string');

This idea and the reference code for develop the above mentioned functionality, was taken (or it's going to be taken) of the nice book: MySQL Cookbook 2nd Edition

Ok, let's see how we are organizing the project based in a previous post article: (FIGURE 1)

FIGURE 1  The Mudjungle project organization (directory structure).