leftJoin
2 posts by 2 authors in: Forums > CMS Builder
Last Post: November 27, 2014 (RSS)
By zaba - November 26, 2014
Hi, I am trying to do a preRecord nextRecord link thing.
I have 2 tables 1 called sector and the other case_studies. The common field is sector.num and case_studies.sec.
The next and previous record wants to go between sectors based on there being a record in case_studies belonging to that sector, other wise it does't go to that sector.
i.e. there may be sectors in the sectors table that have not got any case studies from the case studies table associated with them, hence I would rather the next previous links only jump to the ones that have records.
I pulled some info from another post but i found it confusing.
list($prevRecord, $nextRecord) = getPrevAndNextRecords(array(
'tableName' => 'sector',
'recordNum' => "$current_sec",
'leftJoin' => array('sector.num'), //not sure i have this right
'case_studies' => 'sec', //not sure i have this right
'debugSql' => 'true',
));
page code
<div class="pagination">
<?php if (@$prevRecord): ?>< <a href="/case-study-list.php?sec=<?php echo $prevRecord['num'] ?>">Previous</a><?php endif; ?> <?php if (@$nextRecord): ?><a href="/case-study-list.php?sec=<?php echo $nextRecord['num'] ?>" >Next</a> ><?php endif; ?>
</div><!-- /pagination -->
By Dave - November 27, 2014
Hi Zaba,
Unfortunately, getPrevAndNextRecords() doesn't support the leftJoin option.
I can't think of a really simple solution at the moment that doesn't require some custom programming.
Here's some quick hacks, maybe one of these will work?
- If there's not a lot of records (<100) you could just load them all and loop over them in PHP to determine what the next record should be
- You could just load case_studies, sort it by sector.num and determine the next sector that way (they wouldn't be in alphabetic order, though).
- You could just page through to sectors with no case studies, but with a link "No case studies, click here for the next section".
- Or you could do the above with a PHP redirect, so when a section page loads and finds it case no case studies it just redirects to the next section page. To the end-user this would happen so fast they probably wouldn't notice at all.
Hope that helps!
interactivetools.com