Reference another table in WHERE clause

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, thanks for posting.

I'm still a bit confused on your example.  I thought maybe if I give you the actual database names, and fields, you could help with the proper syntax.

My properties table is called: property_listings
The field name for status is called: status
It is a list field type with 2 possiblities: Active or Inactive

I'm still not sure how to integrate that into the sidebar_photos sql call that gets the photos.  This section has a dropdown for property which is gets from the propert_listings table.  It obviously uses the num for the reference.

Let me know if this is helping.

-Kevin

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,

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,

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

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