How to make a search with many options

[feri_soft]

How to make a search with many many many many options and if some of the are not set to asume that any is posssible.For exemple i have a real-estates website and in search i have many options if flour for example is not set we the script will consider that any flour is possible???

Help me fast please!

[Psycho]

Let's assume you have two fields; 'roomsr for number of rooms and 'pool' for wether the property has a pool or not. On the search page you could create a select list for rooms with the first entry label being "Any" and a value of "*". Each of the other entries will have the same label and values (1, 2, 3...). Then you could have a select list for Pool with the following labels/values:
Any/*
Yes/1
No/0

Then on the page that performs the actual search you would just create your query as follows:

[code]<?php
$query = "SELECT * FROM 'properties' WHERE 'rooms' = $POST_['rooms'] AND 'pool' = $POST_['pool']";
?>[/code]

This is a very simplistic example, but you should get the idea. Some criteria may require additional processing before creating your query. For example what if the number of rooms was to be a miniumum:

Then you could do something like this:[code]<?php
if ($POST_['rooms'] <> "*") {
    $roomQuery = " 'rooms' > " . $POST_['rooms'] . " AND";
} else {
    $roomQuery = ""
}

$query = "SELECT * FROM 'properties' WHERE" . $roomQuery . " 'pool' = $POST_['pool']";?>[/code]

[alpine]

One example, this depends that your postform input fields are named exactly the same as the columns you want to search in your table[code]

<?php

$query = "select * from table";

$count = 0;
foreach($_POST as $key => $value)
{
if($count == 0): $qq = " WHERE"; else: $qq = " AND"; endif;
if(!empty($value)): $query .= "$qq $key = '$value'"; endif;
$count++;
}
$query .= " order by id desc";

$sql = mysql_query($query) or die(mysql_error());

?>

[/code]

Work with it and adjust it to your needs...

[feri_soft]

I think the search with the * symbol is pretty nice,but if it is a text field??? and it is left blanc
i supose i will have to do a basic if/else . But if the query is from '*' to '*' will it find all?

[feri_soft]

And another think is that i preform many security functions like addslashe mysql_real_escape and so on.Is the '*" vuln. symbol so any of the functions will strip it???

[feri_soft]

Another thing i havent achieved many times is for example i have 10 checkboxes how if i chose 3 of them to search for 3 different property types.In other words how dynamicaly to create all the OR statements.Something with isset? Because i dont want to send the input as array.I want every checkbox with different name.

[Barand]

Here's an example of the method that I use

http://www.phpfreaks.com/forums/index.php/topic,89842.msg360739.html#msg360739

Checkboxes

[code]
<?php
if (isset($_POST['proptype'])) {
    $ptypes = join ("','", $_POST['proptype']);
    $where[] = "(property_type IN ('$ptypes'))";
}

?>
<form method='post'>
Search for property type<br>
<input type="checkbox" name="proptype[]" value="Villa"> Villa <br>
<input type="checkbox" name="proptype[]" value="Bungalow"> Bungalow <br>
<input type="checkbox" name="proptype[]" value="Shack"> Shack <br>
<input type="submit" name="action" value="Search">
</form>
[/code]

This gives

...WHERE property_type IN ('Villa','Shack')

which is a simpler form than

... WHERE ((property_type = 'Villa') OR (property_type = 'Shack'))

[feri_soft]

hmm nice but how to include include the variable to the query :

select * from properties where $where[] and asdasdasd ='123' or how???


i have another very hard to make thing too:

See this site: imoti.bg (pick the en version). I use your class baaselect for making the town and district chained.But how to make the search in many regions like in this site with the too buttons >> and <<
I know there is a javascript for the buttons functionality but i am interested in the php aspect?

[Barand]

As you can see under my avatar, I'm using

PHP 4.3  and 5.1
MySQL 5.0.1

[Barand]

See the link I also posted for the full story - the check box example is an extension to that

[feri_soft]

Thats ok but something to my second issue ??

[feri_soft]

You can see and example of what i want to do here:
http://www.dhtmlgoodies.com/scripts/multiple_select/multiple_select.html

[Barand]

If you have

<select name='location[]' multiple>

The php processing is exactly the same as for the checkboxes

[feri_soft]

ok i understand and is the format i entered for the query correct:
select * from properties where $where[] and asdasdasd ='123'

??

[Barand]

Did you read the post that I linked to in reply #6?

http://www.phpfreaks.com/forums/index.php/topic,89842.msg360739.html#msg360739

[feri_soft]

I see this row:
$whereclause = " WHERE " . join (' AND ', $where);

so it seem i should use the $where[] without the []

[Barand]

No, use $whereclause instead of $where[]

[feri_soft]

Itried to use the option with * but no result i type select all where price between * and * no result

[Barand]

If you are searching for any value in a column then do not include it in the query WHERE clause, ignore it.

[feri_soft]

Ok i made it like this:

[code]if (count($where) > 0){
$whereclause = " WHERE " . join (' AND ', $where);
}else{
echo "Chose at least 1!";
}
$t = mysql_query("SELECT * FROM listings $whereclause");[/code]

But now i cant made all the joints because i have in the databse for example type,district,city,bildtype
and all these are numbers.I have databse tables type districts cities buildtype but when i try something like
select listings.*,type.* ..... from listings,type...... and in the whereclause i add
$whereclause = " WHERE listings.city=cities.city(which is the id field) and listings.district=districts.district and ...." . join (' AND ', $where);
i got unknown field in the where clause .... for each field,but the field names are exact.Can you help me with the join.I saw something for the inner joins but i dont know how to make multiple inner joins with 1 query and which exacty to select,because if i have manually to add in the select every field not using the * i must add about 50 fields.Help me please! :)

[Barand]

Set up the joins like this

[code]
SELECT l.*, c.name, d.name
FROM listings l
INNER JOIN cities c ON l.city = c.city
INNER JOIN district d ON l.district = d.district
$whereclause
[/code]

[feri_soft]

Thx will try later.

[feri_soft]

Now i ahve something like:

[code]$q = mysql_query("SELECT listings.*, cities.name, districts.name FROM listings INNER JOIN cities ON listings.city = cities.city INNER JOIN districts ON listings.district = districts.district $whereclause");[/code]

and when i try to view {$row['city']} i get the number... for example 1

[Barand]

Change first bit to

$q = mysql_query("SELECT listings.*, cities.name as cityname, districts.name as distname ...

Then try viewing {$row['cityname']}

[feri_soft]

Yep it now works great.10x :)