Related item on a detail page

11 posts by 2 authors in: Forums > CMS Builder
Last Post: January 7, 2020   (RSS)

By mbareara - January 3, 2020

I have a Restaurants table, with a field called city

In the bottom of the detail page of a Restaurant, i would have a list of 4 other restaurants.

I know taht i could have this code


      <?php 
list($restaurantsRecords, $restaurantsMetaData) = getRecords(array( 
'tableName' => 'restaurants',  
'limit' => '4', 
  ));
?> 
<?php foreach ($restaurantsRecords as $record): ?>
... etc etc

   But... what if i need only 4 restaurants WHERE city is the same of the restaurant in detail page?

By mbareara - January 3, 2020

Hi Dave, thank you for your brilliant solution. It works perfectly.

Every day i learn that nothing is impossibile with Cms Builder. i'm proud to use it!

but life is a constant challenge so i will ask to you another question. 

Of course i don't want in the other 4 item the same item of detail page. Is there a way to exclude it from list?

By daniel - January 6, 2020

Hi mbareara,

Yes, to exclude a specific post you could change the where option to something like this:

'where' => mysql_escapef('city = ? AND num != ?', $restaurantDetailRecord['city'], $restaurantDetailRecord['num']),

Let me know if that works for you, or if you have any other questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

By mbareara - January 6, 2020

Thanks Dave, but it doesn't work. The result of detail page is not excluded

By daniel - January 6, 2020

Hi mbareara,

Could you add the 'debugSql' option to your getRecords call and copy the output here? It will look something like this:

  list($restaurantsRecords, $restaurantsMetaData) = getRecords(array( 
    'tableName' => 'restaurants',  
    'limit' => '4', 
    'where' => mysql_escapef('city = ? AND num != ?', $restaurantDetailRecord['city'], $restaurantDetailRecord['num']),
    'debugSql' => true,
  ));

(Don't change any of the other options - just add the 'debugSql' line)

This will let me get a better look at what the function is doing internally and hopefully give some ideas for troubleshooting.

Thanks!

Daniel
Technical Lead
interactivetools.com

By mbareara - January 6, 2020

SELECT SQL_CALC_FOUND_ROWS `locali`.*
FROM `cmsb_locali` as `locali`
 WHERE (city = ‘Ragusa’ AND num != ″) 
 ORDER BY dragSortOrder DESC
 LIMIT 4
SELECT SQL_CALC_FOUND_ROWS `accounts`.*
FROM `cmsb_accounts` as `accounts`
 WHERE (`num` IN (1)) 
 ORDER BY fullname, username

By daniel - January 6, 2020

Hi mbareara,

I think I have spotted the issue; can you try changing this line:

'where' => mysql_escapef('city = ? AND num != ?', $localiRecord['city']),

To this:

'where' => mysql_escapef('city = ? AND num != ?', $localiRecord['city'], $localiRecord['num']),

Let me know if that sorts it out.

Thanks!

Daniel
Technical Lead
interactivetools.com

By mbareara - January 6, 2020

Ok! now it works!

By mbareara - January 7, 2020 - edited: January 7, 2020

Hi Daniel

I Have another question:

what if have more than one CITY in the field... and would filter result only for one of that?

<?php 
  list($localiRecords, $localiMetaData) = getRecords(array( 
    'tableName' => 'locali',  
    'limit' => '4', 
   'where' => mysql_escapef('city = ? AND num != ?', $localiRecord['city'], $localiRecord['num']),
  ));
?>
				<?php foreach ($localiRecords as $record): ?>