X-Cart forums - How TO Order Products List By Field

X-Cart forums (https://forum.x-cart.com/index.php)

- Dev Questions (https://forum.x-cart.com/forumdisplay.php?f=20)

- - How TO Order Products List By Field (https://forum.x-cart.com/showthread.php?t=802)

How TO Order Products List By Field

Hello, i have a lot of products on my site and i wish to organize them. How to i arrange them by price...with out having to do it one by one? Thanks.

There is a fair amount of info on this on this thread. http://forum.x-cart.com/viewtopic.php?t=677&highlight=

The file to amend is the the customer/products.php file

Line 61 is:

Code:

$search_query = "($sql_tbl[products].categoryid='$cat' or $sql_tbl[products].categoryid1='$cat' or $sql_tbl[products].categoryid2='$cat' or $sql_tbl[products].categoryid3='$cat') and $sql_tbl[products].forsale='Y' group by $sql_tbl[products].productid order by $sql_tbl[products].orderby ".($config["General"]["product_order_reversed"]=="Y"?"desc":"asc")." limit $first_page, ".$config["General"]["products_per_page"];

Change this to:

Code:

$search_query = "($sql_tbl[products].categoryid='$cat' or $sql_tbl[products].categoryid1='$cat' or $sql_tbl[products].categoryid2='$cat' or $sql_tbl[products].categoryid3='$cat') and $sql_tbl[products].forsale='Y' group by $sql_tbl[products].productid order by $sql_tbl[pricing].price ".($config["General"]["product_order_reversed"]=="Y"?"desc":"asc")." limit $first_page, ".$config["General"]["products_per_page"];

I have left this with the product order reversed bit in so that you can set to increase or decrease through the admin centre.

You will also need to amend the featured products php file in the same way if you want that to display in a similar way.

Enjoy.

thanks funkydunk, but i can't find the customer/products.php file. step by step how do i get to that template? Thanks for your patience.

Sorry,

You cant get to it through the admin area in x-cart, you need to open up the products.php file (either your local version or the one on the server) in a text editor. I use ultraedit (it highlights the php code very well).

You can then make any amendments needed.

I posted a similar question in the MySQL forum, and just found the solution overhere.

Nevertheless, I tried this code in the products.php file and I received this error:

INVALID SQL: 1064 : You have an error in your SQL syntax near 'group by xcart_products.productid limit 45' at line 1
SQL QUERY FAILURE: select xcart_products.*, xcart_categories.category, min(xcart_pricing.price) as price from xcart_products, xcart_pricing, xcart_categories where xcart_pricing.productid=xcart_products.productid and xcart_pricing.quantity=1 and xcart_products.categoryid=xcart_categories.categor yid AND (xcart_categories.membership='' OR xcart_categories.membership='') and (xcart_pricing.membership='' or xcart_pricing.membership='') and (xcart_products.categoryid='46' or xcart_products.categoryid1='46' or xcart_products.categoryid2='46' or xcart_products.categoryid3='46') and xcart_products.forsale='Y' group by xcart_products.productid order by xcart_pricing.price asc limit 0, group by xcart_products.productid limit 45

Please can anybody help me out, cause I am pulling my hair out for the last couple of days!

I really would like to have my products sorted by price!!

thx!

what version are you using?

the code posted above was for a really old version and may not work with newer xcarts.

Hi Funkydunk,

Currently I am upgrading form 2.3.0 to 3.4.3 Better to say that I am installing a complete new cart.... 8O

My line in teh products.php looks like:

Code:

$search_query = "($sql_tbl[products].categoryid='$cat' or $sql_tbl[products].categoryid1='$cat' or $sql_tbl[products].categoryid2='$cat' or $sql_tbl[products].categoryid3='$cat') and $sql_tbl[products].forsale='Y'";

I added at the end:

Code:

group by $sql_tbl[products].productid order by $sql_tbl[pricing].price ".($config["General"]["product_order_reversed"]=="Y"?"desc":"asc")." limit $first_page, ".$config["General"]["products_per_page"];

but this will result in the error previously described.

First I thought it was the " that was missing at the end...But I received a parse error when adding the quotationmark.

It's puzzling me...

:(

Little known secret shhhh... :wink:

but you can get it do do an ascending sort by price by adding a link to the product page with additional variable of:

Code:

&orderby=price

Job done :)

same goes with alphabetically

Code:

&orderby=product

Now for one site I just added a table into the top of the products.tpl such as:

Code:

{* custom mod *}

{if $products ne ""}

<table border="0" cellspacing="0" cellpadding="2" width="100%">

<tr>

<td valign="top" align="center" colspan="4">

Sort by:

</td>

</tr>

<tr>

<td valign="top" align="center">

Price

</td>

<td valign="top" align="center">

Product Name

</td>

<td valign="top" align="center">

Brand

</td>

<td valign="top" align="center">

Date Added

</td>

</tr>

</table>











{/if}

{* end of custom mod *}

Now this uses the brand field in the database but xcart does not have that switched on as standard.

You will also need to add:

Code:

// funkydunk mod - added in so that sorting can carry forward across the pages

$smarty->assign("orderby",$orderby);

into the bottom of customer/products.php

and change navigation.tpl to the following so that the orderby variable is carried over to the next page:

Code:

{* $Id: navigation.tpl,v 1.6 2002/10/22 10:27:05 olga Exp $ *}

{if $total_pages gt 2}

<table cellpadding=0 border=0>

<tr height=14>

{section name=page loop=$total_pages start=1}

{if %page.index% eq 1}

<td>{$lng.lbl_result_pages}: </td>

{if $navigation_page gt 1}

<td valign=middle>[img]{$ImagesDir}/larrow.gif[/img]</td>

{/if}

{/if}

{if %page.index% eq $navigation_page}

<td align=center>

{%page.index%}

</td>

{else}

<td width=17 background="{$ImagesDir}/page.gif" align=center>

{%page.index%}

</td>

{/if}

{if %page.last%}

{capture name=total_pages_minus}

{math equation="pages-1" pages=$total_pages}

{/capture}

{if $navigation_page lt $smarty.capture.total_pages_minus}

<td valign=middle>[img]{$ImagesDir}/rarrow.gif[/img]</td>

{/if}

{/if}

{/section}

</tr></table>








{/if}

He Funkydunk,

a million, man! I placed the code in the proper files.

I started with

Code:

&orderby=price

I did not really get it where to place it. What I did is the following:

Code:

$search_query = "($sql_tbl[products].categoryid='$cat' or $sql_tbl[products].categoryid1='$cat' or $sql_tbl[products].categoryid2='$cat' or $sql_tbl[products].categoryid3='$cat') and $sql_tbl[products].forsale='Y' &orderby=price";

As you can see I put it at the end as an argument. Not sure if that's ok....Well I am sure that it is nog OK, because it is not working.... :wink: :?

The order by price argument doesn't bring up any errormessage but no products will show up.

I reckon I made a mistake here, ah... :oops: What did I do wrong???

The rest of your mods seem to be fine. Thanks a lot...very usefull mods!
[/quote]

OK...I got a little bit further....

Now I changed the query in products.php into:

Code:

$search_query = "($sql_tbl[products].categoryid='$cat' or $sql_tbl[products].categoryid1='$cat' or $sql_tbl[products].categoryid2='$cat' or $sql_tbl[products].categoryid3='$cat') and $sql_tbl[products].forsale='Y' &orderby=price &orderby=product ";

This seems to work.... Products will show up alphabetically. When I leave "&orderby=products"... I'll get no searchresults. The same happens when I skip "&orderby=price"....No results... :o

I am running bazurk! :roll: