Skip to main content ↑ ↓
Topic: How to edit database field that reference multiple categorie separated by comma? (Read 1617 times) previous topic - next topic

How to edit database field that reference multiple categorie separated by comma?

Hello all,
I have a table that I cannot modify completely due to compatibility problems with some web applications.
This table contains stores information and for each store I can edit his categories.
The problem is here: categories.
All categories for a single store are "stored" (sorry) in a single field CAT_ID and they are comma separated.
Here an example; imagine the store table has 3 fields:
Code: [Select]
id_store
name_store
cat_id

The cat_id field contains all categories related to a store in this way: ,1,2,3,6,8,19, and so on...

So, the stores table would be:

id_storename_storecat_id
1store 1,1,2,3,6,9,19
2store 2,2,4,6,9,19,20
3store 31,2,3,4,5
While categories table would be:

cat_idcat_namecat_img
1category 1mycat1.jpg
2category 2mycat2.jpg
.........
999category 999mycat999.jpg
How can I manage this table relation with phprad?
is this possible without customizations?
TIA
tony

Re: How to edit database field that reference multiple categorie separated by comma?

Reply #1 –
Never mind,
I found it. I just edited the add category page and set is as checkbox using this query as data source:
Code: [Select]
"SELECT  DISTINCT id_cat AS value,cat_name AS label FROM categories ORDER BY id_cat ASC"

Now that I have the correct values in store table cat_id field, how can I display category names instead of category IDs in LIST and VIEW pages?
TIA
tony
 

Re: How to edit database field that reference multiple categorie separated by comma?

Reply #2 –
@sweetman‍ please use master details relationship or use the JOIN configuration in View and List Page Properties, thanks.

Re: How to edit database field that reference multiple categorie separated by comma?

Reply #3 –
Quote from: willvin –
@sweetman‍ please use master details relationship or use the JOIN configuration in View and List Page Properties, thanks.
Hall Willvin,
thanks for your reply.
I tried the master/detail relation but in the List or View  it will display only one category (not all categories).
How can I display all selected categories?
TIA