[RESOLVED] Selecting WHERE problems!

MitchEvans

Hi Guys i'm sure this is a simple problem!

I need to select data from directory where clean1_id = $carpet_cleaner1 OR $window_cleaner1. My first problem is at the moment the code below only gets data for $carpet_cleaner1.
My second problem is that I would like to have a WHERE that looks for clean1_id,clean2_id OR clean3_id and any of these equal to $carpet_cleaner1 OR $window_cleaner1.

So it will be any of the clean_id's equal to any of the $cleaners.

"SELECT * FROM directory WHERE authorised = 'Y' AND clean1_id = '$carpet_cleaner1' OR '$window_cleaner1'";

Hope this makes sense appreciate you looking guys!

Piranha

First let break your statement up into logical parts. Each part should return either TRUE or FALSE. Here are the parts:

authorised = 'Y'
clean1_id = '$carpet_cleaner1'
'$window_cleaner1'

As you can see the last part doesn't return true or false unless the column is boolean. You have to make it a whole logical expression for it to work. Then you probably need to add a few paranthesis.

The second problem is because your database is not normalized. Search for "database normalization" to understand what is wrong and how you should normalize it. A tip is that you need another table.

MitchEvans

Thanks Piranha,

I think my field names are confusing you though, clean1_id or clean2_id will be carpet_cleaner or window_cleaner not an actual integer id the carpet_cleaner or window_cleaner is there id.

So the SELECT statement is in theory looking like this (but not correct mysql):

WHERE clean1_id OR clean2_id = carpet_cleaner OR window_cleaner

As the below is too long:

WHERE clean1_id = carpet_cleaner OR clean1_id=window_cleaner OR clean2_id=carpet_cleaner OR clean2_id=window_cleaner

MitchEvans

So this works fine but obvioulsy it will be just too long:

AND clean1_id = '$carpet_cleaner1' OR clean1_id = '$window_cleaner1' OR clean2_id = '$carpet_cleaner1' OR clean2_id = '$window_cleaner1'

Piranha

No, I'm not confused. Your explanation validates what I thought, that you use more than one column in the table that could contain the same value. It will get you in trouble, the only way around it is to normalize. If you still think that I got it wrong please post the whole table structure and I can have a better look.

But what I can't understand is why the last line is to long. It is what you get if you don't normalize. It will be worse when you decide to add another cleanid, then you have to change all the queries.

Of course, you can use IN. It doesn't solve the problem that the table is not normalized, but it solves the problem with searching for many values:

SELECT something
FROM somewhere
WHERE clean_id IN ('window_cleaner', 'carpet_cleaner')

MitchEvans

So without normalisation in your personal opinion would this make the page and site run really slowly?

I do have another table with all the cleaning types in it with an id, but still my original table would have to have a clean_id, so the integers will be duplicated just the same.

And is that the shortest we can get the below?

clean1_id IN ('$carpet_cleaner1','$window_cleaner1') OR clean2_id IN ('$carpet_cleaner1','$window_cleaner1');

Is there no way I could say:

clean1_id OR clean2_id IN ('$carpet_cleaner1','$window_cleaner1');

Unfortunately the above doesn't work!

Or anything similar?

Piranha

Then add another table with references to both the tables you have. It is how to handle many-to-many relationships.

MitchEvans

I think i'll still have the same problem.

Is there no way I can put that in the SELECT statement as I want?

WHERE clean1_id OR clean2_id IN ("carpet_cleaner")OR("window_cleaner")

Piranha

No, you won't have the same problem. It is easy if you normalize the database. It will solve this problem and lots of others. But you won't fully understand it until you have it normalized and have worked with it that way for a while.

Anyway I won't bother about this thread anymore. I don't think it is worth spending my time trying to help a person that want a quick and dirty fix instead of a solution that will solve this problem and many other problems to come. Maybe someone else will answer.

MitchEvans

Ok so I have these tables: