Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

MySQL Query


Art Vandelay

Recommended Posts

Hi,

I'm one of those "copy-paste" newbies, ready to fry own head with this one :blush:

I'm trying to get a list of all products in the database that don't have the "download attribute" specified, so I can easily see witch of them are not downloadable.

 

Although I get the entire product listing, I don't know how I can remove from the query those who have this attribute (products_attributes_filename @ TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD) specified.

 

tep_db_query("SELECT p.products_id, pn.products_name, pc.categories_id, pi.products_image, pd.products_attributes_filename FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pn, " . TABLE_PRODUCTS_TO_CATEGORIES . " pc, " . TABLE_PRODUCTS . " pi, " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pd WHERE p.products_id = pn.products_id AND p.products_id = pi.products_id AND p.products_id = pc.products_id AND p.products_id != pd.products_attributes_id AND p.products_status = 1 AND pn.language_id = FLOOR($languages_id) ORDER BY pc.categories_id, pn.products_name");

 

I guess the tricky part is here:

p.products_id != pd.products_attributes_id

 

Any hint?

Link to comment
Share on other sites

the best thing to do would be a direct mysql query, outside of the style of language used here.

use phpmyadmin and see if you can get it extracted the way you want. then you can go from there and merge the statement into the code here.

Link to comment
Share on other sites

the MySQL query look like this:

SELECT p.products_id, pn.products_name, pc.categories_id, pi.products_image, pd.products_attributes_filename FROM products p, products_description pn, products_to_categories pc, products pi, products_attributes_download pd WHERE p.products_id = pn.products_id AND p.products_id = pi.products_id AND p.products_id = pc.products_id AND p.products_id != pd.products_attributes_id AND p.products_status = 1 AND pn.language_id = FLOOR(1) AND pi.products_image IS NOT NULL ORDER BY pc.categories_id, pn.products_name

 

:o It returns the entire catalog in triplicate!

Once for each product I have already with the "download attribute" set.

This is very confusing!

 

I've tried to change

...p.products_id != pd.products_attributes_id...

to

...p.products_id == pd.products_attributes_id...

but got an error!

Link to comment
Share on other sites

I think you need something called a left join:

tep_db_query("SELECT p.products_id, pd.products_attributes_id FROM TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pd on p.products_id = pd.products_attributes_id WHERE pd.products_attributes_id IS NULL");

This might get you started, hopefully ;)

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...