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

Thanks Daniel

changing intval to mysql_escape did the trick - fantastic!

One last question on this please?

My "where" statement now reads

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

This now produces all similar properties based on "property_type" and "town" but it also includes the currently displayed property.

Is there a line I can to the "where" statement that excludes the current listing that is being displayed.?

Thanks again for your great suport.

Andy