Trying to make image urls/location from information extracted from db

[Sensi420]

I am having a really hard time to replicate the image urls from information from the prestashop database.

I just don't get the logic prestashop uses to store the image location.

 

After i extracted all the product information from the database.i should be able to make the image urls from the information right?

 

Prestashop should store the image like this img/p/1/3/13.jpg

From database like this img/p/product_id/category_id/product_id+category_id.jpg

 

It is clearly i am missing something from the sql query to be able to extract the info to make the image url's.

This is the sql query i am using:

SELECT p.id_product AS 'ID',
p.active AS 'Active (0/1)',
pl.name AS 'Name',
p.id_category_default AS 'Default Category',
p.price AS 'Price tax excl.',
p.id_tax_rules_group AS 'Tax rules ID',
p.wholesale_price AS 'Wholesale price',
p.on_sale AS 'On sale (0/1)',
p.reference AS 'Reference #',
p.supplier_reference AS 'Supplier reference #',
sl.description AS 'Supplier',
ml.description AS 'Manufacturer',
p.ean13 AS 'EAN13',
p.upc AS 'UPC',
p.ecotax AS 'Ecotax',
p.weight AS 'Weight',
p.quantity AS 'Quantity',
pl.description_short AS 'Short description',
pl.description AS 'Description',
pl.meta_title AS 'Meta-title',
pl.meta_keywords AS 'Meta-keywords',
pl.meta_description AS 'Meta-description',
pl.link_rewrite AS 'URL rewritten',
pl.available_now AS 'Text when in stock',
pl.available_later AS 'Text when backorder allowed',
p.available_for_order AS 'Available for order',
p.date_add AS 'Product creation date',
p.show_price AS 'Show price',
p.online_only AS 'Available online only',
p.condition AS 'Condition'
FROM ps_product p INNER JOIN
ps_product_lang pl ON p.id_product = pl.id_product LEFT JOIN
ps_supplier_lang sl ON p.id_supplier = sl.id_supplier LEFT JOIN
ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer 

Can anyone please help me add the information to my query i need to be able to get the correct ids to make the image location/url?

[Sensi420]

One other option for me is to get the number 7 in the url of this from the database query :

http://localhost/prestashop/7-large_default/ipod-touch.jpg

How can i find the number(7) from the database query?

What do i need to add to my query to extract that number with the rest of the query i use?

Edited December 6, 2013 by Sensi420 (see edit history)

[Sensi420]

I found what i need to make the url's but how do i add it to my sql query?

What i need to add to my query is id_image when that is added i can make the urls with a script.

 

So anyone care to help add id_image to my query????

[doekia]

It is clearly i am missing something from the sql query to be able to extract the info to make the image url's.

You are missing id_image from table ps_image

Have a look at method Link::getImageLink() should be self explainatory

 

 

 

SELECT p.*
FROM ps_product p
INNER JOIN ps_product_lang pl ON p.id_product = pl.id_product
LEFT JOIN ps_supplier_lang sl ON p.id_supplier = sl.id_supplier
LEFT JOIN ps_manufacturer_lang ml ON p.id_manufacturer = ml.id_manufacturer 
INNER JOIN ps_image i ON p.id_product = i.id_product

Eventually you have to add a where clause such as WHERE i.cover = 1

 

You also need an INNER join with ps_image_shop should you have multiple shop