Need help for a SQL request

3 weeks 6 days 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.

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

3 weeks 4 days ago - 3 weeks 3 days 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
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 review. Thanks!

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

3 weeks 3 days ago - 3 weeks 3 days 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 :
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.

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

3 weeks 3 days 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:
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 review. Thanks!

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

3 weeks 3 days 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.

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

3 weeks 2 days ago - 3 weeks 2 days 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.

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

3 weeks 2 days 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
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 review. Thanks!

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

3 weeks 2 days 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 :)

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

3 weeks 1 day 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 ?

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

3 weeks 2 hours 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.

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

Moderators: vistamediajoomlacornerggppdk
© 2018 Flexicontent. All Rights Reserved.