Search Result orderBy ListingsCourt in homepages
6 posts by 2 authors in: Forums > CMS Builder
Last Post: November 19, 2013 (RSS)
By Tom - November 11, 2013 - edited: November 14, 2013
Hello,
Is it possible to display a search result by listingsCourt?
For example, after the visitor made some search enquiry.
The search result displays 5 agents
Normally the result will be displayed in alphabetical order as default in CMS
e.g.
Amy have 5 listings
Billy have 2 listings
David have 1 listings
Jimmy have 8 listings
Kenny have 10 listing
----------------------------------
However, is it possible to display the result in listingscourt order?
-----------------------------------
Kenny - 10 listings
Jimmy - 8 listings
Amy - 5 listings
Billy - 2 listings
David - 1 listings
--------------------------------------
I did try to use orderBY whatever listingsCourt ASC or DESC ,but it's not work.
<?php
require_once "init.php";
list($homepageRecords, $homepageDetails) = getRecords(array(
'tableName' => 'homepages',
'perPage' => '30',
'orderby' => 'listingsCourt',
));
list($listingRecords, $listingDetails) = getRecords(array(
'tableName' => 'listings',
'where' => "agent LIKE '%\t" . getNumberFromEndOfUrl()."\t%'",
));
?>
$homepage = @$homepageRecords[0]; // get first record
if (!$homepage) { print "Homepage not found!"; exit; }
?>
<?php foreach ($homepageRecords as $homepage): ?>
<?php
$where = "agent LIKE '%\t".intval($homepage['num'])."\t%'";
$listingsCount = mysql_select_count_from('listings', $where);
?>
Thanks a lot and sorry for my poor English
By Dave - November 14, 2013
Hi Tom,
Is listingsCourt a field in your database? Or a value you are calculating by calling MySQL Select?
If it's an actual field, you can sort on it by one of these ways:
- Add this to URL of viewer: ?orderBy=listingsCourt
- Add this to URL of viewer: ?orderBy=listingsCourt+DESC
- add getRecords option: 'orderBy' => "listingsCourt",
However, if it's not a field then there's no easy way and you'd have to write a custom MySQL query to load the data or you could create a text field with "listingsCourt" and update it every time the page loads (which isn't optimized but is what you are doing already. Lastly, if that's the case, let me know what the value of agent is. Is it always just a number? Or is it a multi-value field?
Thanks!
interactivetools.com
By Tom - November 14, 2013 - edited: November 16, 2013
Hello Dave,
Thanks for your reply.
I have 2 tables ---- homepages and listings
listingscourt is not a actual field, it just returns the number of records found, not the actual records themselves.
I have a textfield in homepages -- agentname
In listings --- A pulldown (multi value) field label -- agent
which uses the hompages values(num) and labels(agentname) , I use multi value since sometimes they have co-listing.
----------------------------------------------
In the homepages viewer
Normally it displays as below
A --- 2 Listings
B --- 10 Listings
C --- 5 Listings
----------------------------------------------
And I intend to be displayed below
B --- 10 Listings
C --- 5 Listings
A --- 2 Listings
i.e. displays order by the number of listing desc
----------------------------------------------------------------
Below is the current code I used
----------------------------------------------------------------
<?php
require_once "init.php";
list($homepageRecords, $homepageDetails) = getRecords(array(
'tableName' => 'homepages',
'perPage' => '30',
));
list($listingRecords, $listingDetails) = getRecords(array(
'tableName' => 'listings',
'where' => "agent LIKE '%\t" . getNumberFromEndOfUrl()."\t%'",
));
?>
<?php foreach ($homepageRecords as $homepage): ?>
<?php
$where = "agent LIKE '%\t".intval($homepage['num'])."\t%'";
$listingsCount = mysql_select_count_from('listings', $where);
?>
<?php foreach ($homepage['uploads'] as $upload): ?>
<a href="<?php echo $homepage['_link'] ?>"><img src="<?php echo $upload['urlPath'] ?>" title="<?php echo $homepage['agentname'] ?>" alt="<?php echo $homepage['agentname'] ?>" /></a><br />
<a href="<?php echo $homepage['_link'] ?>"><?php echo $homepage['agentname'] ?></a><br/>Total <?php echo $listingsCount; ?> Listing</br>
<?php break /* only show one image */ ?>
<?php endforeach ?>
<?php endforeach ?>
-----------------------------------------------
Thanks a lot
By Dave - November 18, 2013
Hi Tom,
Ok, there's no easy way to do that, and the way you're currently doing it does a mysql query for every user so it will get slow if there's lots of homepages. If it's not too slow on your server, though, you could do actually add a field called 'listingscourt' and then just update it with the actual listing count every time the page is loaded. (Or even better, have it run automatically once an hour, etc.)
If you want to do that, try adding the code in red (code is untested):
<?php
require_once "init.php";
// update listing counts
foreach (mysql_select('homepages') as $homepage) {
$listingsCount = mysql_select_count('listings', "agent LIKE '%\t".intval($homepage['num'])."\t%'");
mysql_update('homepages', $homepage['num'], null, array('listingscourt' => $listingsCount));
}
list($homepageRecords, $homepageDetails) = getRecords(array(
'tableName' => 'homepages',
'perPage' => '30',
));
list($listingRecords, $listingDetails) = getRecords(array(
'tableName' => 'listings',
'where' => "agent LIKE '%\t" . getNumberFromEndOfUrl()."\t%'",
));
?>
And then replace your call to 'mysql_select_count_from' lower in the file with $homepage['listingcourt'].
Then you can sort on it like a regular field.
Let me know if that works for you.
interactivetools.com
By Tom - November 19, 2013 - edited: November 19, 2013
Hello Dave,
This is great but I think you made some typo mistake on line 6
I think it should be
$listingsCount = mysql_select_count_from('listings', "agent LIKE '%\t".intval($homepage['num'])."\t%'");
Thanks a lot again for your great job.