Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Need help with sql query


14steve14

Recommended Posts

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.

REMEMBER BACKUP, BACKUP AND BACKUP

Link to comment
Share on other sites

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

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

$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

Link to comment
Share on other sites

$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 ...

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...