multiple search not working

[temp2escape]

tbl_data
-------------------------------------------------------------------------------------
id.............name.............contactID............. location.............specialize
-------------------------------------------------------------------------------------
1.............Josh.............1902A .............New York.............IT
2.............Robert...........8291B............. Boston.............Business
3.............Ben.............2893Z ............. Boston.............Marketing
4.............Matt.............X2379............. Beverly Hills.............IT

3 searching option...

1. text box with the id keyword (for name or contactID)
2. drop down list with the id location (for location). can be proceed without selecting any value.
3. drop down list with the id specialize (for specialize). can be proceed without selecting any value.

I did this sql query.

-----------------------------------------------------------------------------------------

SELECT *
FROM tbl_data
WHERE (name LIKE %Colname1%) OR (contactID LIKE %Colname1%) AND location = Colname2 AND specialize = Colname3

-----------------------------------------------------------------------------------------

Name: Colname1
Type: Text
Default value: %
Run-time value: $_POST['keyword']

Name: Colname2
Type: Text
Default value: %
Run-time value: $_POST['location']

Name: Colname3
Type: Text
Default value: %
Run-time value: $_POST['specialize']

-----------------------------------------------------------------------------------------

It is not returning the result as expected. can someone advise me where i go wrong?

[rocknbil]

Wecome aboard!

For starters,

WHERE (name LIKE %Colname1%) OR (contactID LIKE %Colname1%) AND location = Colname2 AND specialize = Colname3

It's going to select any instance of name and ignore your "and's" for this part of the where. It's only going to restrict contactID to the "and's." Read it left to right, what you're doing is actually this:

WHERE name LIKE %Colname1% OR (contactID LIKE %Colname1% AND location = Colname2 AND specialize = Colname3)

Note the new position of the parentheses. Try

WHERE (name LIKE %Colname1% OR contactID LIKE %Colname1%) AND location = Colname2 AND specialize = Colname3

You generally don't need to add parentheses around ands, but OR's can be very slippery this way.

[temp2escape]

i tried your suggestion, but its getting worst. not showing any result at all.

[rocknbil]

It's possibly because it's working as it should. :-)

Just noticed: this

like %something%

Should be quoted:

like '%something%'

Also, I'm a little confused about 'Colname1, Colname2, Colname3'. This should be values to match on, I was **assuming,** say, "Colname1" would actually be the values in Colname 1 ("Josh.");

Here's a complete exercise, I tested it for you. I've added/altered a few records here to demonstrate the difference. Note "Steven", and the "2A" in his contactID. Since your AND requires both location and specialize, note also I changed the location and specialize to "Boston" and "Business" in other records to demonstrate.

create table tbl_data (id int(11) primary key auto_increment,name varchar(150) not null,contactID varchar(12) not null,location varchar(150) not null,specialize varchar(50) not null);

insert into tbl_data (name,contactID,location,specialize) values ('Josh','1902A','Boston','Business');
insert into tbl_data (name,contactID,location,specialize) values ('Robert','8291B','Boston','Business');
insert into tbl_data (name,contactID,location,specialize) values ('Steven','12392A','Boston','Business');
insert into tbl_data (name,contactID,location,specialize) values ('Ben','2893Z','Boston','Marketing');
insert into tbl_data (name,contactID,location,specialize) values ('Matt','X2379','Beverly Hills','IT');

select * from tbl_data where (name like '%Josh%') or (contactID like '%2A%') and location='Boston' and specialize='Business';

id   ¦   name   ¦   contact_id   ¦  location   ¦   specialize 
5   ¦   Josh   ¦   1902A     ¦  Boston    ¦   Business 
7   ¦  Steven  ¦   12392A     ¦  Boston    ¦   Business 

Since Josh and 2A exist in the first record, it returns true; since only 2A exists in record 7 AND it matches on Boston and Business, it returns true. Josh would return even if it was NOT Boston and Business. Next example shows this:

select * from tbl_data where (name like '%Matt%') or (contactID like '%2A%') and location='Boston' and specialize='Business';

id   ¦   name   ¦   contact_id   ¦  location   ¦   specialize 
5   ¦   Josh   ¦   1902A     ¦  Boston    ¦   Business 
7   ¦  Steven  ¦   12392A     ¦  Boston    ¦   Business 
9   ¦  Matt   ¦   X2379     ¦  Beverly Hills ¦  IT 

Matt is neither in Boston or business. If you're trying to restrict to Boston and Business, this is an incorrect result.

The exact same two queries, except for moving the parentheses:

select * from tbl_data where (name like '%Josh%' or contactID like '%2A%') and location='Boston' and specialize='Business';

id   ¦   name   ¦   contact_id   ¦  location   ¦   specialize 
5   ¦   Josh   ¦   1902A     ¦  Boston    ¦   Business 
7   ¦  Steven  ¦   12392A     ¦  Boston    ¦   Business 

This gives you the exact same records as the original query. The difference shows itself in the second query:

select * from tbl_data where (name like '%Matt%' or contactID like '%2A%') and location='Boston' and specialize='Business';

id   ¦   name   ¦   contact_id   ¦  location   ¦   specialize 
5   ¦   Josh   ¦   1902A     ¦  Boston    ¦   Business 
7   ¦  Steven  ¦   12392A     ¦  Boston    ¦   Business 

Matt is now eliminated because the AND for Boston and Business is required with EITHER condition in your parenthetical OR. Matt's in 90210. :-) No records are found for a Matt in Boston and Business.

In summary, it really depends on what you want out of the final query. If you want name (without needing location and specialize,) OR contactID AND location and specialize, your first query is fine. If you want (either name or contactID) AND either of those HAVE to match on location and specialize, you need to use parentheses accordingly.

[temp2escape]

thank you for your explanation... i appreciate your effort in creating and testing the table.

ok. the second query is almost what i want it to be. but i also want it to return result even without inserting any value. like this...

SELECT *
FROM tbl_data where (name like '%' or contactID like '%') and location='%' and specialize='%';

SELECT *
FROM tbl_data where (name like 'josh' or contactID like '%') and location='%' and specialize='%';

SELECT *
FROM tbl_data where (name like '%' or contactID like '2A') and location='%' and specialize='%';

SELECT *
FROM tbl_data where (name like '%' or contactID like '%') and location='boston' and specialize='%';

SELECT *
FROM tbl_data where (name like '%' or contactID like '%') and location='%' and specialize='%';

[rocknbil]

But there's no single '2A'

like '2A'

or %

name like '%' ....and location='%'

in your data.

What you may be looking for is a way to dynamically build the select, based on whether or not values are entered in the form?

A small addition here, in your form, add radio buttons next to each form field for "exact" or "partial." Have the radio button names prepended by 'exact_':

<input type="radio" name="exact_name" value="0" checked> Partial
<input type="radio" name="exact_name" value="1"> Exact

if ($data{'name'}) { 
 $where .= ($data{'exact_name'})?"=\"$data{'name'}\":" like \"\%$data{'name'}\%\""; 
} 
if ($data{'contactID'}) { 
 if ($where) { $where .= ' or'; } 
 $where .= ($data{'exact_contactID'})?"=\"$data{'contactID'}\"":" like \"\%$data{'contactID'}\%\""; 
} 
## If both are here you need the parentheses 
if (( $data{'name'}) and ($data{'contactID'})) { 
 $where = '('.$where.')'; 
} 
if ($data{'location'}) { 
 if ($where) { $where .= ' and'; } 
 $where .= ($data{'exact_location'})?"=\"$data{'location'}\"":" like \"\%$data{'location'}\%\""; 
} 
if ($data{'specialize'}) { 
 if ($where) { $where .= ' and'; } 
 $where .= ($data{'exact_specialize'})?"=\"$data{'specialize'}\"":" like \"\%$data{'specialize'}\%\""; 
} 

$select = "select * from tbl_data"; 
if ($where) { $select .= " where $where"; } 

The possible selects, depending on what's entered in the form:

If nothing is entered in the form at all, you get all records:
select * from tbl_data;

Just name, exact:
select * from tbl_data where name="$data{'name'}";

Name, partial:
select * from tbl_data where name like "%$data{'name'}%";

name and contact ID, partial:
select * from tbl_data where (name like "%$data{'name'}%" or contactID like "%$data{'contactID}%");

Name exact, contactID exact, location exact:
select * from tbl_data where (name="$data{'name'}" or contactID="$data{'contactID}") and location="$data{'location'}";

Name exact, contactID exact, location exact, specialize exact:
select * from tbl_data where (name="$data{'name'}" or contactID="$data{'contactID}") and location="$data{'location'}" and specialize="$data{'specialize'}";

..... and so on, letting your programming build the select based on what's in the form.

Notable to mention, it's a bad idea to use database field names in a publicly accessible form as another layer against SQL injecting, done here for example only.