Need help for a SQL request

More
4 years 9 months ago #76704 by bendeb
Hello,
I need some help please with a SQL request.
I have to edit many items on my website (approximatively 6000), so I intend to do it with phpmyadmin rather than edit each item one by one. :silly:
But I'm a big noob with SQL so I come here to explain my problem (with my owns ID and example in parenthesis).

I created a new field (ID 602) and affect it to a type (ID 6). It's a selectlist field with some values.

I want to put the same value in field 602 (for example : "myvalue") to many items, but only items affected on a specific category (ID 56).

How can I solve this problem please ?

Thank in advance for your help. :)

Regards.

Flexicontent 4.1.0b1
Joomla 3.9.18

Please Log in or Create an account to join the conversation.

More
4 years 9 months ago - 4 years 9 months ago #76712 by ggppdk
Replied by ggppdk on topic Need help for a SQL request
Hello

after / if you run the SQL query successfully,
- 1. you will need to recreate the search indexes
- 2. If you are using versioning and in future you should not restore this particular version, it will restore other value

typically i would run this query to a copy of the original database to make sure it has the desired results

So it should be something like (i have not tested it)
but i probably made some mistake below
Code:
UPDATE v SET v.value = 'myvalue' FROM `PPP_flexicontent_fields_item_relations` AS v INNER JOIN `PPP_content` AS c ON v.item_id = c.`id` AND c.catid = 56 INNER JOIN `PPP_flexicontent_items_ext` AS e ON v.item_id = e.`item_id ` AND e.type_id = 6 WHERE v.field_id = 610


-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...
Last edit: 4 years 9 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

More
4 years 9 months ago - 4 years 9 months ago #76716 by bendeb
Replied by bendeb on topic Need help for a SQL request
Thank you ggppdk,

It seems that's not working. I replace PPP by my table prefix obviously (and ID 610 by 602 too ;) ).
And I tried it on a test database :)

Here is the phpmyadmin error logs when I execute the request :

Erreur

Requête SQL :
Code:
UPDATE v SET v.value = 'Générique' FROM `xxx_flexicontent_fields_item_relations` AS v INNER JOIN `xxx_content` AS c ON v.item_id = c.`id` AND c.catid = 56 INNER JOIN `xxx_flexicontent_items_ext` AS e ON v.item_id = e.`item_id` AND e.type_id = 6 WHERE v.field_id = 602


MySQL a répondu : Documentation
#1064 - Erreur de syntaxe près de 'FROM `xxx_flexicontent_fields_item_relations` AS v

INNER JOIN `xxx_con' à la ligne 3


Thank for your help.

Flexicontent 4.1.0b1
Joomla 3.9.18
Last edit: 4 years 9 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

More
4 years 9 months ago #76717 by ggppdk
Replied by ggppdk on topic Need help for a SQL request
Hello

ok the syntax for MySQL is a little different in order
try:
Code:
UPDATE `jos_flexicontent_fields_item_relations` AS v INNER JOIN `jos_content` AS c ON v.item_id = c.`id` AND c.catid = 56 INNER JOIN `jos_flexicontent_items_ext` AS e ON v.item_id = e.`item_id` AND e.type_id = 6 SET v.value = 'Générique' WHERE v.field_id = 602


-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...

Please Log in or Create an account to join the conversation.

More
4 years 9 months ago #76718 by bendeb
Replied by bendeb on topic Need help for a SQL request
It's not working anymore, but this time I have no error message.
Though, the request return 0 line.

I see that you use the table "_content" to determine catid, but why don't you use "_flexicontent_cats_item_relations" instead ?
And maybe it's my fault because I have no described the problem correctly.
I think type_id 6 is not important.
The only thing we need is to affect a value in a field (602) on all items that are in catid 56. So type_id is not important ?

Thank again for your help.

Flexicontent 4.1.0b1
Joomla 3.9.18

Please Log in or Create an account to join the conversation.

More
4 years 9 months ago - 4 years 9 months ago #76731 by bendeb
Replied by bendeb on topic Need help for a SQL request
Hello,

I try many things, modifying some parts of your request, changing some things by others, but I can't solve this simple problem. :(

I have phpmyadmin and mysql.
There are two tables : one for fields/item relations and one for categories/item relations (either xxx_content or xxx _flexicontent_cats_item_relations, what the difference between the two ?)

I repeat what I want, because maybe I'm not clear with my english, sorry :)
So, for all my items that are in category 56, I want to have 'Générique' in the column 'value' of field_id 602 (it's a select list field).
Field_id 602 is obviously affected to items that are in category 56. But for now, this field is empty.

On the paper, this request seems very simple. But in fact, not. :D

Thank you for your help.

Flexicontent 4.1.0b1
Joomla 3.9.18
Last edit: 4 years 9 months ago by bendeb.

Please Log in or Create an account to join the conversation.

Moderators: vistamediajoomlacornerggppdk
Time to create page: 0.277 seconds
Save
Cookies user preferences
We use cookies to ensure you to get the best experience on our website. If you decline the use of cookies, this website may not function as expected.
Accept all
Decline all
Essential
These cookies are needed to make the website work correctly. You can not disable them.
Display
Accept
Analytics
Tools used to analyze the data to measure the effectiveness of a website and to understand how it works.
Google Analytics
Accept
Decline