Hi,
I am using a CMS system called CMS Made Simple and have made an SQL query based on a search form.
The search form has 3 possible inputs - postcode, number of bedrooms, and minimum rent.
I want to create a query based on the data entered in the 3 inputs above. If the user does not enter something in any/all of these fields, I want it to ignore those inputs and output everything based on just what is entered.
Here's the beginning of the UDT (User Defined Tag) I have created which lets me use PHP within the CMS System:
The last 3 lines take the parameters of the search input and rename them to be used in the following queries (all of which I will comment about):
This ensures that the items output are available
This adds a query based on the data input in the postcode field
This adds a query based on the minimum rent entered and outputs everything equal and more than the number entered.
Now, each query by itself works, however, when any data is entered as a parameter it outputs 0 results. I know each of the additional field queries work by themselves, but cannot get them to connect with each other.
I know I'm missing something here and as someone who isn't an SQL guru I'm finding that I can't get a good answer besides writing in a forum!
Hope someone can help!
I am using a CMS system called CMS Made Simple and have made an SQL query based on a search form.
The search form has 3 possible inputs - postcode, number of bedrooms, and minimum rent.
I want to create a query based on the data entered in the 3 inputs above. If the user does not enter something in any/all of these fields, I want it to ignore those inputs and output everything based on just what is entered.
Here's the beginning of the UDT (User Defined Tag) I have created which lets me use PHP within the CMS System:
Code:
global $gCms;
$smarty = &$gCms->GetSmarty();
$db = &$gCms->db;
function make_alias($string, $isForm=false)
{
$string = trim($string);
$string = preg_replace("/[_-\W]+/", "-", $string);
$string = trim($string, '_');
return strtolower($string);
}
$post=$params['pc'];
$bedrooms=$params['rooms'];
$price=$params['rent'];
The last 3 lines take the parameters of the search input and rename them to be used in the following queries (all of which I will comment about):
Code:
$query ="SELECT DISTINCT product_id FROM cms_module_products, cms_module_products_fieldvals WHERE (cms_module_products_fieldvals.fielddef_id=21 AND cms_module_products_fieldvals.value != 'true') ";
This ensures that the items output are available
Code:
if ($post!= '[unspecified]') {
$query .="AND (cms_module_products_fieldvals.fielddef_id IN (8,9) AND cms_module_products_fieldvals.value LIKE '%$post%') ";
}
This adds a query based on the data input in the postcode field
Code:
if ($bedrooms!= '[unspecified]') {
$query .="AND (cms_module_products_fieldvals.fielddef_id IN (12) AND cms_module_products_fieldvals.value >= '$bedrooms') ";
}[code]
This adds a query based on the minimum number of bedrooms entered and outputs everything equal and more than the number entered.
[code]if ($price!= '[unspecified]') {
$query .="AND cms_module_products.price >= '$price'";
}
This adds a query based on the minimum rent entered and outputs everything equal and more than the number entered.
Now, each query by itself works, however, when any data is entered as a parameter it outputs 0 results. I know each of the additional field queries work by themselves, but cannot get them to connect with each other.
I know I'm missing something here and as someone who isn't an SQL guru I'm finding that I can't get a good answer besides writing in a forum!
Hope someone can help!