Need help with sql query

[♥14steve14]

SQL is not my thing.

 

I am trying to alter the addon products monitor in my 2.3.3 store to show that a large image has also been uploaded to each product. The database holds this information, not in the products table but in a seperate table called products_images.

 

The so far modified sql reads

 

$products_query_raw = "select p.products_id, p.products_image, p.manufacturers_id, p.products_status, p.products_weight, p.products_quantity, p.products_tax_class_id, p.products_retail_price, p.products_cost, p.products_price, p.products_model_sku1, p.products_model_sku2, p.products_reorder_qty, pd.products_name, l.name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LANGUAGES . " l where p.products_id = pd.products_id and l.languages_id = pd.language_id order by pd.products_id, l.languages_id";
 $products_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $products_query_raw, $products_query_numrows);
 $products_query = tep_db_query($products_query_raw);
 while ($products = tep_db_fetch_array($products_query)) {

 

How do I alter this to incorperate the correct large image for each product?

 

I hope someone can help with this. Any help will be greatly received.

[♥14steve14]

28 views and no one has any ideas or can give a bit of help.

[♥bruyndoncx]

please post the layout of the product_images table

 

you have to add product_images to the from clause like ' , product_images pi '

you have to add the fieldname of the large product image into the select clause

you have to add a bit to the where clause, probably something like ' and p.products_id = pi.products_id

[♥14steve14]

@@bruyndoncx

 

The produts_image table consists of the following

 

id

products_id

image

htmlcontent

sort_order

[Bob Terveuren]

$products_query_raw = "select

p.products_id,

p.products_image,

p.manufacturers_id,

p.products_status,

p.products_weight,

p.products_quantity,

p.products_tax_class_id,

p.products_retail_price,

p.products_cost,

p.products_price,

p.products_model_sku1,

p.products_model_sku2,

p.products_reorder_qty,

pd.products_name,

l.name,

pi.large_image

from

" . TABLE_PRODUCTS . " p,

" . TABLE_PRODUCTS_DESCRIPTION . " pd,

" . TABLE_LANGUAGES . " l ,

product_images pi

where

p.products_id = pd.products_id

and

l.languages_id = pd.language_id

and

p.products_id = pi.products_id

order by

pd.products_id, l.languages_id";

 

kind of assumes that the table products_image has the fields products_id and large_image

[♥bruyndoncx]

$products_query_raw = "select p.products_id, p.products_image, p.manufacturers_id, p.products_status, 
p.products_weight, p.products_quantity, p.products_tax_class_id, p.products_retail_price, 
p.products_cost, p.products_price, p.products_model_sku1, p.products_model_sku2, 
p.products_reorder_qty, pd.products_name, l.name, pi.image
from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_LANGUAGES . " l, products_images pi
where p.products_id = pd.products_id and l.languages_id = pd.language_id and p.products_id = pi.products_id
order by pd.products_id, l.languages_id";
 $products_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $products_query_raw, $products_query_numrows);
 $products_query = tep_db_query($products_query_raw);
 while ($products = tep_db_fetch_array($products_query)) {

I've split the lines onto multiple lines so it is easier to read ...