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.
:- /
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.
:- /
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
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
PHP Programmer - interactivetools.com
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
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
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
So the getRecords function for South Carolina will only be retrieved if financing_application_link has more than character in its field.
Thanks!
Greg
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
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.
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.