[RESOLVED] Searching MySql Table using 2+ WHERE's

Oni

Hey--

I want to search a big mysql table with a query something like this. However I dunno if 'WHERE a=1, WHERE b=2' would work, so I'm asking if it will and if it doesn't is there another query that would return what I'm looking for?

Query:

SELECT * FROM mytable WHERE row=1, WHERE row=3 LIKE '%my_seach_string%'

Thanks!

EDIT: Forgot about the 'Database' board. Maybe a mod can move this post please?
Sorry!

Piranha

It does not work with 2 WHERE. Instead use AND and OR like this:

SELECT * FROM mytable WHERE row=1 AND row2=2 // Will return all rows from mytable where row=1 and row2=2

SELECT * FROM mytable WHERE row=1 OR row=2 // Will return all rows from mytable where row is either 1 or 2

But you should look at like as well, it should be used as below:

SELECT * FROM mytable WHERE row LIKE '%my_seach_string%' // Will return all rows from mytable where the search string is somewhere in row field

Oni

There still seems to be something wrong--

SELECT * FROM arama WHERE adi='good' OR adi='middle' OR adi='bad' AND klasor LIKE '%b%'

The search results don't seem to be exactly correct. Maybe this is because I'm using only letters.

For example when I do %bb% I also get a row that only has one b in the klasor column.

There is improvement when I use many ORs and only one AND before LIKE. It was more troublesome when I did all ANDs.

edcellgavin

try to use parentheses to group and/or prioritize your where statement
e.g.

from your query

SELECT * FROM arama WHERE adi='good' OR adi='middle' OR adi='bad' AND klasor LIKE '%b%'

to
SELECT * FROM arama WHERE (adi='good' OR adi='middle' OR adi='bad') AND klasor LIKE '%b%'

will look for 'adi' having any of those values but must also satisfy the klasor LIKE '%b%' condition

Piranha

Oni wrote:
SELECT * FROM arama WHERE adi='good' OR adi='middle' OR adi='bad' AND klasor LIKE '%b%'

You have to watch out a little when using both AND and OR. The select you wrote will return all results where one of the following is true:
- adi = 'good'
- adi = 'middle'
- adi = 'bad' and klasor includes a b

I think that you want that the klasor includes a b and that adi have any of the values. Then it should be:

SELECT * FROM arama WHERE (adi='good' OR adi='middle' OR adi='bad') AND klasor LIKE '%b%

Oni

That's what I was looking for--

Thanks you two! 🙂