Combination WHERE statement troubles

5 posts by 2 authors in: Forums > CMS Builder
Last Post: December 16, 2012   (RSS)

By sublmnl - December 12, 2012

Hi guys, looking for help with a sticky one.
Haven't been here in awhile but I know you can help.

I have a locations page listing where I've divided the listings up by the state selection in the record.
I also only want to show the individual locations that have a (finance) link.

My code looks like this:

list($southcarolinalocationsRecords, $locationsMetaData) = getRecords(array(
'tableName' => 'locations',
'allowSearch' => false,
'debugSql' => true,
'where' => "state = 'south carolina' AND financing_application_link = '1'",
));
list($northcarolinalocationsRecords, $locationsMetaData) = getRecords(array(
'tableName' => 'locations',
'allowSearch' => false,
'where' => "state = 'north carolina'",
));

And the HTML here:

<h3>South Carolina Locations:</h3>
<?php foreach ($southcarolinalocationsRecords as $record): ?><div class="location"><h4><?php echo $record['location_name'] ?></a></h4>
<p><?php echo $record['address'] ?><br />
<?php echo $record['city_st_zip'] ?><br/>
<?php echo $record['contact_info'] ?></p>
<p><a href="<?php echo $record['financing_application_link'] ?>" target="_blank" title="<?php echo $record['location_name'] ?>">Apply Online</a></p>
</div><?php endforeach ?>

<hr/>
<h3>North Carolina Locations:</h3>
<?php foreach ($northcarolinalocationsRecords as $record): ?><div class="location"><h4><?php echo $record['location_name'] ?></a></h4>
<p><?php echo $record['address'] ?><br />
<?php echo $record['city_st_zip'] ?><br/>
<?php echo $record['contact_info'] ?></p>
<p><a href="<?php echo $record['financing_application_link'] ?>" target="_blank" title="<?php echo $record['location_name'] ?>">Apply Online</a></p>
</div><?php endforeach ?>
<hr/>

Problem I am having is the South Carolina locations do not show at all.
Of course the North Carolina locations show, regardless of if they have the link or not. I haven't added this yet: AND financing_application_link = '1'",
Once I do, both sections go blank.
At least it divides up the locations listings!
Right now, the South Carolina listings are blank.

The debug message reads:

SELECT SQL_CALC_FOUND_ROWS `locations`.*
FROM `cms_locations` as `locations`
WHERE (state = 'south carolina' AND financing_application_link = '1')
ORDER BY dragSortOrder DESC

Help please.
:- /

By sublmnl - December 14, 2012

Thank you for your reply Greg.
I think you nailed it by asking me if the finance link was a checkbox.
(I actually tried the capitalization of the state names in the where statement already)

The financing_application_link is actually a text field.
And I only want to show the entries that have something in the text field.

I'm pretty sure I am borrowing code from another site I did where I only wanted to show something based on a checkbox selection.... so yeah makes sense.
But can you help show me how to do this when something is in the text field or not (not using the checkbox method)
?

Thank you

By gregThomas - December 14, 2012

This should be fairly easy to do. You can use the MySQL char_length function to retrieve any entries with a length greater that 0

list($southcarolinalocationsRecords, $locationsMetaData) = getRecords(array(
'tableName' => 'locations',
'allowSearch' => false,
'debugSql' => true,
'where' => "state = 'south carolina' AND CHAR_LENGTH(financing_application_link) > 0",
));
list($northcarolinalocationsRecords, $locationsMetaData) = getRecords(array(
'tableName' => 'locations',
'allowSearch' => false,
'where' => "state = 'north carolina'",
));

So the getRecords function for South Carolina will only be retrieved if financing_application_link has more than character in its field.

Thanks!

Greg
Greg Thomas







PHP Programmer - interactivetools.com

By sublmnl - December 16, 2012

That works.
Thank you
I also wrapped each list grouping on the page with this <?php if ($southcarolinalocationsRecords): ?>
........ . .. . ..... . .. .
<?php endif ?>
because I had a Header to show for each state grouping.
WIthout it, the header would show even if I had no locations with finance links.

So with your help and some previous code I had for another site, this one is done!
Thanks Greg.