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
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
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.
interactivetools.com
Re: [Dave] List: - Get Options from Database
By InHouse - February 22, 2008
Thanks for pointing me in the right direction. I'm getting there now. The list menu now recogizes my database table (it did already have the cms_prefix) and that works well.
Still some problems at the other end though. The section viewer chucks a wobbly and can't read the list of options for a drop down menu I'm using. Still, I think I'm on the right track. There may be something I missed the in schema.
After all, schemas and single malts don't mix.
Jayme
Re: [InHouse] List: - Get Options from Database
By Dave - February 22, 2008
interactivetools.com
Re: [Dave] List: - Get Options from Database
By InHouse - February 22, 2008
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
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.
interactivetools.com
Re: [Dave] List: - Get Options from Database
By InHouse - March 12, 2008
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
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.
interactivetools.com
Re: [Dave] List: - Get Options from Database
By InHouse - March 17, 2008
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
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.
interactivetools.com