Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

SQL select statement


angel17846

Recommended Posts

In catalog/index.php, we have the select statement for the products_listings.php page. I want to change this statement to pull out all product attributes and product text options.

 

Here is the statement now in index.php:

 

// We show them all

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

}

 

I also want to also select all product options via these tables:

products_options

products_options_values

products_options_values_to_products_options

products_text_attributes (this is a new table I installed via contribution for input textbox options instead of select boxes)

 

So, basically just need to modify select statement above to pull out not only product name, image, price, etc., but actually get all the options for each product as well.

 

I'm sure someone can do this for me...PLEASE!!!

 

Thanks,

Angel

Link to comment
Share on other sites

// We show them all

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

}

OK, first off, selecting all that info in one hit is going to really slow your site down. But if you really want to do this, look at the syntax above and I am certain you can figure it out for yourself. Lets break it down into sections:

 

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id

 

so here is where you set what the information you want to select is going to be. The letter before each item tells you which table it came from (next section)

 

from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s

 

This bit defines the tables you are using - the TABLE_NAME and the letter afterwards is your unique table identifier. Note the join statement connects databases together

 

s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id

 

defines common information for selection of single rows. i.e. p.manufacturers_id = m.manufacturers_id tells the statement to pull information from the manufacturers table (m) where the manufacturers id is the same as the manufacturers id set in the products table.

 

Take a backup first, then playaround. You will be much better prepared for future changes you need if you figure this out for yourself - I can code it, as can many others, but honestly its not that hard!

Please note - if I have suggested a contrib above, it doesnt mean it will work! Most of the contribs are not ones I've used, but may be useful for your particular problem....

Have you tried a refined search? Chances are your problem has already been dealt with elsewhere on the forums.....

if (stumped == true) {

return(square_one($start_over)

} else {

$random_query = tep_fetch_answer($forum_query)

}

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...