Need help for a SQL request

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

if you want to consider both main and secondary categories
then yes
you will need to use PPP__flexicontent_cats_item_relations table
see the modified query below
Code:
UPDATE `PPP_flexicontent_fields_item_relations` AS v INNER JOIN `PPP__flexicontent_cats_item_relations` AS c ON v.item_id = c.`itemid` AND c.catid = 56 INNER JOIN `PPP_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 #76737 by bendeb
Replied by bendeb on topic Need help for a SQL request
Thank again, ggppdk, it seems I see the problem (but I'm unable to know why).

This request return 0 line too, so nothing happen.
Field 602 remains empty for items in category 56.

But if I edit an item and set a value in field 602 manually (a value other than 'Générique', like 'toto') and execute your request, 'toto' is well replaced by 'Générique'.
So it seems this request work only for items that have already a value in field 602, and a value different than those in the request.
But if field 602 is empty for items we want to modify, the request don't work.

Thank you for helping me to solve this problem :)

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 #76742 by bendeb
Replied by bendeb on topic Need help for a SQL request
It seems it's because UPDATE affect only existing lines on the table.
Because lines doesn't exist (field 602 has no value yet, and it is empty) the request won't work.

So maybe the UPDATE command have to be replaced by another SQL command ? Like INSERT INTO ?

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 #76750 by bendeb
Replied by bendeb on topic Need help for a SQL request
Hello,
Do you have any solution please to replace the request UPDATE by INSERT INTO ?
Thank in advance.

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 #76752 by ggppdk
Replied by ggppdk on topic Need help for a SQL request
Hello

the query that i gave you is for replacing existing values
because that is what i understood from your original request

The insert will use a SELECT for values

something like: (i have not tested it)
Code:
INSERT INTO `PPP_flexicontent_fields_item_relations` (field_id, item_id, valueorder, suborder, value, value_integer, value_decimal, value_datetime) SELECT c.itemid, 602, 1, 1, 'Générique', 0, 0, NULL FROM `PPP__flexicontent_cats_item_relations` AS c INNER JOIN `PPP_flexicontent_items_ext` AS e ON c.itemid = e.item_id AND e.type_id = 6 WHERE c.catid = 56


-- 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 #76758 by bendeb
Replied by bendeb on topic Need help for a SQL request
Many thanks ggppdk, it works now :)

You just invert some fields (field_id and item_id), so correct request is as follow :
Code:
INSERT INTO `PPP_flexicontent_fields_item_relations` (item_id, field_id, valueorder, suborder, value, value_integer, value_decimal, value_datetime) SELECT c.itemid, 602, 1, 1, 'Générique', 0, 0, NULL FROM `PPP_flexicontent_cats_item_relations` AS c INNER JOIN `PPP_flexicontent_items_ext` AS e ON c.itemid = e.item_id AND e.type_id = 6 WHERE c.catid = 56

And because I have no use of type, I can just use these request :
Code:
INSERT INTO `PPP_flexicontent_fields_item_relations` (item_id, field_id, valueorder, suborder, value, value_integer, value_decimal, value_datetime) SELECT c.itemid, 602, 1, 1, 'Générique', 0, 0, NULL FROM `PPP_flexicontent_cats_item_relations` AS c WHERE c.catid = 56

Now, I have just to replace category_id and value and I can edit all my items at once.

Thank you very much.

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.276 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