Search Form to Control orderBy and perPage functions

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.

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

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?

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

Hi Andy

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

Hi Claire

Great - that was it!

For anyone reading this, the code that works is as follows:-

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

Thanks for your help and perseverance with this Claire - great support!

Now I will try the listings per page code and get back to you.

Kind regards

Andy