×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Combining Queries

Combining Queries

Combining Queries

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

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!

RE: Combining Queries

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

RE: Combining Queries

(OP)
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?

RE: Combining Queries

i don't understand how your two tables are joined

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

RE: Combining Queries

(OP)
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?

RE: Combining Queries

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

RE: Combining Queries

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

CODE

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

RE: Combining Queries

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

try this --

CODE

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

RE: Combining Queries

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 winky smile

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

RE: Combining Queries

SELECT DISTINCT product_id 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 >= [unspecified] 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 )

????

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

RE: Combining Queries

(OP)
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

RE: Combining Queries

Quote:

product_id is in the cms_module_products_fieldvals table
in that case, this is invalid --

  SELECT DISTINCT product_id FROM cms_module_products  ...

Quote:

[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

RE: Combining Queries

(OP)
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

RE: Combining Queries

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

RE: Combining Queries

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

RE: Combining Queries

(OP)
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

RE: Combining Queries

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close