Welcome to WebmasterWorld Guest from 35.175.120.174

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Complex SQL query!

Combining Multiple SQL queries

     
4:11 pm on Nov 12, 2009 (gmt 0)

New User

10+ Year Member

joined:Nov 12, 2009
posts: 6
votes: 0


I am trying to create a search form that uses multiple parameters in multiple columns with multiple values. here's what i have:

a table, called FIELDVALS.
two columns I will be searching in, called "fielddef_id" and "value"

I have built one form with 3 parameters. Each parameter is optional. I therefore have 3 queries for the 3 parameters:

1) fielddef_id IN (8,9) AND value LIKE '%$post%'
2) fielddef_id IN (12) AND value >= '$bedrooms'
3) fielddef_id IN (24) AND value >= '$price'

i can pull off one query at a time, but combining them brings 0 results. I can't seem to find the solution here. My full query is as follows:

$query ="SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (8,9) AND value LIKE '%$post%') ";

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

if ($price!= '0') {
$query .="AND (fielddef_id IN (12) AND value >= '$price')";
}

i know here the problem is combining the multiple queries with AND.

is there any solution to this?

5:23 pm on Nov 12, 2009 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member themadscientist is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 14, 2008
posts:2910
votes: 62


Have you tried OR to put the 3 statements together?

if ($bedrooms!= '0') {
$query .="OR (fielddef_id IN (12) AND value >= '$bedrooms') ";
}

if ($price!= '0') {
$query .="OR (fielddef_id IN (24) AND value >= '$price')";
}

Maybe I'm not understanding the situation, but with 3 different SELECTS you are not looking to match a distinct product_id where all the criteria are met... If these are your 3 selects, then you should probably use OR:

$query ="SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (8,9) AND value LIKE '%$post%') ";
$query ="SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (12) AND value >= '$bedrooms') ";
$query ="SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (24) AND value >= '$price') ";

6:55 pm on Nov 12, 2009 (gmt 0)

New User

10+ Year Member

joined:Nov 12, 2009
posts: 6
votes: 0


putting OR in doesnt output what i want.
i inserted DISTINCT because sometimes the product_id would be output more than once.
the 3 queries at the end of your post are all correct, but i want it to work like this:

do QUERY1 either way
if something is entered in parameter 2 then ADD QUERY2
if something is entered in parameter 3 then ADD QUERY3

this make sense?

8:50 pm on Nov 12, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Welcome aboard manc! Did you echo your completed queries to the screen to debug? Look at your overall queries. Potentially, this could produce

SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (8,9) AND value LIKE '%$post%') AND (fielddef_id IN (12) AND value >= '$bedrooms') AND (fielddef_id IN (12) AND value >= '$price')

See how you have AND fieldef_id in 8,9, AND field_def in 12? How can it be in 8,9, and also in 12?

It can't. :-)

Try something like this. Note that brackets() are generally required only when OR is involved.

$where=$query=$in_clause=$post='';
if (isset($_POST['term'])) { $post = cleanse_this_variable($_POST['term']); }


// These have what may seem like some unnecessary if's, but
// as you program grows, you will see their value
if ($post != '') { $in_clause .= '8,9'; } // queries are strings, no matter what
// Note that > 0 is a better choice here,
// blanks and strings are != 0, and this will
// generally automatically "filter" for numeric data.
if (($bedrooms > 0) or ($price > 0)) {
if ($in_clause != '') { $in_clause .= ','; }
$in_clause .= '12';
}
// $where is '' at this point, no need for an and
if ($in_clause != '') { $where .= " fielddef_id in ($in_clause)"; }
if ($post != '') {
if ($where != '') { $where .= " and"; }
$where .= " value LIKE '%$post%'";
}
if ($bedrooms > 0) {
if ($where != '') { $where .= " and"; }
$where .= " value >= '$bedrooms'";
}
if ($price > 0) {
if ($where != '') { $where .= " and"; }
$where .= " value >= '$price'";
}
$query ="select distinct product_id from fieldvals";
if ($where != '') { $query .= " where $where"; }
header("content-type:text/html");
echo $query;
exit;

Or use print_r() . . . whatever. :-)

9:54 am on Nov 13, 2009 (gmt 0)

New User

10+ Year Member

joined:Nov 12, 2009
posts:6
votes: 0


this will work except for one thing - say for example someone types in "M45" as $post, then will it not look at the "45" in columns 12 and 24 when it should only be looking in column 8 and 9?
11:01 am on Nov 13, 2009 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member themadscientist is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 14, 2008
posts:2910
votes: 62


I'm all confused now...

SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (8,9) AND value LIKE '%$post%')

The preceding says to 'select the distinct product_id(s) from the table where the fielddef_id is in the grouping 8 or 9 and the col value is like $post', which means for the select to be true, the fielddef_id must be 8 or 9...

mysql> SELECT 2 IN (0,3,5,7);
-> 0

mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
-> 1

[dev.mysql.com...]

It's not looking for the value IN the columns it's looking for the value of fielddef_id in the grouping (), unless there's something I'm missing...

Personally, I would scrap the whole 'build a complex query theory' and run them a UNIONs


if($condition1===TRUE) {
$query[] ="(SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (8,9) AND value LIKE '%$post%'))";
}

if($condition2===TRUE) {
$query[] ="(SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (12) AND value >= '$bedrooms'))";
}

if($condition3===TRUE) {
$query[] ="(SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (24) AND value >= '$price'))";
}

if(is_array($query) and count($query>1)) {
$select=implode(' UNION ',$query);
}
elseif(is_array($query) && !empty($query)) {
$select=$query[0];
}
else {
$error = "Sorry, Something Went Wrong and No Conditions Were Met to Select Results From";
}

7:41 pm on Nov 13, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


<light bulb> O.I.C. . . . I think the problem here stems from a problem with underlying structure and usability. Let me start at the beginning to show you what I mean.

Your users visit the form.

Road Name or Postcode (text)
Bedrooms (numeric select)
Minimum Rent (pcm) (numeric select)

Each selection you make should further restrict the results. So if only postcode is entered, all properties in that postcode display. If they select 3 bedrooms, that zip code and bedrooms >= the selection.

These are "and" conditions.

The problem, then, comes in when you select from those field_def columns (which I don't fully understand how or why they are set up.) I'm guessing there is some relationship between the "type" of field it is or something.

This is what I mean by an underlying structure; why is it this way? Why can't you just do

idroperty_idddressitytateostcodedrmenturation (as in, per month)

then the above scenario dispenses with "in".

if (post) { add postcode to where }
if (bedrooms) { add bedroom to where }
if (price) { add price to where }

select * from table
if (where) { add where }

10:06 pm on Nov 13, 2009 (gmt 0)

New User

10+ Year Member

joined:Nov 12, 2009
posts: 6
votes: 0


i wish it was that simple, but i'm using a module within a CMS system that is too complicated for me to just do my own data. therefore i have to deal with the database setup that the module gives.

your thoughts on how it works is exactly right. there are 3 columns in the table that i need to work with:

fielddef_id = the ID number of the custom value I enter. because its a general product module then i have to add my own custom field definitions. here, ID 8 is the road name, ID 9 is the zip code, ID 12 is the number of bedrooms and ID 22 is the rental price.

product_id = the product to which the field definition entry is connected to. this ID connects to another table (which i dont need to use with this form)

value = the value within the field definition.

so, an example will be this:

fielddef_id = 8
product_id = 2
value = random road

another example:

fielddef_id = 22
product_id = 1
value = 650

its just very annoying that the module creator hasn't built this in such a way that we can easily create a search form.
pro

12:06 am on Nov 14, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


here, ID 8 is the road name, ID 9 is the zip code, ID 12 is the number of bedrooms and ID 22 is the rental price.

Why are you querying road name for post code?

then will it not look at the "45" in columns 12 and 24 when it should only be looking in column 8 and 9?

What is 24?

How about this, then? :-)

$where=$query=$post='';
$bedrooms=$price=0;
if ((isset($_POST['term']) and ($_POST['term'] != '')) { $post = cleanse_this_variable($_POST['term']); }
if ($_POST['bdrms'] > 0) { $bedrooms = cleanse_this_variable($_POST['bdrms']); }
if ($_POST['rent'] > 0) { $price = cleanse_this_variable($_POST['rent']); }


if ($post != '') {
// Won't be an and on the first one
$where .= " fielddef_id=9 and value LIKE '%$post%'";
// If road name has relevance, do
// $where .= " ((fielddef_id=9 and value LIKE '%$post%')";
// $where .= " or (fielddef_id=8 and value LIKE '%$post%'))";
}
if ($bedrooms > 0) {
if ($where != '') { $where .= " and"; }
$where .= " fielddef_id=12 and value >= '$bedrooms'";
}
if ($price > 0) {
if ($where != '') { $where .= " and"; }
$where .= " fielddef_id=22 and value >= '$price'";
}
$query ="select distinct product_id from fieldvals";
if ($where != '') { $query .= " where $where"; }
header("content-type:text/html");
echo $query;
exit;

The difference being, you need to lock the fielddef_id to the value you want to query.

11:26 am on Nov 15, 2009 (gmt 0)

New User

10+ Year Member

joined:Nov 12, 2009
posts: 6
votes: 0


re: id 24 - i got it wrong, it is id 22

re: the query - i have made something similar to this before. I have put it live on the URL I gave you so you can see what happens. for example, if you type in "m" for postcode, and then 2+ for bedrooms, 2 products SHOULD come up but 0 are found. This is because it is looking for the letter M in the bedroom column. is there no way of saying to the query "hey, only look in the columns I tell you to dammit!"

8:38 pm on Nov 15, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


<smacks head> OMG . . . sometimes I'm an idiot (voters, hold your votes . . . )

Not only does my last post contradict the earlier post, it's also going to fail. Any here is why.

What you are asking for is a result set that returns a single row based on the parameters. That is, look at my experiment below.

create table fieldvals values (fielddef_id int (11),prod_id int (11),value varchar (255));

create table properties (prod_id int(11), title varchar (255));

insert into properties values (1234,'Three Bedroom Townhouse');
insert into properties values (1235,'One Room Studio Flat');

insert into fieldvals values (8,1234,'M45 155');
insert into fieldvals values (9,1234,'12345');
insert into fieldvals values (8,1235,'Abbey Road');
insert into fieldvals values (9,1235,'98765');
insert into fieldvals values (12,1234,'3');
insert into fieldvals values (12,1235,'1');
insert into fieldvals values (22,1234,'500');
insert into fieldvals values (22,1235,'750');

So let's try just two parameters, with the following query:

select distinct(properties.prod_id),properties.title, fieldvals.value from properties left join fieldvals on properties.prod_id=fieldvals.prod_id where fieldvals.value > 1 and fieldvals.value <= 750;

This will return two records for "Three Bedroom Townhouse" and one for "One Room Studio Flat" because - and ONLY because - a single row was found for each > 1 and <= 750. But neither of those columns is the correct one, > 1 should be bedrooms, <= 750 should be price. You get an empty set if you try to "lock" it to the right column:

select distinct(properties.prod_id),properties.title, fieldvals.value from properties left join fieldvals on properties.prod_id=fieldvals.prod_id where fielddef_id=12 and fieldvals.value > 1 and fielddef_id = 22 and fieldvals.value <= 750;

This will return an empty set.

The bolded demonstrates why a simple query won't work. As I said earlier (<facepalm>) how can you have the field def be 12 and 22 at the same time?

You can't. Using an in clause doesn't work either, because the field def in (8,9,12,22) is still matching on multiple rows, and still doesn't lock the correct ID to the input value.

The solution, or one of them, lies in temporary tables.

create temporary table if not exists tmp_options (product_id int(11), bedrooms tinyint(3), price decimal (12,2) default '0.00', postcode varchar(30));

Use the previous examples to apply the following to all search parameters. Using bedrooms as an example.

if ($bedrooms > 0) {
select product_id from fieldvals where fielddef_id=12 and value >= '$bedrooms';
// if found . . . first CHECK to see if the product id
// is present.
select count(*) from tmp_options where prod_id=$option_prod_id;
// if it is, UPDATE so you have one row.
if ($count > 0) {
update tmp_options set bedrooms=$bedroom_value_found where prod_id=$option_prod_id;
}
else {
insert into tmp_options (prod_id,bedrooms) values ($option_prod_id,$bedroom_value_found);
}

Repeat this process for all input parameters, you can even put it in a loop.

Now, do your select on the temporary table based on input parameters, using the "if" construct mentioned earlier, addressing all parameters, and adding the order by and limit:

select * from tmp_options where bedrooms>=$bedrooms and price<=$price and postcode regexp '.*$post.*' order by $order_val limit $start, $end;

Notes: 1) used regexp because like does not cover case insensitivity in my tests. .* means "zero or more of any character," the equivalent of like's '%'. 2) note we've converted the text "price" to a decimal field type, so it wil now display 750.00 instead of 750 without formatting it in programming.

After you've stored the results in a variable (or output them to the browser), don't forget to drop it, temp_tables die after time but it frees up memory:

drop table tmp_options;

You would probably want to modify the above when you get it working so you can include the property title, created and last modified date of the listing so you can display and order by those results, as well as display a thumbnail.

But this will work, and I'd completely forgotten about it . . . lol

You might post this in the database forum, as there may be a more graceful solution.

12:36 pm on Nov 17, 2009 (gmt 0)

New User

10+ Year Member

joined:Nov 12, 2009
posts: 6
votes: 0


i've come into a hurdle. there is nowhere that actually stores $option_prod_id and $PARAMETER_value_found. where would this happen?
7:36 pm on Nov 17, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


there is nowhere that actually stores $option_prod_id and $PARAMETER_value_found.

The product_id has to exist somewhere, otherwise what would the options (fieldvals) table join on?

From your original post:

$query ="SELECT DISTINCT product_id FROM fieldvals WHERE (fielddef_id IN (8,9) AND value LIKE '%$post%') ";

In the last example, $option_prod_id = product_id

First create your temporary table so it's ready. I left a little part out in my rushed post but thought you might be able to figure that out, sorry.

The idea is you get the product_id and target value from the options (fieldvals) table. You would loop through each submitted value, appending the results in the temp table.

$bedrooms = 3
$price = 750
$post = m45

first iteration - bedrooms (bolded is what I left out)
select product_id,value from fieldvals where fielddef_id=12 and value >= '$bedrooms';

When these are found and you insert them into the temp table, you might have

product_idedroomsriceostcode
1234񔬆ULL
1235񕓞ULL
1236񔬆ULL

second iteration - price
select product_id,value from fieldvals where fielddef_id=22 and value <= '$price';

Note previous post about existing records, this is important. You do an insert OR update based on whether the product id is already in the temporary table. In the below, note we have two matching records, and some others but they do NOT have a matching record for bedrooms.

product_idedroomsriceostcode
12343750ULL
1235񕓞ULL
12363500ULL
1237񓱆00ULL
1238񓱆50ULL

third iteration - post code
select product_id,value from fieldvals where fielddef_id=9 and value regexp '.*$post.*';

Again, this may find matching records without matching bedroom or price values, but our final query will look in all three.

product_idedroomsriceostcode
1234񔬍50m456
1235񕓞ULL
1236񔬋00Chatem45123
1237񔄲00ULL
1238񔘞50ULL
1239񓱂45
1240񓱂45

Now you take the original user's query and apply it to the temporary table.

select product_id from temp_options where bedrooms >= '$bedrooms' and price <= '$price' and postcode regexp '.*$post.*';

1234
1236

This gives you an array of product id's by which you go back to the original product table (= properties table) and select the appropriate properties. It would be more efficient to do a joined select so you can output directly from the temp table.

select temp_options.product_id, properties.title,properties.other_value, temp_table.bedrooms,temp_table.price,temp_table.postcode from temp_options,properties where properties.product_id=temp_options.product_id and temp_options.bedrooms >= '$bedrooms' and temp_options.price <= '$price' and temp_options.postcode regexp '.*$post.*' order by temp_options.price asc, properties.title asc;

displays product id, title, other selected fields, # bdrms, price, post code

I hard coded the order and limit above for an example, see previous post, these should be variables.

12:31 pm on Dec 5, 2009 (gmt 0)

New User

10+ Year Member

joined:Nov 12, 2009
posts: 6
votes: 0


i did it! i took me a while, but it really wasn't liking a lot of stuff i was doing. instead, i had to create a table (temporary table didn't work), and use 4 while comments to construct another table. i then used one query to do what i needed to do. FYI here is the code:

$temp='create table if not exists temp (product_id int(3), price int(3), beds int(2), road text, post text)';
$dotemp= $db->Execute($temp);

$query ="SELECT * FROM example_table WHERE fielddef_id=22";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){

$addprice = "INSERT INTO temp (product_id, price) VALUES ('".$row[product_id]."', '".$row[value]."')";
$doprice= $db->Execute($addprice);

}

$query ="SELECT * FROM example_table WHERE fielddef_id=12";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){

$addbeds = "UPDATE temp SET beds='".$row[value]."' WHERE product_id='".$row[product_id]."'";
$dobeds= $db->Execute($addbeds);

}

$query ="SELECT * FROM example_table WHERE fielddef_id=8";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){

$addroad = "UPDATE temp SET road='".$row[value]."' WHERE product_id='".$row[product_id]."'";
$doroad= $db->Execute($addroad);

}

$query ="SELECT * FROM example_table WHERE fielddef_id=9";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){

$addpost = "UPDATE temp SET post='".$row[value]."' WHERE product_id='".$row[product_id]."'";
$dopost= $db->Execute($addpost);

}

if(is_array($query) and count($query>1)) {
$select=implode(' AND ',$query);
}
elseif(is_array($query) && !empty($query)) {
$select=$query[0];
}
else {
$error = "Sorry, Something Went Wrong and No Conditions Were Met to Select Results From";
}

$query ="SELECT DISTINCT product_id FROM temp WHERE price >= $price AND beds >= $bedrooms AND (post LIKE '%$post%' OR road LIKE '%$post%')";
$result = mysql_query($query) or die(mysql_error());

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

8:33 pm on Dec 5, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Good job, you got it! :-)

Only thing I would suggest is creating an "outer loop" or something to cut down on the code, but if it works . . . it works!