Guide: How to add sorting for featured products

[Vladimir7]

Sorry if this is posted in the wrong forum, feel free to move it...
I didn't want to upload this as a contrib yet because I have never done one before, and have only been working with zen cart for a couple weeks.
Also I really suck at creating instructions/documentation
So if anyone wants to rewrite this, or make it a contrib, feel free to (but credit would be nice)

What this does:
Adds a sort field to featured products. It works like how you can sort regular categories and products.



Instructions
Like always, BACK UP your store and database before attempting.
I did this on a stock zen cart, but maybe I missed something or made a mistake as I have only worked with zen for a few weeks.

Step 1: Run this query on the database

ALTER TABLE `featured` ADD `sort_order` INT( 11 ) NOT NULL DEFAULT '0' AFTER `featured_date_available` ; 

Step 2: In /admin/includes/languages/featured.php
Add these 2 defines

define('TABLE_HEADING_SORT_ORDER','Sort');
define('TEXT_FEATURED_SORT_ORDER', 'Sort Order:'); 

Step 3: In /admin/featured.php
At around line 44 in case 'insert' statement
After

$expires_date = ((zen_db_prepare_input($_POST['end']) == '') ? '0001-01-01' : zen_date_raw($_POST['end'])); 

Add

$sort_order = zen_db_prepare_input($_POST['sort_order']); 

Change

$db->Execute("insert into " . TABLE_FEATURED . "
                    (products_id, featured_date_added, expires_date, status, featured_date_available)
                    values ('" . (int)$products_id . "',
                            now(),
                            '" . zen_db_input($expires_date) . "', '1', '" . zen_db_input($featured_date_available) . "')"); 

To

$db->Execute("insert into " . TABLE_FEATURED . "
                    (products_id, featured_date_added, expires_date, status, featured_date_available, sort_order)
                    values ('" . (int)$products_id . "',
                            now(),
                            '" . zen_db_input($expires_date) . "', '1', '" . zen_db_input($featured_date_available) . "', '" . zen_db_input($sort_order) . "')"); 

At around line 64 in case 'update' statement

After

$expires_date = ((zen_db_prepare_input($_POST['end']) == '') ? '0001-01-01' : zen_date_raw($_POST['end'])); 

Add

$sort_order = zen_db_prepare_input($_POST['sort_order']); 

In the

 $db->Execute("update " . TABLE_FEATURED . "
                      set featured_last_modified = now(),
                          expires_date = '" . zen_db_input($expires_date) . "',
                          featured_date_available = '" . zen_db_input($featured_date_available) . "',
                          sort_order = '" . zen_db_input($sort_order) . "'
                      where featured_id = '" . (int)$featured_id . "'"); 

add the sort_order field like it is shown in the statement

At around line 219

 $product = $db->Execute("select p.products_id, pd.products_name, p.products_price, p.products_priced_by_attribute,
                                      f.expires_date, f.featured_date_available, f.sort_order 

Add f.sort_order like shown


Around line 277
After

<tr>
            <td class="main"><?php echo TEXT_FEATURED_EXPIRES_DATE; ?>&nbsp;</td>
            <td class="main"><script language="javascript">EndDate.writeControl(); EndDate.dateFormat="<?php echo DATE_FORMAT_SPIFFYCAL; ?>";</script></td>
          </tr>

Add

<tr>
              <td class="main"><?php echo TEXT_FEATURED_SORT_ORDER;?>&nbsp;</td>
              <td><?php echo zen_draw_input_field("sort_order", $fInfo->sort_order, "size = 3") ?></td>
</tr>

Around line 304
Add

<td class="dataTableHeadingContent" align="center"><?php echo TABLE_HEADING_SORT_ORDER; ?></td>

Around line 316 change

$order_by = " order by pd.products_name "; 

to

$order_by = " order by f.sort_order "; 

Around line 319 change

$featured_query_raw = "select p.products_id, pd.products_name, p.products_model, p.products_price, p.products_priced_by_attribute, f.featured_id, f.featured_date_added, f.featured_last_modified, f.expires_date, f.date_status_change, f.status, f.featured_date_available from " . TABLE_PRODUCTS . " p, " . TABLE_FEATURED . " f, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p.products_id = f.products_id"  . $search . $order_by; 

To

$featured_query_raw = "select p.products_id, pd.products_name, p.products_model, p.products_price, p.products_priced_by_attribute, f.featured_id, f.featured_date_added, f.featured_last_modified, f.expires_date, f.date_status_change, f.status, f.featured_date_available, f.sort_order from " . TABLE_PRODUCTS . " p, " . TABLE_FEATURED . " f, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p.products_id = f.products_id"  . $search . $order_by; 

(Only added ", f.sort_order" to select)


Around line 353
Add

<td class="dataTableContent" align="center"><?php echo $featured->fields['sort_order'];?></td>

right before

<td class="dataTableContent" align="right">
                  <?php echo '<a href="' . zen_href_link(FILENAME_FEATURED, 'page=' . $_GET['page'] . '&fID=' 
)

Step 4:In /includes/modules/YOUR_TEMPLATE/featured_products.php

At the end of

$featured_products_query = "select distinct p.products_id, p.products_image, pd.products_name, p.master_categories_id
                           from (" . TABLE_PRODUCTS . " p
                           left join " . TABLE_FEATURED . " f on p.products_id = f.products_id
                           left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id )
                           where p.products_id = f.products_id
                           and p.products_id = pd.products_id
                           and p.products_status = 1 and f.status = 1
                           and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' ORDER by f.sort_order"; 

Add the "order by f.sort_order" like shown


At the end of

$featured_products_query = "select distinct p.products_id, p.products_image, pd.products_name, p.master_categories_id
                                from (" . TABLE_PRODUCTS . " p
                                left join " . TABLE_FEATURED . " f on p.products_id = f.products_id
                                left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id)
                                where p.products_id = f.products_id
                                and p.products_id = pd.products_id
                                and p.products_status = 1 and f.status = 1
                                and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                                and p.products_id in (" . $list_of_products . ") order by f.sort_order"; 

Add the "order by f.sort_order" like shown

Change

$featured_products = $db->ExecuteRandomMulti($featured_products_query, MAX_DISPLAY_SEARCH_RESULTS_FEATURED); 

To

$featured_products = $db->Execute($featured_products_query, MAX_DISPLAY_SEARCH_RESULTS_FEATURED); 

Change

$featured_products->MoveNextRandom(); 

to

$featured_products->MoveNext(); 

Step 5:In \includes\modules\pages\featured_products\header_php.php
****NOTE I dont know how to get this working with templates.
I am relatively new to zen cart, so couldn't figure out where the template folder whould go


change

$featured_products_query_raw = "SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call,
                                  p.product_is_always_free_shipping, p.products_qty_box_status,
                                  p.master_categories_id
                                  FROM (" . TABLE_PRODUCTS . " p
                                  LEFT JOIN " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " .
TABLE_PRODUCTS_DESCRIPTION . " pd
                                  LEFT JOIN " . TABLE_FEATURED . " f on pd.products_id = f.products_id )
                                  WHERE p.products_status = 1 and p.products_id = f.products_id and f.status = 1
                                  AND p.products_id = pd.products_id and pd.language_id = :languagesID " .
$order_by; 

To

$featured_products_query_raw = "SELECT p.products_id, p.products_type, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, p.products_model, p.products_quantity, p.products_weight, p.product_is_call,
                                  p.product_is_always_free_shipping, p.products_qty_box_status,
                                  p.master_categories_id
                                  FROM (" . TABLE_PRODUCTS . " p
                                  LEFT JOIN " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " .
TABLE_PRODUCTS_DESCRIPTION . " pd
                                  LEFT JOIN " . TABLE_FEATURED . " f on pd.products_id = f.products_id )
                                  WHERE p.products_status = 1 and p.products_id = f.products_id and f.status = 1
                                  AND p.products_id = pd.products_id and pd.language_id = :languagesID order by f.sort_order "; 

NOTE*** I got rid of the $order_by variable, because I didn't know if it effected anything else, so put it straight into the query

I think that should be it....
I haven't tested it with featured products side boxes because I don't use it