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 daniel - January 3, 2020

Hi mbareara,

The general form for how you would do this would look something like this:

<?php 
  list($restaurantsRecords, $restaurantsMetaData) = getRecords(array( 
    'tableName' => 'restaurants',  
    'limit' => '4', 
    'where' => mysql_escapef('city = ?', $restaurantDetailRecord['city']),
  ));
?>

You'll need to make sure the variable and field names match up to your site and code.

Let me know if that helps, or if you have any questions!

Thanks,

Daniel
Technical Lead
interactivetools.com

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): ?>