Search problem

16 posts by 2 authors in: Forums > CMS Builder
Last Post: August 27, 2012 (RSS)

Archived
Reply
Reply

By honolulu - August 7, 2012

Hi,

is it possible in CMS builder to search in 2 or 3 multi value fields in the same time for example If i want to find 1 and 2 bedroom flat in Acton and Chelsie(London) and i give 120000 punds max price. This is my case, and should i use some real estate script which can do that?

Cheers

Archived
Reply
Reply

By Jason - August 7, 2012

Hi,

Yes, there is no limit to the number of fields you can search at once. All you need to do is put together the right MySQL WHERE clause. If you could add the option:

'debugSql' => true,

to your getRecords() call when you get the error, it will output the entire query it's trying to execute. If you can post that output, we can see what's going wrong.

Thanks

---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Search problem

Archived
Reply
Reply

By honolulu - August 7, 2012

Hi,

with this code:

$where = "";   
     
  if (is_array(@$_REQUEST['oblast_grad'])) {   
     
    $where .= "("; 
    foreach ($_REQUEST['oblast_grad'] as $oblast_grad) {   
      $where .= " oblast_grad LIKE '%\t".mysql_escape($oblast_grad)."\t%' OR";   
    }   
       
    $where = rtrim($where, "OR") . ")";   
  }  
    
  if (is_array(@$_REQUEST['vid_na_imota'])) {   
     
    if ($where) { $where .= " AND "; } 
     
    $where .= "("; 
     
    foreach ($_REQUEST['vid_na_imota'] as $vid_na_imota) {   
      $where .= " vid_na_imota LIKE '%\t".mysql_escape($vid_na_imota)."\t%' OR";   
    }   
       
    $where = rtrim($where, "OR") .")"; 
     
  }  
   
 list($imotiRecords, $imotiMetaData) = getRecords(array(    
    'tableName'  =>  'imoti',    
    'orderBy'    =>   mysql_escape(@$_REQUEST['orderBy']),    
    'perPage'    =>  '2',   
    'where'      =>  $where,   
  )); $where = "";   
     
  if (is_array(@$_REQUEST['oblast_grad'])) {   
       
    foreach ($_REQUEST['oblast_grad'] as $oblast_grad) {   
      $where .= " oblast_grad LIKE '%\t".mysql_escape($oblast_grad)."\t%' OR";   
    }   
       
    $where = rtrim($where, "OR");   
  }  
    
  if (is_array(@$_REQUEST['vid_na_imota'])) {   
       
    foreach ($_REQUEST['vid_na_imota'] as $vid_na_imota) {   
      $where .= " vid_na_imota LIKE '%\t".mysql_escape($vid_na_imota)."\t%' OR";   
    }   
       
    $where = rtrim($where, "OR");   
  }  
   
   
 list($imotiRecords, $imotiMetaData) = getRecords(array(    
    'tableName'  =>  'imoti',    
    'orderBy'    =>   mysql_escape(@$_REQUEST['orderBy']),    
    'perPage'    =>  '2',   
    'where'      =>  $where, 
'debugSql' => true,  
  ));

the output:

SELECT SQL_CALC_FOUND_ROWS `imoti`.* FROM `cms_imoti` as `imoti` WHERE ( oblast_grad LIKE '% Troshevo %' OR oblast_grad LIKE '% Mladost %' vid_na_imota LIKE '% 1-room %' OR vid_na_imota LIKE '% 2-rooms %' ) LIMIT 2
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vid_na_imota LIKE '% 1-room %' OR vid_na_imota LIKE '% 2-rooms %' ) LIMIT 2' at line 3

Cheers

Archived
Reply
Reply

By Jason - August 8, 2012

Hi,

The issue here is that you're going through the process twice.

Try this:

$where = "";

if (is_array(@$_REQUEST['oblast_grad'])) {

$where .= "(";
foreach ($_REQUEST['oblast_grad'] as $oblast_grad) {
$where .= " oblast_grad LIKE '%\t".mysql_escape($oblast_grad)."\t%' OR";
}

$where = rtrim($where, "OR") . ")";
}

if (is_array(@$_REQUEST['vid_na_imota'])) {

if ($where) { $where .= " AND "; }

$where .= "(";

foreach ($_REQUEST['vid_na_imota'] as $vid_na_imota) {
$where .= " vid_na_imota LIKE '%\t".mysql_escape($vid_na_imota)."\t%' OR";
}

$where = rtrim($where, "OR") .")";

}

list($imotiRecords, $imotiMetaData) = getRecords(array(
  'tableName' => 'imoti',
  'orderBy'   => mysql_escape(@$_REQUEST['orderBy']),
  'perPage'   => '2',
  'where'     => $where,

));

Hope this helps

Re: [Jason] Search problem

Archived
Reply
Reply

By honolulu - August 25, 2012

Hi,

this work pretty fine, thanks. I have another question, how can i echo the search query(s) on results.php page i saw on multisearch some like

<?php echo htmlspecialchars(@$FORM['q[]']); ?>

but this not work for me. How can i do that.

Cheers

Archived
Reply
Reply

By Jason - August 27, 2012

Hi,

What you can do is output your search values as a comma separated list.

For EXAMPLE:

<?php
$searchValues = array();

if (is_array(@$_REQUEST['vid_na_imota'])) {
  $searchValues = array_merge($searchValues, $_REQUEST['vid_na_imota']);
}

if (is_array(@$_REQUEST['oblast_grad'])) {
  $searchValues = array_merge($searchValues, $_REQUEST['oblas_grad']);
}

?>

Search Values: <?php echo join(",", array_unique(array_filter($searchValues)));?>

Hope this helps