Search Result orderBy ListingsCourt in homepages

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:

1. Add this to URL of viewer: ?orderBy=listingsCourt
2. Add this to URL of viewer: ?orderBy=listingsCourt+DESC
3. 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!

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

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.

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.