Multiple search

[sridhar golyandla]

Hi 2 all,

 

        Please how to search the variable in a table with multiple columns using php and mysql

 

EG: table1 having the columns like col1, col2, col3, col4.....

 

how search the value in coln?

[phpretard]

I found this useful after reading:

 

http://php.about.com/od/phpwithmysql/ss/php_search.htm

[phant0m]

It depends...

Do you want the result to match the search string entered exactly?

Or does the search string have to be somewhere in the result?

This would return all results where $search is somewhere in coln

SELECT * FROM table WHERE coln LIKE '%$search%'

 

Make sure to escape $search properly before putting it into a query

[sridhar golyandla]

i tried with that link a long back ago... but it was not working properly with my application....

 

How to search the value whether it has in col1, col2...... to con n

 

SELECT DISTINCT r.rid, r.email, s.col1, s.col2, s.col3, s.col4, s.col5

FROM register r, skill s

WHERE s.col1 LIKE '%PHP%'

OR s.col2 LIKE '%PHP%'

OR s.col3 LIKE '%PHP%'

OR s.col4 LIKE '%PHP%'

OR s.col5 LIKE '%PHP%'

AND s.rid= r.rid;

 

getting duplicate values. please help me out.

[kickstart]

Hi

 

That should work without duplicates.

 

The duplicates may be becuase rid is not unique on register or skill.

 

All the best

 

Keith

[redarrow]

 

try grouping them.

 

 

 

SELECT DISTINCT r.rid, r.email, s.col1, s.col2, s.col3, s.col4, s.col5

FROM register r, skill s

WHERE (s.col1 LIKE '%PHP%')

OR (s.col2 LIKE '%PHP%')

OR (s.col3 LIKE '%PHP%')

OR (s.col4 LIKE '%PHP%')

OR (s.col5 LIKE '%PHP%')

AND s.rid= r.rid;

[kickstart]

Hi

 

Seen an error:-

 

SELECT DISTINCT r.rid, r.email, s.col1, s.col2, s.col3, s.col4, s.col5

FROM register r, skill s

WHERE (s.col1 LIKE '%PHP%'

OR s.col2 LIKE '%PHP%'

OR s.col3 LIKE '%PHP%'

OR s.col4 LIKE '%PHP%'

OR s.col5 LIKE '%PHP%')

AND s.rid= r.rid;

 

How you had the SQL the last like statement would be ANDed with the s.rid = r.rid, which would mess up the table join.

 

Probably better would be:-

 

SELECT DISTINCT r.rid, r.email, s.col1, s.col2, s.col3, s.col4, s.col5

FROM register r JOIN skill s ON s.rid= r.rid

WHERE s.col1 LIKE '%PHP%'

OR s.col2 LIKE '%PHP%'

OR s.col3 LIKE '%PHP%'

OR s.col4 LIKE '%PHP%'

OR s.col5 LIKE '%PHP%';

 

All the best

 

Keith

[sridhar golyandla]

Thanks kickstart

 

It is working fine with two tables, if it is more than two table... it was not working well... getting empty rows... i checked all the tables with r.rid, all tables having the rows values of corresponding r.rid, Plz help me out.....

[kickstart]

Hi

 

Can you post the sql that is not working with more than 2 tables?

 

Alll the best

 

Keith

[sridhar golyandla]

SELECT DISTINCT r.rid, r.email, s.col1, s.col2, s.col3, s.col4, s.col5, q.col1, j.col1,

 

c.col1

FROM register r JOIN skill s ON s.rid= r.rid join qal q on q.rid=r.rid join job j on j.rid=r.rid join col c on c.rid=r.rid

WHERE s.col1 LIKE '%PHP%'

OR s.col2 LIKE '%PHP%'

OR s.col3 LIKE '%PHP%'

OR s.col4 LIKE '%PHP%'

OR s.col5 LIKE '%PHP%';

[kickstart]

Hi

 

That looks like it should work. However I would suspect the issue might be that rid is not unique on one of those tables, hence bringing back rows that duplicated (eg, say table job had 2 rows for an rid of 1, yet these rows had the same value of coll but possibly a different value of another column you would get a full duplicate row).

 

All the best

 

Keith

[sridhar golyandla]

Thank you alot kickstart....

 

It is working, now prob has solved....