Jump to content


Photo

How to make a search with many options


  • Please log in to reply
26 replies to this topic

#1 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 20 October 2006 - 07:24 PM

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!

#2 Psycho

Psycho
  • Moderators
  • Move along, nothing to see here
  • 11,892 posts
  • LocationCanada

Posted 20 October 2006 - 08:12 PM

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:

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

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:
<?php
if ($POST_['rooms'] <> "*") {
    $roomQuery = " 'rooms' > " . $POST_['rooms'] . " AND";
} else {
    $roomQuery = ""
}

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

The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#3 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 20 October 2006 - 09:15 PM

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

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

?>


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

#4 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 21 October 2006 - 08:37 AM

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?

#5 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 21 October 2006 - 08:40 AM

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???

#6 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 21 October 2006 - 08:47 AM

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.

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 21 October 2006 - 09:55 AM

Here's an example of the method that I use

http://www.phpfreaks....html#msg360739

Checkboxes

<?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>

This gives

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

which is a simpler form than

... WHERE ((property_type = 'Villa') OR (property_type = 'Shack'))
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 21 October 2006 - 11:32 AM

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?

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 21 October 2006 - 11:36 AM

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

PHP 4.3  and 5.1
MySQL 5.0.1
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 21 October 2006 - 11:41 AM

See the link I also posted for the full story - the check box example is an extension to that
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#11 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 21 October 2006 - 12:03 PM

Thats ok but something to my second issue ??

#12 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 21 October 2006 - 12:27 PM

You can see and example of what i want to do here:
http://www.dhtmlgood...ple_select.html

#13 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 21 October 2006 - 02:02 PM

If you have

<select name='location[]' multiple>

The php processing is exactly the same as for the checkboxes
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#14 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 21 October 2006 - 06:49 PM

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

??

#15 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 21 October 2006 - 06:51 PM

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

http://www.phpfreaks....html#msg360739
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#16 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 22 October 2006 - 08:55 AM

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

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

#17 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 22 October 2006 - 08:57 AM

No, use $whereclause instead of $where[]
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#18 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 28 October 2006 - 05:54 AM

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

#19 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 28 October 2006 - 06:58 AM

If you are searching for any value in a column then do not include it in the query WHERE clause, ignore it.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#20 feri_soft

feri_soft
  • Members
  • PipPipPip
  • Advanced Member
  • 147 posts

Posted 28 October 2006 - 01:18 PM

Ok i made it like this:

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

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! :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users