List: - Get Options from Database

10 posts by 2 authors in: Forums > CMS Builder
Last Post: March 17, 2008   (RSS)

By InHouse - February 21, 2008

Really diving into CMSB now and enjoying it. However, I've run into an odd snag.

Situation:
I have manually created a table in my DB where I want to list something I'm calling 'Categories' (has 1 int field and 1 VarChar field). In that VarChar field I've listed the areas of my site: Nutrition, Smoke-Free Living, Workplace Wellness, etc...

The Goal:
I planned on making a single multi-page section editor for each type of post (tips, recipes, links, etc.) and add the field 'Category' to each. The goal was to choose a Category from the drop-down list when I fill in the content of a posting. That way I have only one collection of, for example, 'tips' with a sub-classification by category to manage. The code on the PHP page would use the Advanced record search to find only the applicable posts that belong to that Category.

Problem:
The Section Editor does not recognize my manually created table. Any ideas what I might be doing wrong? Do I need to register this table with a master list somewhere else in the database?

Any help would be greatfully appreciated.

J. in London, ON

Re: [InHouse] List: - Get Options from Database

By Dave - February 21, 2008

Hi InHouse,

The "Section Editors" menu will only list tables that start with the defined "table prefix" which is usually "cms_".

Then you should be able to "modify" it and click save to save a schema for it automatically in /data/schema/.

You can also force CMS Builder to use a specific column type by manually adding a key called "customColumnType" to the schema ini file. See /data/schema/uploads.ini.php for an example. This lets you manage a table with CMS Builder but have different column types.

Using manually created SQL tables isn't a well tested part of CMS Builder, but should be possible. Let me know if you run into any other problems or have any other questions and I'll be glad to help.
Dave Edis - Senior Developer
interactivetools.com

Re: [InHouse] List: - Get Options from Database

By Dave - February 22, 2008

Let me know if you get stuck with that. You can send me the schema for your database and I can give it a try locally.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] List: - Get Options from Database

By InHouse - February 22, 2008

Are you offering to help with the code or the scotch? [;)] You're welcome either way but you'll have to travel a bit to the east to help with one of them.

On more serious notes, I've drawn a blank on this issue. I can get the Section Editors to behave properly but I think my schema is fouled up. It even lead to some dire messages about the site being overworked from CMSB. Glad to have those catches in the system though!

If you're up for some experimentation, here's my very simple table:

-- Table structure for table `cms_categories`
--

CREATE TABLE IF NOT EXISTS `cms_categories` (
`id` int(10) unsigned NOT NULL auto_increment,
`categoryName` varchar(30) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `cms_categories`
--

INSERT INTO `cms_categories` (`id`, `categoryName`) VALUES
(1, 'Nutrition'),
(2, 'Workplace Wellness'),
(3, 'Smoke-Free Living'),
(4, 'In Motion'),
(5, 'Speical Projects');
If you can show me how this schema should look I would be very greatful.

Cheers,
J.

Re: [InHouse] List: - Get Options from Database

By Dave - February 25, 2008

Ok, this one took a little bit of research.

Try updating your /data/schema/categories.ini.php file with this:

;<?php die('This is not a program file.'); exit; ?>

menuType = "multi"
menuName = "Categories"
listPageFields = "categoryName"
listPageOrder = "categoryName"
listPageSearchFields = "categoryName"
menuOrder = ""
_disableErase = 0
_disableAdd = 0
_maxRecords = ""
menuHidden = 1

[id]
order = 1
type = "none"
label = "Record Number"
isSystemField = 1
customColumnType = "int(10) unsigned NOT NULL auto_increment"

[categoryName]
order = 2
label = "category Name"
type = "none"
isSystemField = 1
customColumnType = "varchar(30) default NULL"

You may already have that. That will give it all the correct info to work for the pulldown menus, and hide it from the menu (see "menuHidden" option above). If you want to be able to edit your categories section from the menu you'll need to use 'num' instead of 'id' as the program is hardcoded to use 'num'.

In future it might be easier to create the menu through the program, add the customColumnType fields to the ini, then modify/save each field to have the program change the column type for you. It's not the most straightforward process. We haven't had anyone ask about changing the column type yet (although we do it ourselves sometimes).

Is having the index field named 'id' a requirement for another program you're using, or can it be named 'num'?

Hope that helps. Let me know how it goes.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] List: - Get Options from Database

By InHouse - March 12, 2008

Well, all was happy and working properly until the upgrade to v.1.10. Now I can't access the Admin area. I get the following error message:

Error adding fields to 'cms_categories', the error was: Incorrect table definition; there can be only one auto column and it must be defined as a key


The front end seems to be working well.

I've checked the database schema and all seems to conform. Certainly nothing has changed. The custom file at .../data/schema/categories.ini.php matches the info also.

Any ideas?
Jayme

Re: [InHouse] List: - Get Options from Database

By Dave - March 12, 2008

Oh no! Sorry about that.

Version 1.10 adds some new fields: createdByUserNum, createdDate, updatedByUserNum, and updatedDate. It's probably trying to add those (as well as auto_increment num field) to categories.

Try temporarily removing (or moving) categories.ini.php out of the /schema/ directory. Then run the upgrade, then put it back, and you should be fine.

Let me know if you still have errors or problems after that and I'll help you sort it out if needed.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] List: - Get Options from Database

By InHouse - March 17, 2008

Just an update. Many thanks Dave. I've done as you suggested and it's back up and running again. Also had to go through the code where the old language for the datestamps was used.

All is well again, though I'm a little nervous about having to perhaps do this every time a new update is released. Any ideas is this is likely to be an ongoing concern?

Best wishes,
Jayme

Re: [InHouse] List: - Get Options from Database

By Dave - March 17, 2008

Not likely an ongoing concern, no. Version 1.10 was the first time we needed to add fields to existing CMS Builder tables so we had something to check for the improved user access controls (createdByUserNum is used as the 'owner' id).

One thing you could do to be extra safe is just remove that schema file before upgrading (then CMS Builder won't know it exists) and then put it back. But many upgrades don't

But I don't see it happening again, feel free to post before upgrading to future version and I can always double check for you.

Hope that helps.
Dave Edis - Senior Developer
interactivetools.com