Union Query for product listing

patator · January 23, 2005

Hello

Chemo helped me a lot for being able to create a query which will get all the products available into a selection of different categories.

It is done thanks to a php code which puts together a succession of union queries.

Here is the genuine code :

Chemo tips

The query obtained thanks to Chemo's code can look as follow :

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) WHERE p.products_id='90' AND pd.language_id='4' 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) WHERE p.products_id='69' AND pd.language_id='4' 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) WHERE p.products_id='68' AND pd.language_id='4' 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) WHERE p.products_id='67' AND pd.language_id='4' 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) WHERE p.products_id='66' AND pd.language_id='4' 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) WHERE p.products_id='43' AND pd.language_id='4' 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) WHERE p.products_id='42' AND pd.language_id='4' 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) WHERE p.products_id='31' AND pd.language_id='4' 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) WHERE p.products_id='30' AND pd.language_id='4' 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) WHERE p.products_id='29' AND pd.language_id='4' AND p.products_status='1' ORDER BY products_name desc

I want to display the selected products using the product_listing module :

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

The product_listing first calls the splitpageresults (for splitting the products on several pages).

Apparently, the splitpageresults can't work with union queries.

Could somebody take over Master Chemo and tell how can I do and eventually guide me ?

Thanks

Patrice

January 23, 2005

Tell me more about your application...is this going to be a cat/sub-cat listing like the normal product listing? Is this going to be an infobox like featured or default specials?

We can make a custom split result class to handle unions without problems. However, presenting the data will vary based on your application.

Bobby

patator · January 23, 2005

It will be a cat/sub-cat/sub-sub-cat..... listing like the normal product listing.

It won't be used for anything else. It will be shown below the categories name and above the new products table.

As you know, it already works if I'm in the last subcategory.

I have already created and declared a new class called splitPageResults2 located in the file split_Page_Results_2.

I'm trying to find out a solution as you suggested, but your help will be gratefully appreciated

Patrice

Tell me more about your application...is this going to be a cat/sub-cat listing like the normal product listing?? Is this going to be an infobox like featured or default specials?

We can make a custom split result class to handle unions without problems.? However, presenting the data will vary based on your application.

Bobby

<{POST_SNAPBACK}>

patator · January 23, 2005

here is the part of the code I'm working on (in splitpageresult2)

$pos_to = strlen($this->sql_query);

$pos_from = strpos($this->sql_query, ' from', 0);

$pos_group_by = strpos($this->sql_query, ' group by', $pos_from);

if (($pos_group_by < $pos_to) && ($pos_group_by != false)) $pos_to = $pos_group_by;

$pos_having = strpos($this->sql_query, ' having', $pos_from);

if (($pos_having < $pos_to) && ($pos_having != false)) $pos_to = $pos_having;

$pos_order_by = strpos($this->sql_query, ' order by', $pos_from);

if (($pos_order_by < $pos_to) && ($pos_order_by != false)) $pos_to = $pos_order_by;

if (strpos($this->sql_query, 'distinct') || strpos($this->sql_query, 'group by')) {

$count_string = 'distinct ' . tep_db_input($count_key);

} else {

$count_string = tep_db_input($count_key);

}

$count_query = tep_db_query("select count(" . $count_string . ") as total " . substr($this->sql_query, $pos_from, ($pos_to - $pos_from)));

$count = tep_db_fetch_array($count_query);

$this->number_of_rows = $count['total'];

patator · January 23, 2005

The splitPageResults is case sensitive for recognizing the select from, group by, order by, statements

My sql was written with upper case : SELECT, GROUP BY, so it couldn't be correctly analysed.

Now I get it working a bit better but I still have an error message :

1222 - The used SELECT statements have a different number of columns

select count(p.products_id) as total from products p left join products_description pd on ( p.products_id=pd.products_id) where p.products_id='90' and pd.language_id='4' 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) where p.products_id='69' and pd.language_id='4' 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) where p.products_id='68' and pd.language_id='4' 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) where p.products_id='67' and pd.language_id='4' 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) where p.products_id='66' and pd.language_id='4' 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) where p.products_id='43' and pd.language_id='4' 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) where p.products_id='42' and pd.language_id='4' 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) where p.products_id='31' and pd.language_id='4' 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) where p.products_id='30' and pd.language_id='4' 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) where p.products_id='29' and pd.language_id='4' and p.products_status='1'

[TEP STOP]

January 24, 2005

STEP #1 - Save this code as includes/classes/union_split_page_results.php

<?php
/*=======================================================================*\
|| #################### //-- SCRIPT INFO --// ########################## ||
|| #	Script name: union_split_page_results.php 
|| #	Contribution: Union Query Product Listing
|| #	Version: 1.0
|| #	Date: 23 January 2004
|| # ------------------------------------------------------------------ # ||
|| #################### //-- COPYRIGHT INFO --// ######################## ||
|| #	Copyright (C) 2005 Bobby Easland        # ||
|| #	Internet moniker: Chemo         	 # ||	
|| #	Contact: [email protected]       	 # ||
|| #	Commercial Site: http://gigabyte-hosting.com/   	 # ||
|| #	GPL Dev Server: http://mesoimpact.com/     	 # ||
|| #               	 # ||
|| #	This script is free software; you can redistribute it and/or	# ||
|| #	modify it under the terms of the GNU General Public License  # ||
|| #	as published by the Free Software Foundation; either version 2	# ||
|| #	of the License, or (at your option) any later version. 	 # ||
|| #               	 # ||
|| #	This script is distributed in the hope that it will be useful,	# ||
|| #	but WITHOUT ANY WARRANTY; without even the implied warranty of	# ||
|| #	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the	# ||
|| #	GNU General Public License for more details.   	 # ||
|| #               	 # ||
|| #	Script is intended to be used with:        # ||
|| #	osCommerce, Open Source E-Commerce Solutions   	 # ||
|| #	http://www.oscommerce.com          # ||
|| #	Copyright (c) 2003 osCommerce       	 # ||
|| ###################################################################### ||
\*========================================================================*/

class unionSplit{
   var $query, $num_rows, $page_number, $number_of_pages, $page_number, $page_name;	
var $offset, $limit;

function unionSplit($query, $max_rows, $page_number, $page_name = 'page'){
 $this->page_name = $page_name;
 $this->page_number = ( empty($page_number) || !is_numeric($page_number) ? 1 : $page_number );
 $replace = "SELECT SQL_CALC_FOUND_ROWS ";
 $union_query = substr_replace($query, $replace, 0, 7);
 $this->limit = $max_rows;
 $this->offset = ($this->page_number - 1) * $this->limit;	
 $union_query .= " LIMIT " . $this->offset . ", " . $this->limit;  
 $this->query = tep_db_query($union_query);	
 $rows = tep_db_fetch_array(tep_db_query('SELECT FOUND_ROWS() as count'));
 $this->num_rows = (int)$rows['count'];	
 $this->number_of_pages = ceil($this->num_rows / $this->limit);
}

   function display_count($text_output) {
     $to_num = ($this->limit * $this->page_number);
     if ($to_num > $this->num_rows) $to_num = $this->num_rows;
     $from_num = ($this->limit * ($this->page_number - 1));
     if ($to_num == 0) {
       $from_num = 0;
     } else {
       $from_num++;
     }
     return sprintf($text_output, $from_num, $to_num, $this->num_rows);
   }

   function display_links($max_page_links, $parameters = '') {
     global $PHP_SELF, $request_type;

     $display_links_string = '';
     $class = 'class="pageResults"';
     if (tep_not_null($parameters) && (substr($parameters, -1) != '&')) $parameters .= '&';
     if ($this->page_number > 1) $display_links_string .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . ($this->page_number - 1), $request_type) . '" class="pageResults" title=" ' . PREVNEXT_TITLE_PREVIOUS_PAGE . ' "><u>' . PREVNEXT_BUTTON_PREV . '</u></a>  ';
     $cur_window_num = intval($this->page_number / $max_page_links);
     if ($this->page_number % $max_page_links) $cur_window_num++;
     $max_window_num = intval($this->number_of_pages / $max_page_links);
     if ($this->number_of_pages % $max_page_links) $max_window_num++;
     if ($cur_window_num > 1) $display_links_string .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . (($cur_window_num - 1) * $max_page_links), $request_type) . '" class="pageResults" title=" ' . sprintf(PREVNEXT_TITLE_PREV_SET_OF_NO_PAGE, $max_page_links) . ' ">...</a>';
     for ($jump_to_page = 1 + (($cur_window_num - 1) * $max_page_links); ($jump_to_page <= ($cur_window_num * $max_page_links)) && ($jump_to_page <= $this->number_of_pages); $jump_to_page++) {
       if ($jump_to_page == $this->page_number) {
         $display_links_string .= ' <b>' . $jump_to_page . '</b> ';
       } else {
         $display_links_string .= ' <a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . $jump_to_page, $request_type) . '" class="pageResults" title=" ' . sprintf(PREVNEXT_TITLE_PAGE_NO, $jump_to_page) . ' "><u>' . $jump_to_page . '</u></a> ';
       }
     }
     if ($cur_window_num < $max_window_num) $display_links_string .= '<a href="' . tep_href_link(basename($PHP_SELF), $parameters . $this->page_name . '=' . (($cur_window_num) * $max_page_links + 1), $request_type) . '" class="pageResults" title=" ' . sprintf(PREVNEXT_TITLE_NEXT_SET_OF_NO_PAGE, $max_page_links) . ' ">...</a> ';
     if (($this->page_number < $this->number_of_pages) && ($this->number_of_pages != 1)) $display_links_string .= ' <a href="' . tep_href_link(basename($PHP_SELF), $parameters . 'page=' . ($this->page_number + 1), $request_type) . '" class="pageResults" title=" ' . PREVNEXT_TITLE_NEXT_PAGE . ' "><u>' . PREVNEXT_BUTTON_NEXT . '</u></a> ';

     return $display_links_string;
   }
} # end class
?>

STEP #2 - Save this code as includes/modules/product_listing_union.php

<?php
/*=======================================================================*\
|| #################### //-- SCRIPT INFO --// ########################## ||
|| #	Script name: product_listing_union.php 
|| #	Contribution: Union Query Product Listing
|| #	Version: 1.0
|| #	Date: 23 January 2004
|| # ------------------------------------------------------------------ # ||
|| #################### //-- COPYRIGHT INFO --// ######################## ||
|| #	Copyright (C) 2005 Bobby Easland        # ||
|| #	Internet moniker: Chemo         	 # ||	
|| #	Contact: [email protected]       	 # ||
|| #	Commercial Site: http://gigabyte-hosting.com/   	 # ||
|| #	GPL Dev Server: http://mesoimpact.com/     	 # ||
|| #               	 # ||
|| #	This script is free software; you can redistribute it and/or	# ||
|| #	modify it under the terms of the GNU General Public License  # ||
|| #	as published by the Free Software Foundation; either version 2	# ||
|| #	of the License, or (at your option) any later version. 	 # ||
|| #               	 # ||
|| #	This script is distributed in the hope that it will be useful,	# ||
|| #	but WITHOUT ANY WARRANTY; without even the implied warranty of	# ||
|| #	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the	# ||
|| #	GNU General Public License for more details.   	 # ||
|| #               	 # ||
|| #	Script is intended to be used with:        # ||
|| #	osCommerce, Open Source E-Commerce Solutions   	 # ||
|| #	http://www.oscommerce.com          # ||
|| #	Copyright (c) 2003 osCommerce       	 # ||
|| ###################################################################### ||
\*========================================================================*/

# Get the subcategory ID's
# $sub_cats parameter will be populated with subcategory ID's
tep_get_subcategories($sub_cats, $current_category_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_quantity, p.products_model, p.products_image, p.products_price, p.products_weight, p.manufacturers_id, p.products_ordered, pd.products_name, pd.products_description';
# use a variable for the language ID so the script will be portable

# loop the $prod_array and start compiling the UNION query
foreach($prod_array as $index => $pID){
#$column_list = ( $index=0 ? 'SQL_CALC_FOUND_ROWS '.$column_list : $column_list);
$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='".$languages_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_ordered desc";
# unset some stuff...clean as we go
unset($prod_array, $union);
# execute the query

# initiate the unionSplit class
	 $listing_split = new unionSplit($union_str, MAX_DISPLAY_SEARCH_RESULTS, (int)$_GET['page']);
# define the list of columns
   $define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
                        'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
                        'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,
                        'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
                        'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
                        'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
                        'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE,
                        'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);
   asort($define_list);
   $column_list = array();
   reset($define_list);
   while (list($key, $value) = each($define_list)) {
     if ($value > 0) $column_list[] = $key;
   }

/*======================================================*\
   Start the listing
\*======================================================*/

 if ( ($listing_split->num_rows > 0) && ( (PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3') ) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
   <td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
   <td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }

 $list_box_contents = array();

 for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
   switch ($column_list[$col]) {
     case 'PRODUCT_LIST_MODEL':
       $lc_text = TABLE_HEADING_MODEL;
       $lc_align = '';
       break;
     case 'PRODUCT_LIST_NAME':
       $lc_text = TABLE_HEADING_PRODUCTS;
       $lc_align = '';
       break;
     case 'PRODUCT_LIST_MANUFACTURER':
       $lc_text = TABLE_HEADING_MANUFACTURER;
       $lc_align = '';
       break;
     case 'PRODUCT_LIST_PRICE':
       $lc_text = TABLE_HEADING_PRICE;
       $lc_align = 'right';
       break;
     case 'PRODUCT_LIST_QUANTITY':
       $lc_text = TABLE_HEADING_QUANTITY;
       $lc_align = 'right';
       break;
     case 'PRODUCT_LIST_WEIGHT':
       $lc_text = TABLE_HEADING_WEIGHT;
       $lc_align = 'right';
       break;
     case 'PRODUCT_LIST_IMAGE':
       $lc_text = TABLE_HEADING_IMAGE;
       $lc_align = 'center';
       break;
     case 'PRODUCT_LIST_BUY_NOW':
       $lc_text = TABLE_HEADING_BUY_NOW;
       $lc_align = 'center';
       break;
   }

   if ( ($column_list[$col] != 'PRODUCT_LIST_BUY_NOW') && ($column_list[$col] != 'PRODUCT_LIST_IMAGE') ) {
     $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
   }

   $list_box_contents[0][] = array('align' => $lc_align,
                                   'params' => 'class="productListing-heading"',
                                   'text' => ' ' . $lc_text . ' ');
 }

 if ($listing_split->num_rows > 0) {
   $rows = 0;
   while ($listing = tep_db_fetch_array($listing_split->query)) {
     $rows++;

     if (($rows/2) == floor($rows/2)) {
       $list_box_contents[] = array('params' => 'class="productListing-even"');
     } else {
       $list_box_contents[] = array('params' => 'class="productListing-odd"');
     }

     $cur_row = sizeof($list_box_contents) - 1;

     for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
       $lc_align = '';

       switch ($column_list[$col]) {
         case 'PRODUCT_LIST_MODEL':
           $lc_align = '';
           $lc_text = ' ' . $listing['products_model'] . ' ';
           break;
         case 'PRODUCT_LIST_NAME':
           $lc_align = '';
           if (isset($HTTP_GET_VARS['manufacturers_id'])) {
             $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing['products_id']) . '">' . $listing['products_name'] . '</a>';
           } else {
             $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $listing['products_id']) . '">' . $listing['products_name'] . '</a> ';
           }
           break;
         case 'PRODUCT_LIST_MANUFACTURER':
           $lc_align = '';
           $lc_text = ' <a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $listing['manufacturers_id']) . '">' . $listing['manufacturers_name'] . '</a> ';
           break;
         case 'PRODUCT_LIST_PRICE':
           $lc_align = 'right';
           if (tep_not_null($listing['specials_new_products_price'])) {
             $lc_text = ' <s>' .  $currencies->display_price($listing['products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</s>  <span class="productSpecialPrice">' . $currencies->display_price($listing['specials_new_products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</span> ';
           } else {
             $lc_text = ' ' . $currencies->display_price($listing['products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . ' ';
           }
           break;
         case 'PRODUCT_LIST_QUANTITY':
           $lc_align = 'right';
           $lc_text = ' ' . $listing['products_quantity'] . ' ';
           break;
         case 'PRODUCT_LIST_WEIGHT':
           $lc_align = 'right';
           $lc_text = ' ' . $listing['products_weight'] . ' ';
           break;
         case 'PRODUCT_LIST_IMAGE':
           $lc_align = 'center';
           if (isset($HTTP_GET_VARS['manufacturers_id'])) {
             $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing['products_image'], $listing['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>';
           } else {
             $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $listing['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing['products_image'], $listing['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a> ';
           }
           break;
         case 'PRODUCT_LIST_BUY_NOW':
           $lc_align = 'center';
           $lc_text = '<a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $listing['products_id']) . '">' . tep_image_button('button_buy_now.gif', IMAGE_BUTTON_BUY_NOW) . '</a> ';
           break;
       }

       $list_box_contents[$cur_row][] = array('align' => $lc_align,
                                              'params' => 'class="productListing-data"',
                                              'text'  => $lc_text);
     }
   }

   new productListingBox($list_box_contents);
 } else {
   $list_box_contents = array();

   $list_box_contents[0] = array('params' => 'class="productListing-odd"');
   $list_box_contents[0][] = array('params' => 'class="productListing-data"',
                                  'text' => TEXT_NO_PRODUCTS);

   new productListingBox($list_box_contents);
 }

 if ( ($listing_split->num_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3')) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
   <td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
   <td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }
?>

STEP #3 - Place this code where you want the new listing to show up:

include('includes/classes/union_split_page_results.php');
include('includes/modules/product_listing_union.php');

STEP #4 - Send big, fat donation to Chemo for coding this when he said he would help but not do it all for you :)

Bobby

patator · January 24, 2005

First of all : THANKS FOR YOUR HELP and for all the time you spent for helping me.

I had a quick look at your website and might contact you quickly to use your service.

Anyway, it seems that I'm having a small prob :

Any idea ?

1222 - The used SELECT statements have a different number of columns

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) where p.products_id='90' and pd.language_id='4' 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) where p.products_id='69' and pd.language_id='4' 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) where p.products_id='68' and pd.language_id='4' 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) where p.products_id='67' and pd.language_id='4' 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) where p.products_id='66' and pd.language_id='4' 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) where p.products_id='43' and pd.language_id='4' 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) where p.products_id='42' and pd.language_id='4' 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) where p.products_id='31' and pd.language_id='4' 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) where p.products_id='30' and pd.language_id='4' 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) where p.products_id='29' and pd.language_id='4' and p.products_status='1' UNION SELECT products_id FROM products_to_categories WHERE categories_id='21' UNION SELECT products_id FROM products_to_categories WHERE categories_id='55' UNION SELECT products_id FROM products_to_categories WHERE categories_id='56' UNION SELECT products_id FROM products_to_categories WHERE categories_id='22' UNION SELECT products_id FROM products_to_categories WHERE categories_id='86' UNION SELECT products_id FROM products_to_categories WHERE categories_id='87' ORDER BY products_id desc

January 24, 2005

Look at the last part of that error message...you're trying to union different queries.

FYI - http://www.oscommerce.com/community/contributions,2846

Bobby

patator · January 24, 2005

what a plonker I am, I left some code you gave earlier on in the index.php file....it didn't help ! :-" so rry so sorry

anyway, it still doesn't work :

I keep trying to understand what you've done....if I manage to fix the problem, I will let you know straight away.

pat

1054 - Unknown column 'products_ordered' in 'order clause'

SELECT SQL_CALC_FOUND_ROWS p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='1' ) WHERE p.products_id='70' AND p.products_status='1' UNION SELECT p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='1' ) WHERE p.products_id='57' AND p.products_status='1' UNION SELECT p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='1' ) WHERE p.products_id='56' AND p.products_status='1' UNION SELECT p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='1' ) WHERE p.products_id='55' AND p.products_status='1' UNION SELECT p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='1' ) WHERE p.products_id='53' AND p.products_status='1' UNION SELECT p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='1' ) WHERE p.products_id='51' AND p.products_status='1' UNION SELECT p.products_id FROM products p LEFT JOIN products_description pd ON ( p.products_id=pd.products_id AND pd.language_id='1' ) WHERE p.products_id='50' AND p.products_status='1' ORDER BY products_ordered desc LIMIT 0, 20

January 24, 2005

Grab the contribution and just drop the files into their correct directories. Follow the instructions.

Bobby

patator · January 24, 2005

I was just reading the files....

I will send you a donation (as long as the pay pal system is secure for me...)

What's the difference between what you do here with me and what you can propose on your website (50$ -> for helping once or more ?.....)

Thanks

Patrice

Grab the contribution and just drop the files into their correct directories. Follow the instructions.

Bobby

<{POST_SNAPBACK}>

January 24, 2005

We are not allowed to discuss that on the open forum.

Bobby

patator · January 24, 2005

Sorry, I will contact you directly from your website

Appart from that : WELL DONE IT WORKS :thumbsup:

oldworldcharms · July 22, 2005

I placed the split in the center and it shows Pages: 1 2 3

I would like to have Next Page - Previous Page in the next table under. Does anyone know how I can do this?

Thanks

Elizabeth

oldworldcharms · July 22, 2005

Sorry this might help explain what I want to do better

if ( ($listing_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3')) ) {

?>

<tr>

<td class="main" align="center"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>

</tr>

<tr>

</tr>

</table>

Thanks

Elizabeth

oldworldcharms · August 15, 2005

Can someone help with this. I am worried that customers may not see the split page links and would like to make it more visible.

I have the product listing page set up to display split page number and would like the next line to have previous - next links. like this

oldworldcharms · August 15, 2005

Can someone help with this. I am worried that customers may not see the split page links and would like to make it more visible.

I have the product listing page set up to display split page number and would like the next line to have previous - next links. like this

http://oldworldcharms.net/images/icons/union_2.jpg

osjunkie · August 20, 2005

I suppose I am really sleepy today :blink: but I am like having a stupid time trying to install this into my index page lol.

Can some post a example of what is correct? I need this because I have 500 categories with 60,000 products. Some pages take 90 seconds! lol

I want to see if this will solve the problem.

Just need the index.php page.

Much appreciated.

Brad :thumbsup:

redrum · August 22, 2005

Just need the index.php page.

<{POST_SNAPBACK}>

I believe you should replace

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

with

<?php
include('includes/classes/union_split_page_results.php');
include('includes/modules/product_listing_union.php');
?>

Let me know if it works

// Fredrik

osjunkie · August 22, 2005

I believe you should replace
<?php ?include(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING); ?>
with
<?php
include('includes/classes/union_split_page_results.php');
include('includes/modules/product_listing_union.php');
?>
Let me know if it works

// Fredrik

<{POST_SNAPBACK}>

I get the error: Warning: Invalid argument supplied for foreach() in /home/xxxx/public_html/catalog/includes/modules/product_listing_union.php on line 39

Doesn't make sense... Everything looks ok.

:-"

redrum · August 22, 2005

I get the error: Warning: Invalid argument supplied for foreach() in /home/xxxx/public_html/catalog/includes/modules/product_listing_union.php on line 39

<{POST_SNAPBACK}>

I get the same error message.

// Fredrik

Union Query for product listing

Recommended Posts

patator

Guest

patator

patator

patator

Guest

patator

Guest

patator

Guest

patator

Guest

patator

oldworldcharms

oldworldcharms

oldworldcharms

oldworldcharms

osjunkie

redrum

osjunkie

redrum

Archived

Browse

Activity