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

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?

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
PHP Programmer
interactivetools.com

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?

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

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
PHP Programmer
interactivetools.com
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

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
PHP Programmer
interactivetools.com

Ok! now it works!

By mbareara - January 7 - edited: January 7

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