10 posts by 2 authors in: Forums > CMS Builder
Last Post: January 9, 2013   (RSS)

By weblm - January 2, 2013

I have a multi-record section that lists properties available to rent.  In the properties section, I can have properties be either Active or Inactive based on a dropdown.

I have another multi-record section that I add records to include a photo and link to a property.  This section is used to randomly include 3 properties in the sidebar of the site.  That section in the admin has the owner choose a property from a dropdown.  That dropdown is populated as a list field pulling from the properties section.

Is there anyway, in my query to pull in rand property photos, to have it skip any properties that in the properties table are set to inactive?  I'd like to do this in the sql query if possible. 

At the top of my subpage, this is the query used:

// load records from 'sidebar_photos'
list($sidebar_PhotosRecords, $sidebar_PhotosMetaData) = getRecords(array(
'tableName' => 'sidebar_photos',
'orderBy' => 'RAND()',
'loadUploads' => true,
'allowSearch' => false,
'limit' => '3',
));

Basicallly (in english), I'd like to have a where clause that references the properties table and checks to see if each property it's trying to select is only set to active.

Is this possible?

-Kevin

Hi Kevin,

I think the getRecords variable that you need to use is leftJoin, here are some notes on how it can be used: 

'leftJoin' => array( // Note: leftJoins require you to use fully qualified fieldnames in WHERE and ORDER BY, such as tablename.fieldname
  'grocery_aisle' => 'aisleNum', // foreign table => local field (that matches num in foreign table)
  'brands' => 'brandNum',
  'otherTable' => 'ON mainTable.foreignKey = foreignTable.num',
)

With the example you've given, I think you'll need to implement roughly it like this:

// load record from 'category'
list($sidebar_PhotosRecords, $sidebar_PhotosMetaData) = getRecords(array(
  'tableName' => 'sidebar_photos',
  'loadUploads' => true,
  'allowSearch' => false,
  'orderBy' => 'RAND()',
  'limit' => '3',
  'leftJoin' => array( // Note: leftJoins require you to use fully qualified fieldnames in WHERE and ORDER BY, such as tablename.fieldname
  'properties' => 'dropDownField', // foreign table => local field (that matches num in foreign table)
)
));

die(showme($sidebar_PhotosRecords));

=========================

This is just an example, so you will need to change the names of the fields in the leftJoin array to match what you've created.

Let me know if you have any problems implemeting this.

Thanks!

Greg

Greg Thomas

PHP Programmer - interactivetools.com

Hi Kevin,

I'll have a look for you, but I'll need to know what the name of the field is that's a dropdown that is populated as a list field pulling from the properties section?

Thanks!

Greg

Greg Thomas

PHP Programmer - interactivetools.com

By weblm - January 7, 2013

Greg,

The name of the field in the sidebar_photos table is:  property

This is a list field.  It is configured as Get options for a database (advanced) from:

Section Tablename: property_listings
Use this field for option values: num
Use this field for option labels: property_name

Let me know if that helps.

-Kevin

Hi Kevin, 

I think you code should look something like this:

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
/* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */

// 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 record from 'category'
list($sidebar_PhotosRecords, $sidebar_PhotosMetaData) = getRecords(array(
'tableName' => 'sidebar_photos',
'loadUploads' => true,
'allowSearch' => false,
'orderBy' => 'RAND()',
'limit' => '3',
'leftJoin' => array( // Note: leftJoins require you to use fully qualified fieldnames in WHERE and ORDER BY, such as tablename.fieldname
'properties' => 'property', // foreign table => local field (that matches num in foreign table)
)
));

//cycle through the contents of the sidebar_photos array.
foreach($sidebar_PhotosRecords as $row){
//This should display the status of a property, eg: active/inactive
echo $row['properties.status'].'<br>';
//If the property status is Active, then display it's contents
if($row['properties.status'] == "Active"){
//If the record is active, then it's contents should be displayed below
showme($row);
}
}

This should display all of the array contents for entries that have a status of Active. 

Let me know if you get any errors.

Thanks

Greg

Greg Thomas

PHP Programmer - interactivetools.com

By weblm - January 8, 2013

Greg,

Thanks so much.....I got this working.....sort of :-)

Only problem I have now is.....I was limiting the sidebar photos to 3 in the SQL query.  What's happening now is.....the query is randomly selecting 3....however, it could be selecting one that's not active....so on the display end, I might only show 2 photos because the check to see if it's active would skip the inactive one.

Any ideas?  Should I just get all the sidebar photos and then in the display page limit it there, instead of in the query?

-Kevin

By weblm - January 8, 2013

Greg,

For now I just used a break in the foreach loop to limit it to showing 3 records:

<?php $i = 1; ?>
<?php foreach ($sidebar_PhotosRecords as $row): ?>

<?php if ($i>'3'): /*only show 3 photos*/ ?>
<?php break; ?>
<?php endif ?>

<?php if ($row['property_listings.status'] == "Active"): ?>

<?php
$propertyLink = preg_replace('/[^a-z0-9\.\-\_]/i', '-', $row['property:label']);
$propertyLink = preg_replace("/(^-+|-+$)/", '', $propertyLink);
?>

<div class="rightFeature">
<a href="/utah-vacation-rental-home-detail.php?<?php echo $propertyLink ?>">
<?php foreach ($row['photo'] as $index => $upload): ?><img src="<?php echo $upload['urlPath'] ?>" width="240" height="180" border="0" alt="" title="" /><?php endforeach ?>
<span><?php echo $row['property:label'] ?></span>
</a>
</div>

<?php $i++; ?>
<?php endif ?>

<?php endforeach ?>

-Kevin

Hi Kevin,

I should have mentioned that you can use the new fields that are created in the where statement of your getRecords function as well. Something like this should work:

// load record from 'category'
list($sidebar_PhotosRecords, $sidebar_PhotosMetaData) = getRecords(array(
'tableName' => 'sidebar_photos',
'loadUploads' => true,
'where' => "properties.status = 'Active'",
'allowSearch' => false,
'orderBy' => 'RAND()',
'limit' => '3',
'leftJoin' => array( // Note: leftJoins require you to use fully qualified fieldnames in WHERE and ORDER BY, such as tablename.fieldname
'properties' => 'property', // foreign table => local field (that matches num in foreign table)
)
));

So this where statement should only return sidebar_photos with a property status of Active.  

Thanks!

Greg

Greg Thomas

PHP Programmer - interactivetools.com

By weblm - January 9, 2013

Well that's awesome!  And works perfectly.

Thanks so much for all your help Greg.....it's been invaluable.

-Kevin