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 gregThomas - December 13, 2012

Hi Sublmnl,

If your not getting an error back from MySQL, and North Carolina is returning correctly, then the most likely issue is that you don't have the same spelling or capitalization of South Carolina in the state field. You could try copying the South Carolina text from the state field and pasting it into your where statement to ensure it's the same. What field type is the financing_application_link, is it a checkbox field? Is it currently checked for North and South Carolina?

Thanks!

Greg
Greg Thomas







PHP Programmer - interactivetools.com

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.