Search Form to Control orderBy and perPage functions

7 posts by 2 authors in: Forums > CMS Builder
Last Post: August 1, 2014   (RSS)

By andybarn - July 27, 2014

Hi

I have 2 questions which I hope you can help me with.

Question 1

I have  a search form for a property website and I want to order the results by price.

I create a hidden field in my search form as below:

<input type ="hidden" name ="orderBy" value ="price" />

This seems to work in ascending order, however it appears to ignore the thousand delimiter and orders the price as follows:


1,250,000
895,000
89,700
90,000
95,000
995,000

How can I put a hidden field in the search form so that it would order as follows:-

89,700
90,000
95,000
895,000
995,000
1,250,000

Now I know in the header I could put the following statement

'orderBy'     => 'price+0'

But that would override whatever I put in the search form. I need the search form
to control the 'orderBy' function,

Is this possible and if so could you show me how?

Question 2

I also have a search form that I want to control the number of listings per page.

Is there a command I could put in the search form that would do this. This could either
be a hidden input or a drop box select option.

Again I know I could put the following statement in the header

'perPage'     => '10'

But that would override whatever I put in the search form. I need the search form to control
the 'perPage ' function.

I can’t seem to find anything that would do this in a search form.

Is this possible and if so could you show me how?

Thanks for your help.
Andy

By claire - July 28, 2014

Hi Andy

For the first question - you'll need to tell the database to order the records as if the price were a number, not a text string. So try changing the orderBy line to this:

'orderBy='     => 'CAST(price AS UNSIGNED)'

For the second question - what you'd need to do is add a GET parameter to the URL that specifies the number of listings per page, then check for it and insert it as needed in every link on the page. This really depends on your level of experience with PHP, as there's no quick way of adding it. I'll see if I can find an example for you.

--------------------

Claire Ryan
interactivetools.com

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

By andybarn - July 29, 2014

Hi Claire

Thanks for your suggestion.

Regarding first question

When I add

'orderBy='     => 'CAST(price AS UNSIGNED)'

I get the error message

Unknown option 'orderBy=' specified.  Valid option names are: 
(tableName, where, orWhere, orderBy, limit, offset, perPage, 
loadUploads, allowSearch, requireSearchMatch, loadCreatedBy, useSeoUrls,
 loadListDetails, joinTable, debugSql, leftJoin, useCache, pageNum, 
ignoreHidden, ignorePublishDate, ignoreRemoveDate, requireSearchSuffix, 
includeDisabledAccounts, addSelectExpr, groupBy, having, 
loadPseudoFields)

I think the problem was with the "=" sign after the "orderBy". Therefore I then changed to

'orderBy'     => 'CAST(price AS UNSIGNED)'

And this worked fine, the results pages were displayed in the correct price order which by default seems to be ascending

However the question was how can I put this command in a search form. The reason for this is that once the results are displayed, I have a small search form that will allow you to reorder the results either by ascending or descending price order as follows:-

<form method="post" class="sky-form sky-formnb" action="http://37.220.93.134/~javeahom/property/listings.php">                   
                <input type = "hidden" name = "town" value = "<?php echo @$_REQUEST['town'];?>" /> 
                <input type = "hidden" name = "property_type" value = "<?php echo @$_REQUEST['property_type'];?>" /> 
                <input type = "hidden" name = "bedrooms_min" value = "<?php echo @$_REQUEST['bedrooms_min'];?>" />                        
                <input type = "hidden" name = "price_min" value = "<?php echo @$_REQUEST['price_min'];?>" />                        
                <input type = "hidden" name = "price_max" value = "<?php echo @$_REQUEST['price_max'] ?>" />                        
                <input type = "hidden" name = "reference_number_keyword" value = "<?php echo @$_REQUEST['reference_number_keyword'];?>" />
                        <div class="row">
                            <label class="select">
                                <select name = "orderBy"  onchange="this.form.submit();">
                                    <option value = "price">Price Order</option>
                                    <option value = "price">Price: Low to High</option>
                                    <option value = "price DESC">Price: High to Low</option>
                                </select>
                            </label>
                        </div>
                   </form>


Now the sort order remains the same, no matter what I select here. It is as if the orderBy statement at the top of the page is overriding whatever I select here and just displays the results in ascending order

If I remove the orderBy statement at the top of the page, the form works correctly on the results page but we are back to the original problem of the price field being treated as a text string.

Any help here appreciated or is there a better way to do this?

Regarding the 2nd question

What I want to do here is exactly what I am trying to do with the price field and that is give the client the option to change the number of results per page by adding a form to the results page as above. In fact I was hoping to use the exact same form above but add to the form another row as follows, but this does not work (I suspect the syntax is not correct)

                           <label class="select">
                                <select name = "perPage">
                                    <option value="10">Results per page</option>    
                                    <option value="10">10</option>    
                                    <option value="25">25</option>    
                                    <option value="50">50</option>    
                                    <option value="">All</option>    
                                  </select>
                            </label>


In summary, what I want to end up with is a search results page whereby the visitor can then select either the price sort order or the number of listings per page after a search has been conducted. If you look at the page on my development server you will see what I mean.

http://37.220.93.134/~javeahom/property/listings.php

I hope that makes things easier to understand.

Thank you for your help so far, I hope you can help me achieve the desired result.

Kind regards

Andy

By claire - July 30, 2014

Hi Andy

Yeah, I think I understand. For the orderBy form, what you'll have to do is check the $_REQUEST['price'] variable when the page loads, and insert the right kind of ordering.

So, just before the getRecords function, add this:

$orderby = "CAST(price AS UNSIGNED)"; // default
if(@$_REQUEST['orderby'] == 'price DESC') {
    $orderby = "CAST(price AS UNSIGNED) DESC";
}

Then in the getRecords function, change the orderBy like so:

'orderBy'     => $orderby

If you want to do different variations on the ordering, all you need to do is test for them like above and change the $orderby clause as needed.

For the perPage problem, you can do something very similar, like so:

$perPage = 10; // whatever your default needs to be
if(isset($_REQUEST['perPage'])) {
     $perPage = mysql_escape($_REQUEST['perPage']);
}

And the perPage clause in getRecords:

'perPage' => $perPage

Does this help?

--------------------

Claire Ryan
interactivetools.com

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

By andybarn - July 31, 2014

Hi Claire

I thought I would concentrate on the orderby price issue first however I am struggling with this.

I have changed the form method to "get" so I can see what is selected in the URL and also added a php echo command to see the order by value <?php echo @$_REQUEST['orderBy'] ?>

I am still unable to get this to work - sorry

Could you take a look at my page to see if you can spot the problem please?

I attach for you.

You can also see the page in action on my development server at

http://37.220.93.134/~javeahom/property/listings.php

I am sure this would be helpful to other people once we get it working.

Kind regards

Andy

Attachments:

listings.php 26K

By claire - July 31, 2014

Hi Andy

The variable names are a little off. Can you change $_REQUEST['orderby'] to $_REQUEST['orderBy']?

--------------------

Claire Ryan
interactivetools.com

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