Odd Price Ordering Behaviour

11 posts by 4 authors in: Forums > CMS Builder
Last Post: June 29, 2010   (RSS)

By mark99 - June 23, 2010 - edited: June 23, 2010

I am about to put my new listing system live but have stumbled accross an annoying bug that doesn't make much sense.

http://www.ispreview.co.uk/isp_list/ISP_List_Fixed_Line_Broadband.php

At the top I have an "ORDER LIST BY: Price (Premium Packages) | Price (Cheapest Packages)" feature that is structured as follows:

<b>ORDER LIST BY: <a href="ISP_List_Fixed_Line_Broadband.php?orderBy=<?php echo (@$_REQUEST['orderBy']!="land_line_price")? "land_line_price" : "land_line_price+DESC" ?>">Price (Premium Packages)</a> | <a href="ISP_List_Fixed_Line_Broadband.php?orderBy=<?php echo (@$_REQUEST['orderBy']!="land_line_cheap_price")? "land_line_cheap_price" : "land_line_cheap_price+DESC" ?>">Price (Cheapest Packages)</a></b>

The problem it has is that any value over 10.00 is correctly ordered but any value of less than 10 seems to be put at the bottom, even when the lowest prices are supposed to order towards the top. So, for example, if you click "Price (Cheapest Packages)" then all the "Cheapest Package" products will order from cheapest to most expensive. This appears to work fine, but notice how it orders packages that cost £10 to the top (lowest price). Seems fair until you skip to page 5 and realise that all the sub-£10 packages are coming after the most expensive ones? It makes no sense.

http://www.ispreview.co.uk/isp_list/ISP_List_Fixed_Line_Broadband.php?orderBy=land_line_cheap_price&page=5


^ Scroll to the bottom - PlusNet at £6.49 starts after OrchidServe on 29.27. Weird. I am on v2.04.

Re: [mark99] Odd Price Ordering Behaviour

By zip222 - June 23, 2010

Take a look at this topic and see if it answers your question. There is a solution in the middle of the discussion that ended up working for me.

http://www.interactivetools.com/forum/gforum.cgi?post=70820;#70820

Re: [mark99] Odd Price Ordering Behaviour

By Jason - June 23, 2010

Hi,

If you're still running into a problem, if you could email your CMS Login and FTP details to jason@interactivetools.com I can take a closer look.

Please only email this information, don't post it to the forum.

Thanks.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Odd Price Ordering Behaviour

By mark99 - June 24, 2010 - edited: June 24, 2010

Right now my price field is a "textfield" and I think what's needed is to change the field so that it uses a Mysql Column Type like NUMERIC, but I don't know how to do that.

I also saw that this worked for somebody but I don't know how to work that into my viewer code (first post) "date_awarded DESC, category, (grant_amount+0) DESC, title"

Re: [mark99] Odd Price Ordering Behaviour

By Jason - June 24, 2010

Hi,

Is the "£" symbol being stored in the text field? If so, this could be what's affecting the sorting. One thing you could try is to go to the section editor and modify those fields. In the "Input Validation" section, you can "Disallow characters".

Give that a try. If you're still running into trouble, please email your CMS Login and FTP Details to jason@interactivetools.com and I'll take a closer look.

Thanks.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Odd Price Ordering Behaviour

By mark99 - June 24, 2010

No that symbol is on the HTML and doesn't touch the field. The field itself just contains numerical data like 6.49 or 27.45 etc.

Re: [mark99] Odd Price Ordering Behaviour

By Chris - June 24, 2010

Hi there,

I think zip222 has it. I think the problem is that the sort is alphabetical instead of numeric. You don't need to change the MySQL field type to fix this, you just need to add a "+ 0" to the ORDER BY.

Can you please post the complete PHP source code to your page? (Or, at least the getRecords() call?) It may be simpler to change this with some PHP right before the getRecords() call instead of changing all your links.
All the best,
Chris

Re: [chris] Odd Price Ordering Behaviour

By mark99 - June 28, 2010 - edited: June 28, 2010

The header call goes a bit like this:

list($isp_listRecords, $isp_listMetaData) = getRecords(array(
'tableName' => 'isp_list',
'perPage' => '15',
'where' => " category LIKE '%Fixed Line Broadband%' ",
'loadCreatedBy' => false,
));

There's nothing sophisticated about the output itself, I'm just doing "foreach ($isp_listRecords as $record):" and following it with specific records output that is repeated for each product as you might imagine.

In the adminCP it is crucial that I have priority to "Order By" 'title' and have removed the DragSortOrder stuff because that was preventing A-Z ordering.

Can't I just add the "+ 0" somehow to my original code (first post above)? I don't really want to apply a general rule to the whole page if it's going to screw up my searching options. In any case I am surprise this is so tedious to solve, sorting should be something that MySQL does easily.

Re: [mark99] Odd Price Ordering Behaviour

By Jason - June 28, 2010

Hi,

You could try something like this:

$order=@$_REQUEST['orderBy'];
if($order=="land_line_cheap_price" || $order=="land_line_cheap_price DESC"){
$order = str_replace("land_line_cheap_price","land_line_cheap_price+0",$order);
}
else{
$order = str_replace("land_line_price","land_line_price+0",$order);
}

list($isp_listRecords, $isp_listMetaData) = getRecords(array(
'tableName' => 'isp_list',
'perPage' => '15',
'where' => " category LIKE '%Fixed Line Broadband%' ",
'orderBy => "$order",
'loadCreatedBy' => false,
));

This code will add a +0 or the field name. This code will not affect any other part of the code.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/