SQL statement : subquery

[patator]

:( Hello

I'm trying to show all the products available within the current cataegory. The category contain sub+sub-sub categories.

 

Here is my query, which doesn't work. Could somebody help me explaining me where I'm wrong ?

 

Thanks

Pat

 

$listing_sql2="SELECT c2.categories_id

FROM " . TABLE_CATEGORIES . " c2

LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd2

ON c2.categories_id = c2d.categories_id

WHERE (c2.parent_id='23' AND cd2.language_id='4')";

 

$listing_sql = "SELECT ". $select_column_list ." p.products_id

FROM (". listing_sql2 .")

 

LEFT JOIN " . TABLE_CATEGORIES_ . " c on (". listing_sql2 .")= c.parent_id

 

LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON c.categories_id=p2c.categories_id

LEFT JOIN " . TABLE_PRODUCTS . " p on p2c.products_id = p.products_id

LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id

 

 

WHERE (p.products_status='1' AND pd.language_id='4')";

 

 

ERROR MESSAGE :

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT p.products_image, pd.products_name, p.products_id FROM

 

select count(p.products_id) as total SELECT p.products_image, pd.products_name, p.products_id FROM (listing_sql2) LEFT JOIN TABLE_CATEGORIES_ c on (listing_sql2)= c.parent_id LEFT JOIN products_to_categories p2c ON c.categories_id=p2c.categories_id LEFT JOIN products p on p2c.products_id = p.products_id LEFT JOIN products_description pd on p.products_id = pd.products_id WHERE (p.products_status='1' AND pd.language_id='4')

 

 

 

[TEP STOP]

[Guest]

If you use subqueries the splitPage class cannot be used...you'll have to roll your own.

 

Try taking it out of the splitPage and adding manual limits to the query. If this method works then commence coding your own split.

 

Bobby

[Rob123]

How 'bout trying to use the function tep_get_category_tree...

it does the work for you.

 

If you can't use it, look at the code and get a few ideas.

HTH,

Robert

[Guest]

I don't know if Patrice could use the tep_get_category_tree function since it only returns the category tree. It looks like a top down query is what would do the trick...

 

Bobby

[Rob123]

I'm embarassed after I re-read his question.

We're having the first blizzard of the season and my brain is a bit frozen.

:blush:

 

Rob

[Guest]

Hey Patrice...try the code below. I coded it to be fast, scalable, and as easy on system resources as possible. I expect some of the arrays could get pretty large.

 

The code will return an array of all products in and below the current category. There are variables in the code that you can use, modify, or pass at your convenience. For example, pay attention to the following vars: $current_cat_id and $lang_id

 

As always, my code is thoroughly commented so you can follow along...it shouldn't be hard.

 

Code was developed and test on a stock osC-MS2 codebase.

 

Enjoy!

 

# This is the current category ID or one of interest
$current_cat_id = 3;
# Get the subcategory ID's
# $sub_cats parameter will be populated with subcategory ID's
tep_get_subcategories($sub_cats, $current_cat_id);
# Loop the $sub_cats array and start compiling the UNION query
foreach ($sub_cats as $index => $cat_id){
$union[] = "SELECT products_id FROM ".TABLE_PRODUCTS_TO_CATEGORIES." WHERE categories_id='".$cat_id."'";
}
# implode the UNION query to get the final string
$union_str = implode(" UNION ", $union) . " ORDER BY products_id desc";
# unset some stuff...clean as we go
unset($sub_cats, $union);
# execute the query
$products_id_query = tep_db_query($union_str);
# Loop the array and populate the $prod_array with product ID's
while($prods = tep_db_fetch_array($products_id_query)){
$prod_array[] = $prods['products_id'];
}
# free the result...clean as we go
tep_db_free_result($products_id_query);
# define the column list :products p, products_description pd
$column_list = 'p.products_id, p.products_image, p.products_price, p.manufacturers_id, pd.products_name, pd.products_description';
# use a variable for the language ID so the script will be portable
$lang_id = '1';
# loop the $prod_array and start compiling the UNION query
foreach($prod_array as $index => $pID){
$union[] = "SELECT ".$column_list." 
   FROM ".TABLE_PRODUCTS." p 
   LEFT JOIN ".TABLE_PRODUCTS_DESCRIPTION." pd 
   ON ( p.products_id=pd.products_id AND pd.language_id='".$lang_id."' ) 
   WHERE p.products_id='".$pID."' AND p.products_status='1'";
}
# implode the UNION query and get the final string
$union_str = implode(" UNION ", $union) . " ORDER BY products_id desc";
# unset some stuff...clean as we go
unset($prod_array, $union);
# execute the query
$products_query = tep_db_query($union_str);
# Loop the array and populate the final $prod_array
while($products = tep_db_fetch_array($products_query)){
$prod_array[] = $products;
}
# free the result...clean as we go
tep_db_free_result($products_query);

# do whatever you need to do with the $prod_array
echo '<pre>';
print_r($prod_array);
echo '</pre>';

# script created and tested by Chemo
# Who's your daddy? :-)

[patator]

Hello to all of you and thanks a lot for your answers.

 

 

Sorry for not giving any answer but it was bed time in France....

 

I'm trying now your solutions and let you know ASAP

 

 

Thanks A LOT

Patrice :thumbsup:

[patator]

The SQL works perfectly, it transfers the correct products within the array :), but I can't get the result I'm looking for.

 

I use the sql string (listing-sql or whatever name) for :

<td><?php include(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING);?></td>

 

Before calling FILENAME_PRODUCTS_LISTNG, I do :

listing_sql = $union_str -> doesn't work

listing_sql= $prod_array() -> doesn't work and I understand why (an array is not a query).

 

Any further idea

Thanks again and again

Pat

[patator]

I forgot to indicate the error message :

 

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT p.products_image, pd.products_name, p.products_id FR

 

select count(p.products_id) as total SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='90' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='69' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='68' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='67' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='66' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='43' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='42' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='31' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='30' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='29' AND p.products_status='1' ORDER BY products_id desc

[boxtel]

I forgot to indicate the error message :

 

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT p.products_image, pd.products_name, p.products_id FR

 

select count(p.products_id) as total SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='90' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='69' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='68' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='67' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='66' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='43' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='42' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='31' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='30' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='29' AND p.products_status='1' ORDER BY products_id desc

<{POST_SNAPBACK}>

 

 

you have 2 select statements:

 

select count(p.products_id) as total SELECT p...

[patator]

you have 2 select statements:

 

select count(p.products_id) as total SELECT p...

<{POST_SNAPBACK}>

yes indead...

 

the "DIR_WS_MODULES . FILENAME_PRODUCT_LISTING" counts the number of items to split on several pages if they're too many to display on one page

[patator]

I added the missing FROM, I still have the error message :

 

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT p.products_image, pd.products_name, p.products_id FR

 

select count(p.products_id) as total FROM SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='90' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='69' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='68' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='67' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='66' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='43' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='42' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='31' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='30' AND p.products_status='1' UNION SELECT p.products_image, pd.products_name, p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='4' ) WHERE p.products_id='29' AND p.products_status='1' ORDER BY products_name desc

[Guest]

Patrice,

 

You cannot use the splitPageResults class with that code...you will have to roll your own output script.

 

Bobby

[patator]

OK

 

Thanks for your help anyway

 

Patrice,

 

You cannot use the splitPageResults class with that code...you will have to roll your own output script.

 

Bobby

<{POST_SNAPBACK}>

[Guest]

Don't give up Patrice...it's easier to create a custom split result class than you think :)

 

Give it an honest attempt and I'll help you along (but won't code it all for you).

 

Bobby

[patator]

OK Chemo

 

I'm new at php.....

I'm only (very ?) good at VBA, so I will give it a serious try and try to understand better what I'm doing. It seems to be so tough.

 

Forget the new topic I just posted, I thought it was extremely difficult to create a class.

 

Thanks

Patrice

Don't give up Patrice...it's easier to create a custom split result class than you think :)

 

Give it an honest attempt and I'll help you along (but won't code it all for you).

 

Bobby

<{POST_SNAPBACK}>

[Guest]

Leave the other thread up...we'll take the discussion over there and develop a custom split result class for this.

 

Bobby

[patator]

Thanks Chemo....

Leave the other thread up...we'll take the discussion over there and develop a custom split result class for this.

 

Bobby

<{POST_SNAPBACK}>