Create a "Where" statement by selecting a field already present on the page.

4 posts by 2 authors in: Forums > CMS Builder
Last Post: March 23, 2019   (RSS)

By andybarn - March 22, 2019

Hi

I have a property website where I have a search form, search results page and search details page, all working fine.

What I would like to do on the details page is also list similar properties based on the field “property_type” and maybe others but just "property_type" for now

Currently my "Details Page" header is as follows:-

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
  // load viewer library
  $libraryPath = 'cmsb/lib/viewer_functions.php';
  $dirsToCheck = array('/home/aplacein/public_html/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

 // load record from 'listings'
  list($listingsRecords, $listingsMetaData) = getRecords(array(
    'tableName'   => 'listings',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $listingsRecord = @$listingsRecords[0]; // get first record
  if (!$listingsRecord) { dieWith404("Record not found!"); } // show error message if no record found

I presume to also list my listings as well I need to add more code

I have tried to add the following :-

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
  // load viewer library
  $libraryPath = 'cmsb/lib/viewer_functions.php';
  $dirsToCheck = array('/home/aplacein/public_html/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

 // load record from 'listings'
  list($listingsRecords, $listingsMetaData) = getRecords(array(
    'tableName'   => 'listings',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $listingsRecord = @$listingsRecords[0]; // get first record
  if (!$listingsRecord) { dieWith404("Record not found!"); } // show error message if no record found

  list($likeRecords, $likeMetaData) = getRecords(array(
    'tableName' => 'listings', 
    'where' => "property_type ='".intval($listingsRecord['property_type'])."'", 
    'limit'       => '3',
    'loadUploads' => true,
    'allowSearch' => true,
));
?>

However this does not work and I can't seem to display any records.

I know the problem is with my "where" statement

    'where' => "property_type ='".intval($listingsRecord['property_type'])."'", 

If I take the "where" statement out I get 3 listings displayed on the page but with different "property_type" (as one would expect)

Please could you tell me how to write a "where" statement that will select the "property_type" value in the details page.

Thanks
Andy

By daniel - March 22, 2019

Hey Andy, 

It's possible that the value in $listingsRecord['property_type'] isn't an integer, and so when you use intval() it doesn't return the correct value for matching. You can try changing intval to mysql_escape to see if that makes any difference. If that doesn't help, I would suggest adding:

'debugSql' => false,

 to the list of arguments in getRecords() and load the page. This will display the full MySQL query being used - if you copy that here I can check to see if there are any other obvious issues with it.

Thanks,

Daniel
Technical Lead
interactivetools.com

By andybarn - March 23, 2019

Hi Daniel

I think I have answered my own question after a bit of delving into the support forum.

I changed my "where" statement by adding one more "AND" statement to exclude current property. For you and those that may be looking for the same thing, my "where" statement is as follows:-

    'where' => "property_type ='".mysql_escape($listingsRecord['property_type'])."' AND town='".mysql_escape($listingsRecord['town'])."' AND num!='".mysql_escape($listingsRecord['num'])."'", 

Thanks again for pointing me in the right direction.

Kind regards

Andy