Reference another table in WHERE clause

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

LM

By gregThomas - January 3, 2013

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

By weblm - January 7, 2013

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

LM

By gregThomas - January 7, 2013

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

LM

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

LM

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

LM

By gregThomas - January 9, 2013

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

LM