SQL Query Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

SQL Query Question

Author

Topic

manc
Starting Member

2 Posts

Posted - 2009-11-04 : 05:04:54

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:

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):

$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

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

if ($bedrooms!= '[unspecified]') {
$query .="AND (cms_module_products_fieldvals.fielddef_id IN (12) AND cms_module_products_fieldvals.value >= '$bedrooms') ";
}

This adds a query based on the minimum number of bedrooms entered and outputs everything equal and more than the number entered.

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources