Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

SQL query problem, please help!


paulm2003

Recommended Posts

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: )

Link to comment
Share on other sites

$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 . '"';

I'd rather be flying!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...