SQL Manager and images

[nickzampieri]

Is there a way to create a query in SQL manager that is able to show images? For example, to show the results of products with the primary image associated to that product?

 

Right now, if I try to use the concat function in the sql query, the text is output and not the actual image: <img src="blah.jpg">

 

It seems that html tags are stripped in the results that are displayed in the resulting window. There was a topic about modifying the AdminRequestSqlController.php to remove strip_tags, but I think that is only going to work for the actual "export" function.

 

Maybe there is another way to show images in the output screen of the SQL Manager?

 

Thanks,

Nick

 

P.S. Edited to show a screenshot of what I am seeing instead of the actual image

Edited August 1, 2017 by nickzampieri (see edit history)

[selectshop.at]

As images are not stored on database, you need to read first the ID of the product and than link it to the FTP folder /img/p/folder (URL) for to have it shown - Don't think you will have luck without including FTP folder.

[nickzampieri]

I have the full url but it seems that the resulting screen in the SQL Manager will strip out the tags. I have tried with the <img src="http://www.myserver.com/full/path/to/file.jpg"> and instead of showing the image, it just shows the string. If I take what is shown in the SQL Manager results page, I can paste that into a blank html document, open it up, and it resolves without any issues.

 

Similarly, none of the results in SQL manager will have hyperlinks in the results... only the text that you must copy and paste in another tab....

 

Nick

[selectshop.at]

You should override the Prestashop scripts in this case.

[nickzampieri]

Sure... I can override the original scripts, however, I am looking for guidance on where the scripts for SQL Manager is so that I can override them. Pointers?

[selectshop.at]

on FTP controllers/admin/AdminRequestSqlController.php

[nickzampieri]

OK. so as I mentioned in the original post, I have looked through the code on that page and the only reference that was obvious to me was with respect to the actual export to file within the processExport function. There, the "strip_tags" is being used, however, this only seems to apply to the actual export, and not to the "view".

 

Nick

[nickzampieri]

    public function renderView()
    {
        /** @var RequestSql $obj */
        if (!($obj = $this->loadObject(true))) {
            return;
        }

        $view = array();
        if ($results = Db::getInstance()->executeS($obj->sql)) {
            foreach (array_keys($results[0]) as $key) {
                $tab_key[] = $key;
            }

            $view['name'] = $obj->name;
            $view['key'] = $tab_key;
            $view['results'] = $results;

            $this->toolbar_title = $obj->name;

            $request_sql = new RequestSql();
            $view['attributes'] = $request_sql->attributes;
        } else {
            $view['error'] = true;
        }

        $this->tpl_view_vars = array(
            'view' => $view
        );
        return parent::renderView();
    }

I think the above section is what I need to modify, but I can't seem to figure out what to change and how... Any tips/ideas? $results is what I believe contains the actual data returned from the query.

Edited August 1, 2017 by nickzampieri (see edit history)

[nickzampieri]

• using the var_dump($results) actually shows the images
• using print_r($results) also shows the images

 

But the goal is to get the pictures to show within the table that is produced and not by using the above two php commands.... 

[selectshop.at]

Why do you want to store pictures in database ? You are overblowing the database unnecessarily.

[nickzampieri]

SELECT 
    A.NoEntre,
    concat('<img src=http://www.complexelepheonix.com/store/modules/bestkit_checkoutfields/files/', B.id_customer, '/image/photoPicID.jpg>') id_photo,
    B.id_customer,
    A.Moment,
    A.tTimeStamp,
    A.ReasonCode,
    B.id_gender,
    B.firstname,
    B.lastname,
    B.email,
    B.birthday,
    C.id_product
FROM
    ((SELECT 
        *
    FROM
        `pstp_pheonix_client_entries`) AS A
    INNER JOIN (SELECT 
        *
    FROM
        `pstp_customer`) AS B ON A.`NoClient` = B.`id_customer`
        INNER JOIN
    (SELECT 
        *
    FROM
        `pstp_membership_history` WHERE is_cancelled= '0' AND expiry_date > (NOW()) ) AS C ON B.`id_customer` = C.`id_customer`)
ORDER BY A.`tTimeStamp` DESC
LIMIT 0 , 500;

I don't think I was clear.... my goal is to "show" the images...not store the images. My files are sitting on my webserver. I am only trying to create a report that shows the image. The query I am using in SQL Manager looks like this:

[selectshop.at]

Why you don't go via webservice instead ? Webservice tutorials: http://doc.prestashop.com/display/PS16/Web+service+one-page+documentation

 

Developer tutorials: http://doc.prestashop.com/display/PS16/Developer+tutorials

[nickzampieri]

SELECT SQL_CALC_FOUND_ROWS p.`id_product`  AS `id_product`,
 p.`reference`  AS `reference`,
 p.`price`  AS `price`,
 p.`id_shop_default`  AS `id_shop_default`,
 p.`is_virtual`  AS `is_virtual`,
 pl.`name`  AS `name`,
 pl.`link_rewrite`  AS `link_rewrite`,
 sa.`active`  AS `active`,
 shop.`name`  AS `shopname`,
 image_shop.`id_image`  AS `id_image`,
 cl.`name`  AS `name_category`,
 0 AS `price_final`,
 pd.`nb_downloadable`  AS `nb_downloadable`,
 sav.`quantity`  AS `sav_quantity`,
 IF(sav.`quantity`<=0, 1, 0) AS `badge_danger` 
FROM  `pstp_product` p 
 LEFT JOIN `pstp_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 1 AND pl.`id_shop` = 1) 
 LEFT JOIN `pstp_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1  AND sav.id_shop_group = 0 ) 
 JOIN `pstp_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = 1) 
 LEFT JOIN `pstp_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1) 
 LEFT JOIN `pstp_category` c ON (c.`id_category` = cl.`id_category`) 
 LEFT JOIN `pstp_shop` shop ON (shop.id_shop = 1) 
 LEFT JOIN `pstp_image_shop` image_shop ON (image_shop.`id_product` = p.`id_product` AND image_shop.`cover` = 1 AND image_shop.id_shop = 1) 
 LEFT JOIN `pstp_image` i ON (i.`id_image` = image_shop.`id_image`) 
 LEFT JOIN `pstp_product_download` pd ON (pd.`id_product` = p.`id_product`) 
WHERE (1 AND state = 1)
 
ORDER BY  `id_product` ASC
 
LIMIT 0, 300
;

Well... first of all, I should have been clear that I am currently using 1.7.x of Prestashop. That being said, I am pretty sure that they have webservices there as well...but how does creating a webservice help me in understanding whether I am hitting a limitation of SQL Manager or if I am just doing something incorrectly in my query.

 

For example, on a separate note. Goto Catalog > Products and then click on the Export to SQL Manager which is supposed to give you a similar to "exact" report based on what you would see under the Catalog > Products page. But as you can see, the Catalog > Products page shows the little thumbnails/pics and hyperlink urls, but the Exported SQL looks like the attached images and as you can see from the attached screenshots, does not contain images or hyperlinks...mostly because it gets stripped out or surrounded by double quotes within the output screen.

 

I don't want to reinvent the wheel here.... The query I have works perfectly for me, but I want to see the images.

 

 

[selectshop.at]

With the webservice you can extract and write what you want to Prestashop and to another application on boths ways (from and to). images included. I think is easier as to grope with SQL-queries. Take a look into the docu.

 

As PS 1.7. will be at the end a transitional version for the project symphony, i will not spend my time in big programming. Take PS 1.6. which is stable and nearly bugless.

[bnadauld]

did u solve this problem?

[selectshop.at]

Yes, it is solved, and solution written 2 or 3 posts before yours. The solution here is a customized one, so for the special needs of nickzampieri. Pictures are not stored in database as written also previously.

[bnadauld]

ok thanks. Looks like i'll be signing up to 4 years at uni before i get this done

[bnadauld]

i solved it here with an sql query: