Search problem

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

By honolulu - July 31, 2012

Hi to all, i have a little problem with custom search form and result page. Here is it:


I have table for Real Estate properties called 'imoti':



Search index code head:


<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php

require_once "/some-path/lib/viewer_functions.php";

list($imotiRecords, $imotiMetaData) = getRecords(array(
'tableName' => 'imoti',
));


?>


Body form code:

<form name="search" method="GET" action="result-nov-search.php">
<select name="oblast_grad[]" multiple="multiple" size="5">
<option value="&#1058;&#1088;&#1086;&#1096;&#1077;&#1074;&#1086;">&#1058;&#1088;&#1086;&#1096;&#1077;&#1074;&#1086;</option>
<option value="&#1052;&#1083;&#1072;&#1076;&#1086;&#1089;&#1090;">&#1052;&#1083;&#1072;&#1076;&#1086;&#1089;&#1090;</option>
<option value="&#1042;&#1098;&#1079;&#1088;&#1072;&#1078;&#1076;&#1072;&#1085;&#1077;">&#1042;&#1098;&#1079;&#1088;&#1072;&#1078;&#1076;&#1072;&#1085;&#1077;</option>
<option value="&#1042;&#1083;&#1072;&#1076;&#1080;&#1089;&#1083;&#1072;&#1074; &#1042;&#1072;&#1088;&#1085;&#1077;&#1085;&#1095;&#1080;&#1082;">&#1042;&#1083;&#1072;&#1076;&#1080;&#1089;&#1083;&#1072;&#1074; &#1042;&#1072;&#1088;&#1085;&#1077;&#1085;&#1095;&#1080;&#1082;</option>
<option value="&#1050;&#1072;&#1081;&#1089;&#1080;&#1077;&#1074;&#1072; &#1075;&#1088;&#1072;&#1076;&#1080;&#1085;&#1072;">&#1050;&#1072;&#1081;&#1089;&#1080;&#1077;&#1074;&#1072; &#1075;&#1088;&#1072;&#1076;&#1080;&#1085;&#1072;</option>
</select>
<input type="submit" name="submit" value="Search" />

</form>


The result page - result-nov-search.php:

Head:


<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php

require_once "/some-path/lib/viewer_functions.php";

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

?>


List:

<h1>Real Estate - List Page Viewer</h1>
<?php foreach ($imotiRecords as $record): ?>
Record Number: <?php echo $record['num'] ?><br/>
Name: <?php echo $record['name'] ?><br/>
Price: <?php echo $record['price'] ?><br/>
State: <?php echo $record['oblast_grad'] ?><br/>
_link : <a href="<?php echo $record['_link'] ?>"><?php echo $record['_link'] ?></a><br/>

<hr/>
<?php endforeach ?>

<?php if (!$imotiRecords): ?>
No records were found!<br/><br/>
<?php endif ?>

The thing is when choose 1 options (&#1058;&#1088;&#1086;&#1096;&#1077;&#1074;&#1086;) from oblast_grad select, it shows all listing, not only form &#1058;&#1088;&#1086;&#1096;&#1077;&#1074;&#1086; for example. When you choose 2 options it is the same - all listings. Is my code wrong or ... ?

In URL - result-nov-search.php?oblast_grad[]=&#1058;&#1088;&#1086;&#1096;&#1077;&#1074;&#1086;

The result page have to show the listings form 1 option if the user select 1 or 2,3 options at the same time if he search for properties in 2,3 states.

Thanks for help in advanced :)

Cheers

Re: [honolulu] Search problem

By Jason - July 31, 2012

Hi,

The issue seems to be that CMS Builder doesn't automatically do a search if the incoming field is an array. In order to do this, you'll need to manually create your own WHERE clause.

What type of field is "oblast_grad". If it's a list, is it single value or multi value list?

Let me know and I'll put together an example for you.

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: [honolulu] Search problem

By Jason - August 1, 2012

Hi,

You can create a WHERE clause like this:

EXAMPLE:

$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");
}


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

Hope this helps get you started
---------------------------------------------------
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

By honolulu - August 3, 2012

Ok that works like a charm, but only for this multivalue field. How can i combine to search on some others multi value fields. I mean real estate have type like: house, flat etc. They have to be multi choice too in search form. Like i said there will be a lot of options for one property so if I know how to add some option to be searchable in where clause it will be great for advanced search to build it on my own.

Cheers

Re: [honolulu] Search problem

By Jason - August 3, 2012

Hi,

Basically you can replicate the code in the above post and modify it to search for other fields. The most important thing to remember about multi value fields in CMS Builder is that they are stored as a string of values, separated by tab characters (\t). That's why you see the tab characters appearing in the where clause.

Give this a try with another field, and let me know if you run into any other problems.

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

By honolulu - August 4, 2012

Hi,

i try this, but it does not work:

two fields(multi values)
1 - oblast_grad
2 - vid_na_imota

$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,
));


and search form:

<div style="margin-left:20px; margin-right:20px; position:relative;">
<form id="search" name="search" method="GET" action="results.php">
<div style="float:left; width:300px; margin-right:20px;">
<div>&#1042;&#1080;&#1076; &#1085;&#1072; &#1080;&#1084;&#1086;&#1090;&#1072;</div><div><select id="state" name="vid_na_imota[]" size="5" multiple="true">
<option value="1-&#1089;&#1090;&#1072;&#1077;&#1085;" selected="selected">1-&#1089;&#1090;&#1072;&#1077;&#1085;</option>
<option value="2-&#1089;&#1090;&#1072;&#1077;&#1085;" selected="selected">2-&#1089;&#1090;&#1072;&#1077;&#1085;</option>
<option value="3-&#1089;&#1090;&#1072;&#1077;&#1085;">3-&#1089;&#1090;&#1072;&#1077;&#1085;</option>
<option value="4-&#1089;&#1090;&#1072;&#1077;&#1085;">4-&#1089;&#1090;&#1072;&#1077;&#1085;</option>
<option value="&#1052;&#1085;&#1086;&#1075;&#1086;&#1089;&#1090;&#1072;&#1077;&#1085;" selected="selected">&#1052;&#1085;&#1086;&#1075;&#1086;&#1089;&#1090;&#1072;&#1077;&#1085;</option>
</select></div></div>
<div style="float:left; width:300px; margin-right:20px;">
<div>&#1050;&#1074;&#1072;&#1088;&#1090;&#1072;&#1083;/&#1056;&#1072;&#1081;&#1086;&#1085;</div>
<div><select id="city" name="oblast_grad[]" multiple="multiple" size="5">
<optgroup label="&#1058;&#1088;&#1086;&#1096;&#1077;&#1074;&#1086;, &#1052;&#1083;&#1072;&#1076;&#1086;&#1089;&#1090;, &#1042;&#1098;&#1079;&#1088;&#1072;&#1078;&#1076;&#1072;&#1085;&#1077;">
<option value="&#1058;&#1088;&#1086;&#1096;&#1077;&#1074;&#1086;">&#1058;&#1088;&#1086;&#1096;&#1077;&#1074;&#1086;</option>
<option value="&#1052;&#1083;&#1072;&#1076;&#1086;&#1089;&#1090;">&#1052;&#1083;&#1072;&#1076;&#1086;&#1089;&#1090;</option>
<option value="&#1042;&#1098;&#1079;&#1088;&#1072;&#1078;&#1076;&#1072;&#1085;&#1077;">&#1042;&#1098;&#1079;&#1088;&#1072;&#1078;&#1076;&#1072;&#1085;&#1077;</option>
</optgroup>
<optgroup label="&#1042;&#1083;&#1072;&#1076;&#1080;&#1089;&#1083;&#1072;&#1074; &#1042;&#1072;&#1088;&#1085;&#1077;&#1085;&#1095;&#1080;&#1082;, &#1050;&#1072;&#1081;&#1089;&#1080;&#1077;&#1074;&#1072; &#1075;&#1088;&#1072;&#1076;&#1080;&#1085;&#1072;">
<option value="option4">&#1042;&#1083;&#1072;&#1076;&#1080;&#1089;&#1083;&#1072;&#1074; &#1042;&#1072;&#1088;&#1085;&#1077;&#1085;&#1095;&#1080;&#1082;</option>
<option value="option5">&#1050;&#1072;&#1081;&#1089;&#1080;&#1077;&#1074;&#1072; &#1075;&#1088;&#1072;&#1076;&#1080;&#1085;&#1072;</option>
</optgroup>
</select>
</div>
</div>
<div style="float:left; width:300px; margin-right:20px;"><input type="submit" name="submit" value="Search" /></div>
</form></div>


when chooce 1 option from vid_na_imota and 1 option from oblast_grad it does not do the search correct. Maybe my code is wrong :(

Re: [honolulu] Search problem

By honolulu - August 6, 2012

Someone?

Re: [honolulu] Search problem

By Jason - August 6, 2012

Hi,

What you'll need to do is group your search elements with parentheses, putting an AND in between.

Try this:
NOTE: This example code has not been tested

$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,
));

Hope that helps get you started
---------------------------------------------------
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

By honolulu - August 6, 2012

Hi Jason,

i try your code, but i doesn`t work it show some:

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-стаен %' ) LIMIT 2' at line 3.

This search thing is very important for the site, because it is based ot this search to find property that match their criteria for example from 2 different areas by 2 different types of homes in one town.

Cheers