0
Undo
Votes
Hi, I need help with sql:
I want to reorganize my JEvents categories, the number is too large. But firstable I want to set some jev_tags in all events which have a special category.
For example: all events with the catid=160 should get the tag_id=15
Is there a way in phpmyadmin?
Thank you says
Jens.
I want to reorganize my JEvents categories, the number is too large. But firstable I want to set some jev_tags in all events which have a special category.
For example: all events with the catid=160 should get the tag_id=15
Is there a way in phpmyadmin?
Thank you says
Jens.
Hi Jens
Not sure why which post was missed I may be able to write some SQL to do this but need to check if you are using multi-category events?
Geraint
Not sure why which post was missed I may be able to write some SQL to do this but need to check if you are using multi-category events?
Geraint
JEvents Club members can get priority forum support at the Support Forum. As well as access to a variety of custom JEvents addons and benefits. Join the JEvents club today!
Try something like this
Explanation:
1. onjqe_ => my site/database prefix
2. 1 => the tag_id I am setting (taken from jev_tags table of the list of JEvents tags)
3. 8 => category id I want map to tag_id 1
I use REPLACE INTO instead of INSERT INTO to avoid duplicated entries
Backing up the jev_tageventsmap table before you start experimenting.
Good luck
REPLACE INTO onjqe_jev_tageventsmap SELECT catmap.evid, 1 FROM onjqe_jevents_catmap AS catmap WHERE catmap.catid = 8
Explanation:
1. onjqe_ => my site/database prefix
2. 1 => the tag_id I am setting (taken from jev_tags table of the list of JEvents tags)
3. 8 => category id I want map to tag_id 1
I use REPLACE INTO instead of INSERT INTO to avoid duplicated entries
Backing up the jev_tageventsmap table before you start experimenting.
Good luck
JEvents Club members can get priority forum support at the Support Forum. As well as access to a variety of custom JEvents addons and benefits. Join the JEvents club today!
Hi Geraint,
thank you for the code. When I use it the category field in the event is empty. What is this code doing? It should replace the old cat id with the new one, isn't it? But in the table jev_tageventsmap there is no field named evid.
Something seems to be wrong. But I'm really not an expert.
thank you for the code. When I use it the category field in the event is empty. What is this code doing? It should replace the old cat id with the new one, isn't it? But in the table jev_tageventsmap there is no field named evid.
Something seems to be wrong. But I'm really not an expert.
A typo - sorry.
The the select part fetches the event id of all events in category 8
The replace part inserts the values 1 and event id into the tags map columns tag_id and ev_id
REPLACE INTO onjqe_jev_tageventsmap SELECT 1, catmap.evid FROM onjqe_jevents_catmap AS catmap WHERE catmap.catid = 8
The the select part fetches the event id of all events in category 8
The replace part inserts the values 1 and event id into the tags map columns tag_id and ev_id
JEvents Club members can get priority forum support at the Support Forum. As well as access to a variety of custom JEvents addons and benefits. Join the JEvents club today!
can you give me access to phpMyAdmin to take a look for you?
JEvents Club members can get priority forum support at the Support Forum. As well as access to a variety of custom JEvents addons and benefits. Join the JEvents club today!
Are you wanting to change events from category 181 -> category 160 or to assign them a JEvents tag?
It its a tag which tag is it you want to assign to the evetns in category 181?
It its a tag which tag is it you want to assign to the evetns in category 181?
JEvents Club members can get priority forum support at the Support Forum. As well as access to a variety of custom JEvents addons and benefits. Join the JEvents club today!
That is a little more complicated
This query
SELECT c1.*, c2.catid FROM `sk8j3_jevents_catmap` as c1 INNER JOIN `sk8j3_jevents_catmap` as c2 on c1.evid=c2.evid where c1.catid=181 AND c2.catid=160
says there are 4 events in both already so I'll need to avoid duplicates.
I'll have time to do this tomorrow
This query
SELECT c1.*, c2.catid FROM `sk8j3_jevents_catmap` as c1 INNER JOIN `sk8j3_jevents_catmap` as c2 on c1.evid=c2.evid where c1.catid=181 AND c2.catid=160
says there are 4 events in both already so I'll need to avoid duplicates.
I'll have time to do this tomorrow
JEvents Club members can get priority forum support at the Support Forum. As well as access to a variety of custom JEvents addons and benefits. Join the JEvents club today!
should be sorted now.
JEvents Club members can get priority forum support at the Support Forum. As well as access to a variety of custom JEvents addons and benefits. Join the JEvents club today!
Two steps
1,
this gives the list of records that will cause a clash from step 2. in phpMyAdmin I removed the records where catid already exist for 160
2.
If you try step 2 before step 1 you may get an SQL error.
1,
SELECT c1.*, c2.catid FROM `sk8j3_jevents_catmap` as c1 INNER JOIN `sk8j3_jevents_catmap` as c2 on c1.evid=c2.evid where c1.catid=181 AND c2.catid=160
this gives the list of records that will cause a clash from step 2. in phpMyAdmin I removed the records where catid already exist for 160
2.
UPDATE `sk8j3_jevents_catmap` set catid=160 where catid=181
If you try step 2 before step 1 you may get an SQL error.
JEvents Club members can get priority forum support at the Support Forum. As well as access to a variety of custom JEvents addons and benefits. Join the JEvents club today!
Thank you for you help. I did for my next category both steps. After that I want do delete the old category - but it is not possible:
What can I do? In which table there are these events?
You are disabling a category with 287 events
Please deleted the associated events in this category first
What can I do? In which table there are these events?
Its possibible that the catid column of the #_jevents_event column needs to be updated too. Normally this is set at the same time as the category map table and represents the 'primary' category for the event.
JEvents Club members can get priority forum support at the Support Forum. As well as access to a variety of custom JEvents addons and benefits. Join the JEvents club today!
- Page :
- 1
There are no replies made for this post yet.