Page 1 of 1

Looping through TABLES for given field name not records

Posted: Sat Sep 18, 2010 7:13 pm
by Borojimmy
this might sound like a weird question but the answer would make my life a whole lot easier.

I am writing a CMS and certain MySQL tables are linked to PHP classes (as the norm), but what I want to do is to add a particular 'Flag' to certain tables to show that the records within can be used to build menus, so 'Pages' can be a menu, 'Users' can be a sub menu 'products' can be a menu, but 'user_address' wouldnt, once I can see that a table could contain a record named 'is_menu'

so it would be something like:

SHOW TABLES WHERE COLUMN LIKE %is_menu%

Ive tried using %is_menu% , vis_menu% & is_menu and none give a return value (using sing;e quotes of course!),

any help would be apprectiated.


Jimmy

Re: Looping through TABLES for given field name not records

Posted: Sat Sep 18, 2010 7:18 pm
by Eran
I would suggest avoiding this approach. You are trying to add meta-data about a table (should it be in a menu) on a column level. This means every row in those tables would contain this information. A better approach would be to write the query that builds the menu from the tables that participate - ie. have that logic in code and not in the database (where it has no natural place).

Re: Looping through TABLES for given field name not records

Posted: Sat Sep 18, 2010 9:04 pm
by califdon
I agree with pytrin, but if you need to have this information, the logical place to store this info is another table that simply has a record for each table that can be a menu. Then you can search that table to determine whether another table is in that category. Of course, this requires that this meta-table must be maintained as you make changes in your schema that affect those conditions.