Search module build for CMSCanvas

Hello,

This is my first post in a CI forum and so I hope that I've asked this question in the right place. I need help determining the best approach to build a search module for a CMS built using CI 2.x.

In the CMS, pages (or entries) consists of many content types/field types; text field, checkboxes, File, etc. See example below:

Content_Types Table
id | Name | Type
1 | File | File_Upload
2 | Editor | Text

Entries_Data Table
id | field_id_2 | field_id_2 | field_id_1
1 | Lorem... | NULL         | NULL
2 | NULL      | My Lorem... | NULL

As you can see from the table above the searchable text can live in multiple fields. As a new entry type is created a new column is added to the Entries_Data table. This makes it almost impossible to do a FULLTEXT search. My thoughts were to create a new table (let's call it "Search") where the compiled content could live and be searchable like so:

Search Table
id | title | content |
1 | Welcome | Lorem... |
2 | NULL | My Lorem... |

In the above table "title", "content" could then be FULLTEXT to make the searching easier. But I am not sure if there is a better way to be doing this. Any advice given is appreciated.

I am working on a Fork of this project http://cmscanvas.com/

I had a similar problem where I had to search multiple fields on multiple tables.

In the end I went for doing each each search separately, then combining the result arrays into one big results array. I then reordered that array depending on a weighting criteria I made up. It works very well now.

Since all your data is in one table, surely you can create a query that can be done all at once using or_where.

Hello Paul, did you have to modify any columns (like making them FULLTEXT, etc.)? When you say "I went for doing each each search separately" I'm guessing that you mean do separate searches for each keyword?