Forum Moderators: coopster
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?
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') ";
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?
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. :-)
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);
-> 0mysql> 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";
}
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¦property_id¦address¦city¦state¦postcode¦bdrm¦rent¦duration (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 }
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
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.
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!"
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.
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¦bedrooms¦price¦postcode
1234¦3¦0¦NULL
1235¦5¦0¦NULL
1236¦3¦0¦NULL
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¦bedrooms¦price¦postcode
1234¦3¦750¦NULL
1235¦5¦0¦NULL
1236¦3¦500¦NULL
1237¦0¦400¦NULL
1238¦0¦450¦NULL
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¦bedrooms¦price¦postcode
1234¦3¦750¦m456
1235¦5¦0¦NULL
1236¦3¦500¦Chatem45123
1237¦1¦400¦NULL
1238¦2¦450¦NULL
1239¦0¦0¦m45
1240¦0¦0¦m45
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.
$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);
Only thing I would suggest is creating an "outer loop" or something to cut down on the code, but if it works . . . it works!
