Presta doesn't answer so I ask for help here

[Guest]

I reported the bug on 22 Apr but have not gotten the answer yet.

Bug report : http://www.prestashop.com/bug_tracker/view/4255/

Details:
Just got the CSV import working successfully and very nicely.

However,when you go onto the product, the images are all underneath the main image (5 of them) but when you choose another attribute all the images disappear as they are not ‘checked’ in the back office under combinations.


Question:how to tick all the checking box by csv file ?

[Guest]

Seems Nobody knows how to deal with this issues

[rocky]

The CSV import tool in PrestaShop doesn't support linking combinations with images at the moment, so you must do it manually. It isn't a simple thing to fix either, so don't expect it to be fixed in PrestaShop v1.3 final. I suggest that you post this issue as a feature request and hopefully it will be added to the next major version of PrestaShop.

Perhaps it would be possible to write an SQL query to link all combinations to images for you. Do you want all combinations to be linked to all images by default?

[Guest]

Yes, sure Rocky, I want all of the images link to conbinations(attributes) ,please help me!

[rocky]

I've written an SQL query that will delete all the existing links between combinations and images and then link every product image to every one of its combinations:

TRUNCATE `ps_product_attribute_image`;
INSERT INTO `ps_product_attribute_image` SELECT `id_product_attribute`, `id_image` FROM `ps_product_attribute` pa LEFT JOIN `ps_image` i ON (pa.`id_product` = i.`id_product`);

Just change ps_ to your database prefix. Remember to make a backup of the `ps_product_attribute_image` table in case anything goes wrong.

[Guest]

HI Rocky, thank you very much.

How should I use the SQL query?


Should I change my exsiting datebase prefix into ps_ ?

[rocky]

No. If your database prefix is not ps_, then you should replace all instances of ps_ in the query above with whatever you entered as your database prefix.

[Guest]

1.So I have replace the prefix "ps_" with mine?

2.Paste the code into the text area as you see in the attached pic and then click "go" ?

[rocky]

Yes. In your case, you should paste the following:

TRUNCATE `ff_product_attribute_image`;
INSERT INTO `ff_product_attribute_image` SELECT `id_product_attribute`, `id_image` FROM `ff_product_attribute` pa LEFT JOIN `ff_image` i ON (pa.`id_product` = i.`id_product`); 

Make sure that you click the Export tab and save a backup of the table first, so you can restore the table back to the way it was if my query doesn't give you the result you expect.

[Guest]

Thank you very much Rocky, I will test it soon.

Can I ask for another help about slow database ?

[rocky]

Sure, but you'll need to be more specific about the problem. What version of PrestaShop are you using? Is it a certain table that is slow?

[Guest]

This is the post:
http://www.prestashop.com/forums/viewthread/54342/

I find the solution in forum but not for my version:

post 1

Post2

I am using version:1.3.0.6

Do you have any suggestions with this issues in version 1.3.0.6?

[rocky]

Sorry, I can't help much with that. I know that five more indexes have been added to database tables since v1.3.0.6, which should improve performance, but I'm not sure how much. In PrestaShop v1.3.0.8, indexes were added to the ps_product_attribute and ps_image_lang tables and in PrestaShop v1.3.0.10, indexes were added to the ps_order_detail, ps_category_group and ps_product tables.

EDIT: I did notice that your bug report called "Slow database" has been marked as "confirmed", so maybe PrestaShop will improve database performance further before releasing v1.3 final.

[Guest]

Thank you.

For the first question, will it be a matter with upgrading after I change the table:ff_product_attribute_image with your code?

and do you think it is safe to upgrade to version 1.3.10 please now?

[rocky]

No, my code doesn't modify the structure of the table, so it won't affect any attempt to upgrade. It just does the same thing that ticking all the boxes next to the images on the "3. Combinations" tab does, except it does it all at once using a single query.

[Guest]

Thank you for your time and your great help , Thank you Rocky.

[Guest]

I tested the code and it works fine , thank you very much !!!

[kleinermann]

Hi there,

I've found the place where i have to insert the query, but i must do something wrong because i get an error message saying i use the wrong syntax. What i did is i pasted the query just after the one that's already in there. Or do i have to delete the old to make it work?

Error
SQL query:

SELECT *
FROM `ps_product_attribute_image`
WHERE 1 TRUNCATE `ps_product_attribute_image` ;


MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRUNCATEÂ `ps_product_attribute_image`' at line 2

[kleinermann]

Hi there,
I've deleted the old entry first and tried it only with the new query, but i get an error message as well.
I run MySQL client version: 5.0.90, Server version: 5.0.90 in case that helps to find the problem.

Thanks so much for your help.


Error
SQL query:

TRUNCATE `ps_product_attribute_image` ;


MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRUNCATEÂ `ps_product_attribute_image`' at line 1

[rocky]

The query is working fine for me. Are you sure you copied it right? Are you using a database prefix other than "ps_"? Perhaps your version of SQL doesn't let you have multiple statements in one query. Try executing the TRUNCATE line by itself, then the INSERT INTO line by itself.

[kleinermann]

Hi Rocky,

I've tried it the way you said, but it just didn't work for me. Then i've played around a bit and finally got it working:

First i checked the syntax on this website: http://beginner-sql-tutorial.com/sql-delete-statement.htm
And made the changes as seen here:

TRUNCATE TABLE ps_product_attribute_image

When i tried it again i got a new error message, saying:

Error
SQL query:

TRUNCATE TABLE ps_product_attribute_image

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRUNCATEÂ TABLE ps_product_attribute_image' at line 1

I dont know where the "Â" symbol on the end of "TRUNCATE" came from. It doesn't show in my text editor, only in the MySQL error message. I tried to get rid of it with changing it in my text editor into plain text. But still the same. So i deleted the empty space behind "TRUNCATE" and created a new one (delete and spacebar), and it worked!

After that i had to do the same with the second part of the code:

INSERT INTO ps_product_attribute_image
SELECT id_product_attribute, id_image 
FROM ps_product_attribute pa LEFT JOIN ps_image i ON (pa.id_product = i.id_product)

Now it's all working perfect. Thanks, without your code i couldn't have done that! That saved me about 15hours of work if i would have to change all the attribute images manually!

[ricksterinps]

Thanks for the code as that is exactly what I was looking for.

I do have a question that may or may not be directly related to this. Is there a way for the attribute images to remember their name and display that? We have towels on our site and various colors and sizes and this code is great for having the images shown on each option, but is there a way for the customer to see what color they are looking at, or which one to select in the dropdown?

Thanks

[rocky]

I can't think of any SQL query that would do that automatically. You'd have to edit the products and tick only the relevant images for each colour.

[James R]

I've written an SQL query that will delete all the existing links between combinations and images and then link every product image to every one of its combinations:

TRUNCATE `ps_product_attribute_image`;
INSERT INTO `ps_product_attribute_image` SELECT `id_product_attribute`, `id_image` FROM `ps_product_attribute` pa LEFT JOIN `ps_image` i ON (pa.`id_product` = i.`id_product`);

Just change ps_ to your database prefix. Remember to make a backup of the `ps_product_attribute_image` table in case anything goes wrong.

You're a lifesaver Rocky, thanks for that query!

[mytheory.]

I've written an SQL query that will delete all the existing links between combinations and images and then link every product image to every one of its combinations:

TRUNCATE `ps_product_attribute_image`;
INSERT INTO `ps_product_attribute_image` SELECT `id_product_attribute`, `id_image` FROM `ps_product_attribute` pa LEFT JOIN `ps_image` i ON (pa.`id_product` = i.`id_product`);

Just change ps_ to your database prefix. Remember to make a backup of the `ps_product_attribute_image` table in case anything goes wrong.

First, THANK YOU ROCKY!

Been looking for a lead on this for many months!

Just one request... is there a way to modify this query to run for only certain product id's?

So let's say I know the product_id of a product with combinations, and I want to select all the images for all the combinations of this product, but this product only. How can I modify this query so I can specify the product_id and have it run for only that 1 product?

Thanks again!

[my-name]

Will this SQL query work on 1.5.2 and I have the same question as mytheory above.