help needed populating a list field from a list field in a different table
5 posts by 2 authors in: Forums > CMS Builder
Last Post: August 9, 2017 (RSS)
By willydoit - August 1, 2017 - edited: August 2, 2017
Hi all,
As this makes for complicated reading I have colour coded as follows: keyword tables field names
in an attempt to minimise the number of list and detail pages used within a website I have created a table called information_pages which contains information on various topics such as places to visit, car park locations etc. within the table I have a list field called "category" comprising of options such as "car Parks" "place of interest" etc and each record in the table selects its relevance type from this list.
The records are called to a list page from a menu using variations on the following;
information-list.php?category=Place of Interest
This all works fine, however, as I am using a single listing page to display information relating to a number of different subjects I need to be able to supply meta tags and introduction text to the page that is relevant to the subject in question if that makes sense.
Ie If the search criteria is category=places of interest I want it to produce a list of matching entries from the "information_pages" table and at the same time populate the metatags and heading text from the second table from a record that also has a "category" field matching places of interest.
I assumed the best way of achieving this would be to create a second table called information_section_headers containing a record for each category type with each record containing fields to populate the metatags and an introduction area on the listing page and then loading both tables when loading the listing page.
The idea is that both tables will be searched for content where the field "category" matches the required subject.
To this end both tables will have a field called "category" and to ensure matching content I want the "category" list field in the table "information_section_headers" to populate itself using the content from the "category" field in the "information_pages" table. Hopefully this still makes sense.
Unfortunately while I can start setting up the category field in the second table and get as far as the dropdown box to select the table to be used to import from I have no idea what code is needed to replace the default entries in the MySQL Where box or the More Search link box.
If anyone can advise as to what I need in there to populate the field with the list content in the "category" field of the first table it would be appreciated.
Apologies for taking so much time to explain what is probably a simple question but it isnt easy when you dont have a clue what you are talking about. :-)
By Dave - August 8, 2017
Hi willydoit,
Did you get this sorted out? If not, try this:
- Create a new section called: Categories
- Only have these fields in it: num, name
- For any sections that you want these categories in, create a list field with: Get options from database (advanced)
- Select the category table as the Tablename, num for "values" and name for "labels"
Let me know if that works for you.
Note: I'm using "num" as the stored value, which means you'll need to look up categories like this: category=4, you can use text if you want. The benefits of using record numbers is that even if your text changes the records are still linked.
interactivetools.com
By willydoit - August 9, 2017
Hi Dave,
thanks for the help but I still cannot get this working but I know it is likely to be something simple caused by my lack of php and mysql knowledge.
I have created a new section called category list
it contains the pre created num field and I have added a text field called title
I have created two test records in the category list section.
I have then created a field in my section information pages called Category List
I have selected field type Related Records
In the related table I have selected categories_list
List Actions I have ticked view
And this is where I come unstuck,
I have changed the MySQL Where element to num='<?php echo mysql_escape(@$RECORD['num']) ?>'
I have also tried changing it to
title='<?php echo mysql_escape(@$RECORD['num']) ?>'
I have left the More "Search" Link as the default entry.
When I go to edit or create a record in the information pages table this field shows "Sorry, no records found!" if I click on see related records it goes to the correct table and I can see the two test records but they dont populate the list as I assumed they would.
I obviously havent grasped what is supposed to be happening and what it is actually doing. In case it helps I have uploaded screen dumps showing settings etc.
By Dave - August 9, 2017
Hi willydoit,
Instead of a related records field, can you try creating a list field?
- For any sections that you want these categories in, create a list field with: Get options from database (advanced)
- Select the category table as the Tablename, num for "values" and name for "labels"
Let me know if that works for you.
interactivetools.com
By willydoit - August 9, 2017
Hi Dave,
that sorted it, thanks, that,s opened up a whole new world of possibilities now. Thanks again for your patience, it is appreciated.