Combining Queries

[manc1]

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') ";
}[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!

 

[r937]

you're working with an EAV (entity-attribute-value) design, which is deceptively simple to store data into but depressingly difficult to get information out of

search on that phrase for more information

your problem is that you should be looking for the existence of multiple sets of values on different rows

however, your query is written to look for these different values on the same row, since the conditions of the WHERE clause are always evaluated on each row separately

if you combine this --

cms_module_products_fieldvals.fielddef_id IN (8,9)

with this --

cms_module_products_fieldvals.fielddef_id IN (12)

then no single row can ever satisfy both conditions at the same time

make sense?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[manc1]

yes i understand this. do you know the solution to this? what do i need to do to combine the 3 queries with one another?

 

[r937]

i don't understand how your two tables are joined

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[manc1]

cms_module_products - i only need the product ID to output from this table, but may need to use the price column if entered.

then i need from cms_module_products_fieldvals:

fielddef_id is the identifier
value is the value within the fielddef_id

fielddef_id 21 has to be false

fielddef_id 8 or 9 has to have the value contain LIKE whatever postcode is entered (if a value is entered)

fielddef_id 12 has to be more than or equal to the value entered (if entered)

price from the table cms_module_products must be more than or equal to the value entered (if entered)


once the query is done, all i should then have is a list of product IDs from the cms_module_products table

that all make sense?

 

[r937]

i still don't understand how your two tables are joined

is there a column in cms_module_products_fieldvals that links to the cms_module_products product ID, which i am assuming is the primary key?

what are those columns called?

and if all you want is a list of product IDs, may i ask what you're going to do with them? run another query?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[manc1]

yes. the 3 columns in cms_module_products_fieldvals are:

product_id
fielddef_id
value

from this, product_id connects with the table cms_module_products with the column "id" - this is the primary key column

i don't just want a list of product IDs. what i actually want to do is output more info using the id as the primary key. there is no issue with this code below. for your convenience, here is the code after the query finishes:

$counting = mysql_query($query);
$count = mysql_num_rows($counting);
echo "We have found <b>".$count."</b> properties that match your search!<br />";

$fieldresult= $db->Execute($query);
while ($fieldprop= $fieldresult->FetchRow())
{
$smarty_data = "{Products action='details' detailtemplate='result' productid='".$fieldprop['product_id']."'}";
$smarty->_compile_source('temporary template', $smarty_data, $_compiled );
@ob_start();
$smarty->_eval('?>' . $_compiled);
$_contents = @ob_get_contents();
@ob_end_clean();
echo $_contents;
}

 

[r937]

sorry, i don't do python or perl or whatever language that happens to be

try this --

SELECT foo
     , bar -- columns you want for each product
  FROM cms_module_products 
 WHERE id IN
       ( SELECT p.id
           FROM cms_module_products AS p
         INNER
           JOIN cms_module_products_fieldvals AS pf
             ON pf.product_id = p.id
          WHERE p.price >= $price  -- independent condition on product
            AND (
                pf.fielddef_id = 21 AND pf.value != 'true'          -- EAV 1
             OR pf.fielddef_id IN (8,9) AND pf.value LIKE '%$post%' -- EAV 2
             OR pf.fielddef_id = 12 AND pf.value >= $bedrooms'      -- EAV 3
                )
         GROUP
             BY p.id
         HAVING COUNT(*) >= 3 -- satisfies all three EAV conditions
       )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[manc1]

unfortunately didn't work. check out the form itself:

http://www.northernflats.co.uk/flats-to-let/search

the next page also outputs $query so you can see what is going on...

 

[r937]

uh, what's the difference between id and product_id? in the same table?

oh, and i don't see any MySQL error, just a bunch of php errors ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[r937]

SELECT [red]DISTINCT product_id[/red] FROM cms_module_products WHERE id IN ( SELECT p.id FROM cms_module_products AS p INNER JOIN cms_module_products_fieldvals AS pf ON pf.product_id = p.id WHERE [red]p.price >= [unspecified][/red] AND ( pf.fielddef_id = 21 AND pf.value != 'true' OR pf.fielddef_id IN (8,9) AND pf.value LIKE '%[unspecified]%' OR pf.fielddef_id = 12 AND pf.value >= [unspecified]' ) GROUP BY p.id HAVING COUNT(*) >= 3 )

[red]????[/red]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[manc1]

product_id is in the cms_module_products_fieldvals table
id is in the cms_module_products table

[unspecified] is what is sent if nothing is entered into any of the parameters. it may be best to test using the letter M in the postcode field, and just selecting the lowest values for the other two fields

 

[r937]

product_id is in the cms_module_products_fieldvals table

in that case, this is invalid --


SELECT DISTINCT product_id FROM cms_module_products ...

[unspecified] is what is sent if nothing is entered into any of the parameters

surely you must see that this is invalid syntax --


p.price >= [unspecified]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[manc1]

there is nothing i can do about the [unspecified] being output if nothing is there. however, i have added if statements to change the values to 1 if nothing is entered. however, i still get errors.

to ber honest, it doesn't matter if the query gets product_id from cms_module_products_fieldvals or id from cms_module_products - they are the same result.

adding in DISTINCT means I won't get the same ID popping up more than once

 

[manc1]

is there a graphical program where i can drag and drop columns, valuies and tables so that it automatically generates a query for me?

 

[r937]

sure, there are several, but i don't use any of them (instead, i write sql by hand in HeidiSQL)

try looking on the mysql.com site for their query browser, or google for navicat, sqlyog, toad for mysql...

none of these, however, is going to get you over the hump of searching for multiple conditions on separate rows...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[manc1]

is there an easier way to select a column by a vlaue without making a query for it i.e.

instead of:
fielddef_id=12 AND value >= '$bedrooms'

and just using something like:
fielddef_id[12] WHERE value >= '$bedrooms'

this has become the reason I haven't been able to sleep as much! i can also knuckle this down to only use one table (cms_module_products_fieldvals) and two columns (fielddef_id and value), but i'd still want to base a query on multiple parameters based on certain fielddef_id values

 

[r937]

you're still going to have to face the fact that what you want is spread over multiple rows

this requires complex SQL involving multiple self-joins, or else counting conditions using GROUP BY

like i said, EAV is deceptively simple to store data into but depressingly difficult to get information out of

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon