Missing legends in ps_image_lang

[2grosiek7]

Hello,

I'd like to know, why I missed out part of legend column in ps_image_lang table? How to copy product name to that column back?

[Vince]

It should be done by PHP script.

 

Step 1: Get all product's images.

Step 2: Update the legends by the result in Step 1.

 

Let me know if you need more help :).

 

[musicmaster]

You can use the mass edit of Prestools (it is free).

[2grosiek7]

29 minutes ago, musicmaster said:

You can use the mass edit of Prestools (it is free).

Could you guide me with that, because it is my first time with this script, and I am afraid about possibly mistake in my work.

[musicmaster]

Please install an extra Prestashop on your localhost for experimentation. You cannot maintain a shop when you don't have a "playground" to try out new things.

[2grosiek7]

18 hours ago, Vince said:

It should be done by PHP script.

 

Step 1: Get all product's images.

Step 2: Update the legends by the result in Step 1.

 

Let me know if you need more help :).

 

Please, show me step by step solution with details

[hakeryk2]

Could You be more specific about your issue? Do You want to add labels with product name in it to all images without label  in Your shop?

It can be done using some php and mysql because I made something simmilar for mysefl when I uploaded a lot of product through CSV and they were not labeled.

 

Edited December 28, 2017 by hakeryk2 (see edit history)

[2grosiek7]

3 hours ago, hakeryk2 said:

Could You be more specific about your issue? Do You want to add labels with product name in it to all images without label  in Your shop?

It can be done using some php and mysql because I made something simmilar for mysefl when I uploaded a lot of product through CSV and they were not labeled.

 

Yeah, just like that. I was trying with CSV import/export, but some products has more than one photo, and it bring me some problems.

[hakeryk2]

Grab this "module" and extract it into modules folder. Do not install it, it is just some code that You will need to run and that is it.

playground.zip

Basically this is this code
 

<?php

require_once(dirname(__FILE__).'/../../config/config.inc.php');

//-------------------------------------------------
// AKtualizacja wszystkich nazw image które sa puste z automatu
// Zrób później testy co jest szybsze, dodanie do .sql  i wykonanie potem, czy wykonanie pojedynczo kazdego
// Albo i nie bo już nigdy tego nie użyjesz
//-----------------------------------------------

// Grab some non labeled images
$empty_image_legends = Db::getInstance()->executeS('
   SELECT il.id_image, i.id_product, pai.id_product_attribute, il.legend FROM '._DB_PREFIX_.'image_lang il
   LEFT JOIN '._DB_PREFIX_.'image i ON (il.id_image = i.id_image)
   LEFT JOIN  '._DB_PREFIX_.'product_attribute_image pai ON (pai.id_image = i.id_image)
   WHERE il.legend is null OR il.legend = \'\'
');

// set id lang by hand like 0 or get it through context
$id_lang = Context::getContext()->language->id;
$i = 0;

// for every empty label update with product name
foreach ($empty_image_legends as $image) {
    $name = ($image['id_product_attribute']) ? Product::getProductName($image['id_product'], $image['id_product_attribute']) : Product::getProductName($image['id_product']);

    Db::getInstance()->execute('
        UPDATE '._DB_PREFIX_.'image_lang SET legend = \''.$name.'\' WHERE id_image = '.$image['id_image'].' AND id_lang = '.$id_lang.';
    ');

    echo 'Updated '.$name.'<br>';
    $i++;
}

echo $i.' images without legend was updated';

It should use context as id_language but it is probably not doing it but I don't care - I don't use multistore or multilanguage so I didn't wrote this script to be universal. You can make it universal if You want by adding in SQL clause in WHERE id of shop or id of language.

After that You just have to go to http://yourwebsite.com/modules/playground/tests.php and see the magic - this script is taking all of the non labeled images and give them labels like name or if they are assigned to some images - name and attribute name as label.

Before - You should make copy of ps_image_lang table in Your database in case if something went wrong.

Edited June 6, 2018 by hakeryk2 (see edit history)

[2grosiek7]

It works great! My need is to update column of legends, where many lines has "Zdjecie" instead of image name. I modified it and set IS NOT NULL and update all of them. Thank you so much!

[ilario]

thank you very much hakeryk2

your code helped me, for the language I made this change and it works fine

foreach ($empty_image_legends as $image) {
    $legend = Product::getProductName($image['id_product'],$image['id_product_attribute'], $image['id_lang'] );
   
    Db::getInstance()->execute('
        UPDATE '._DB_PREFIX_.'image_lang SET legend = \''.$legend.'\' WHERE id_image = '.$image['id_image'].' AND id_lang = '.$image['id_lang'].';
   ');
   
   
    echo 'Updated '.$legend.'<br>';
        
    $i++;
}

echo $i.' images without legend was updated';

 

ciao 

Ilario