SQL query problem, please help!

[paulm2003]

Hi,

 

I'm trying to create a nice pricelist contri (for printing), mostly it works fine but it prints the prices/products for all languages at once. Could anybody help me to change the queries below to make it load only the products/categories for the current langage (= "(int)$languages_id" ?). I really don't know any sql myself yet :( , these queries are copied from another GPL script .

 

$query = 'SELECT * FROM categories LEFT JOIN categories_description USING(categories_id) ORDER BY sort_order, categories_name';

 

$query = 'SELECT * FROM products LEFT JOIN products_description USING(products_id) LEFT JOIN products_to_categories USING(products_id) WHERE  products_status = 1';// WHERE products_description.language_id='.$lang;

 

here you can see what it does at the moment:

http://www.eeweb.nl/osc-bts/prijslijst.php

(if the server isn't down again :rolleyes: )

[paulm2003]

Anyone please? :)

[bluepony]

$query = 'SELECT * FROM categories LEFT JOIN categories_description USING(categories_id) WHERE categories_description.language_id = '" . (int)$languages_id . "' ORDER BY sort_order, categories_name';

 

$query = 'SELECT * FROM products LEFT JOIN products_description USING(products_id) LEFT JOIN products_to_categories USING(products_id) WHERE  products_status = 1 and products_description.language_id="' . (int)$languages_id . '"';

[paulm2003]

Hi Henry,

 

thank you very very much!!! :D

 

I tried several things alike, but I just don't understand enough to really make it work. I believe I put WHERE categories_description.language_id = "' . (int)$languages_id . '" after the ORDER BY at a certain moment but I'm not sure if that really was the problem.

 

Only had to make a small change to the categories query you posted:

$query = 'SELECT * FROM categories LEFT JOIN categories_description USING(categories_id) WHERE categories_description.language_id = "' . (int)$languages_id . '" ORDER BY sort_order, categories_name';

Switched the " with the ' surrounding (int)$languages_id, but that was very easy to find of course. (it resulted in a unexpected " error)

 

Now I will try to fix a problem that arises if a main category has so many entries that three tables isn't enough to print it all (and a fourth table won't fit the page usually). After that, and some other minor/easy changes, I think it's worth while to upload a first version to the contributions :) .

 

thanks again,

 

Paul

[bluepony]

I like what you have done. I am sure that others will find this to be a very useful contribution. Good work! :)

[paulm2003]

Uploaded to contributions some days ago:

 

http://www.oscommerce.com/community/contributions,2059