Help with query and sorting

Hi Kevin, 

There is a way you can do this with one getRecords function using the :label meta field for your drop down title. I would do something like this:

// load viewer library
$libraryPath = 'cmsAdmin/lib/viewer_functions.php';
$dirsToCheck = array('C:/wamp/www/','','../','../../','../../../');
foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

// load records from 'reviews'
list($reviewRecords, $reviewMetaData) = getRecords(array(
'tableName' => 'reviews',
'loadUploads' => true,
'allowSearch' => false,
));

//Create an array that only contains the review items.
$reviewItems = array();
foreach($reviewRecords as $record){
//check if propery already is already in the review Items array, if not, add it to it.
if(!in_array($record['property:label'], $reviewItems) && $record['property'] > 0){
//Set the num value to the key, and set what is being stored in the label as the record value
$reviewItems[$record['property']] = $record['property:label'];
}
}
//Sort the items in the array alphabetically
sort($reviewItems);

echo ''."\n";
echo 'All Properties'."\n";

foreach ($reviewItems as $numValue => $reviewTitle) {
echo ''.$reviewTitle.''."\n";
}

echo ''."\n";

You might have to change some of the variables ( for example, section or field names) to get this working.

Thanks!

Greg

Greg....you rock.

Two things:

1) Is there a way in that to obtain the property num?  That's the value I need in my select list as the value to re-sort the page.  Since the reviews table stores the property num, not the name.
2) Is there anyway to check to see if the property is Active and if not, don't include it in the list.

Now with your awesome left join code....I was able to change my reviews page to only show reviews for properties that are Active.  In doing that I changed my original code from this:

$reviewedProperties = mysql_query_fetch_all_assoc("SELECT num, property_name FROM {$TABLE_PREFIX}property_listings WHERE num='$reviewedPropertyNumrecord[property]'");

To this:

$reviewedProperties = mysql_query_fetch_all_assoc("SELECT num, property_name FROM {$TABLE_PREFIX}property_listings WHERE num='$reviewedPropertyNumrecord[property]' AND status='Active' ORDER BY property_name"); 

Thanks for all your help.

-Kevin

Hi Kevin,

1) If you've set up the property field in the reviews section so that the property num value is the value, and the property name/title is the label of each drop down entry (which I think you have looking at your code above). Then the field 'property field for each record should contain the property num value. I've highlighted in red values that I think should contain the num value in blue below.

2)This can be done with the trusty left join feature. I've added a left join statement to the getRecords function, and then used a where statement to filter out entires that arn't active. 

// load records from 'category'
list($reviewRecords, $reviewMetaData) = getRecords(array(
'tableName' => 'reviews',
'loadUploads' => true,
'where' => "property_listings.status = 'Active'",
'allowSearch' => false,
'leftJoin' => array(
'property_listings ' => 'property', // foreign table => local field (that matches num in foreign table)
)
));

//Create an array that only contains the review items.
$reviewItems = array();
foreach($reviewRecords as $record){
if(!in_array($record['property:label'], $reviewItems) && $record['property'] > 0){
$reviewItems[$record['property']] = $record['property:label'];
}
}
sort($reviewItems);

echo ''."\n";
echo 'All Properties'."\n";

foreach ($reviewItems as $numValue => $reviewTitle) {

echo '<option value="'.htmlentities($_SERVER['PHP_SELF']).'?property='.$numValue.'">'.$reviewTitle.'</option>'."\n";

}

echo ''."\n";

You will probably need to modify the code to use the correct variables like before. 

Cheers

Greg

Hi Greg,

Yes, in the reviews section.....the property field is just the number of the property  (from the num field in the property_listings table).

What I'm getting with the value $numValue is array numbers from the NEW array....not the original values.  So for example, the $reviewItems array that is created below looks like this with a showme($reviewItems);

Array(
[0] => Alpine Creek #3
[1] => Alpine Creek #4
[2] => Aspenglow
[3] => Chez Louisa
[4] => Creekside
[5] => Great Western
)

Make sense?

Let me know your thoughts.

-Kevin

Greg......thank you SO much.  You have no idea how helpful you've been to me.  Do you work for Interactive Tools?  It's hard to tell in the new forum without the avatars like the old forum had.

This worked perfectly for me.

The only thing I added, just for anyone else's reference is.....we have some homes that start with A, or The.  In the MySQL queries, I'm able to orderby, by ignoring the 'The'.   But because we have to get this list and have PHP sort it.....I came across this bit of code that works:

# move a,an,the to the end of string
$reviewItems = preg_replace('/^(a|an|the) (.*)/i', "$2\x00$1", $reviewItems);

#asort keeps the original key values
asort($reviewItems);

# move a,an,the back to the beginning
$reviewItems = preg_replace('/^(.*?)\x00(.*)/', "$2 $1", $reviewItems);

Thanks again...you've been a HUGE help to me on this project.  

-Kevin

Hi Kevin,

Not a problem! I am part of the Interactive Tools team, so it's all in a days work. :-)

Thanks

Greg