Odd Price Ordering Behaviour

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

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/

Re: [Jason] Odd Price Ordering Behaviour

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

That worked but now I have another problem, it is placing products with no field data (i.e. no price information / not relevant) at the front of the list instead of the back. How can I add an exclusion to say that fields with only a default value (I use "?" when no data is entered because a product doesn't exist) be put at the back and not the front. Right now people just see several pages of products that don't have data at the front with only "?" as the default value.


UPDATE: Also just realised that the code you gave broke my list anyway by over-riding the default 'title' ordering from admincp, thus it went back to record number based ordering when not clicking my custom order-by links (as per first post).