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 Tom - November 14, 2013

Anyone can HELP??

Thanks a lot

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&nbsp;<?php echo $listingsCount; ?>&nbsp;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.

Dave Edis - Senior Developer
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.