Is there a way to get a list of cms tables that already exist?

3 posts by 2 authors in: Forums > CMS Builder
Last Post: September 18, 2012   (RSS)

By garyhoffmann - September 17, 2012

I've been doing this for several years now with text fields, but would like to use a drop-down if it's possible. Keep in mind, this is all referencing the admin area - I already have it all worked out in the presentation of the site.

Our scenario: I have a "form information record" which includes general information about the form (form name, what should happen when a user submits it, etc.). I also create a section called form fields in which the user lays out their form (record with keys of FirstName, LastName, etc.).

In my form info section, I have a field called form_fields_section_name in which the user has to type the name of the table (minus the cms_ prefix).

I'm wondering if there is a way to get a drop down list of all the tables in the database - I could use SHOW TABLES LIKE 'cms_%' but then I get the cms_ portion of the name back. I'm wondering if there is a "cms friendly" way to get the table names back?

Thanks,
Gary.

Re: [garyhoffmann] Is there a way to get a list of cms tables that already exist?

By gregThomas - September 18, 2012 - edited: September 18, 2012

Hi Gary,

This is a good question, and we've found a solution to it! If you use this MySQL code for the get options from MySQL query option on the list field type:

SELECT REPLACE(TABLE_NAME, '<?php echo $TABLE_PREFIX;?>', '') FROM information_schema.`TABLES` where TABLE_SCHEMA = '<?php echo $GLOBALS['SETTINGS']['mysql']['database'];?>' AND TABLE_NAME like '<?php echo $TABLE_PREFIX;?>_%'

This should get the all of the table names from the information_schema table, and uses the MySQL replace function to remove the 'cms_' part of the table name in the results. It should all be dynamic so you shouldn't have to change anything in the code above.

Thanks
Greg Thomas







PHP Programmer - interactivetools.com