My query wont work, its just returns everything

[rossbrunton]

Hi guys,

 

I need a hand with something. So far Ive managed to get my queries to work but Im doign a search page, just a  basic one in this case. A user enters information or chooses drop downs on the search page then it runs the search itself after finding out what the user searched for.

 

For example, if I search for someone in the UK in Doncaster, the query looks like this:

 

select * FROM members, member_stats, member_details WHERE members.member_profile_complete = '1' AND member_details.member_detail_country LIKE 'United Kingdom' AND member_details.member_detail_town LIKE 'doncaster' ORDER BY members.member_donation DESC, members.member_username ASC

 

For some reason this just returns everyone whos profile is complete, it doesnt add the other sections of the query. There is only 1 person in the DB who has completed his profile and who is in doncaster...why isnt this working?

 

Im completely stuck...please help!!

 

Thank you!

[kickstart]

Hi

 

You need to join the tables on some columns. At the moment that will bring back every combination of rows from the 3 tables (excluding a few due to the conditions).

 

All the best

 

Keith

[rossbrunton]

Hi Keith,

 

Thank you!

 

How do I join the tables? Sorry sounds a bit dim I know.

 

Thanks

 

Ross

[kickstart]

Hi

 

You join tables on matching values of columns.

 

For example, say you have 2 tables:-

 

Table1

Id1, Name1

1,Fred

2,Burt

3,Joe

 

Table2

Id2, Name2

1,Jane

2,Jill

3,Sarah

 

If you selected from them like this

 

SELECT Name1,Name2

FROM Table1, Table2

 

You would get 9 rows back:-

 

Name1,Name2

Fred,Jane

Fred,Jill

Fred,Sarah

Burt,Jane

Burt,Jill

Burt,Sarah

Joe,Jane

Joe,Jill

Joe,Sarah

 

This is probably not what you would want, given that most of the time the 2 tables would contain related data. Such as one table for customers and another table containing all the purchases of a customer. As such you would normally join the tables together on one or more columns that match the data together.

 

Something like this:-

 

SELECT Name1,Name2

FROM Table1 JOIN Table2 ON Table1.Id1 = Table2.Id2

 

Name1, Name2

Fred, Jane

Burt, Jill

Joe, Sarah

 

Does that make sense?

 

All the best

 

Keith

[rossbrunton]

Hi Keith, I got it to work using this:

 

$query = "select * FROM members, member_stats, member_details

WHERE member_stats.member_stats_id = members.member_id

AND member_details.member_detail_id = members.member_id

AND members.member_profile_complete = '1'

" . $search_query . "

ORDER BY members.member_donation DESC, members.member_username ASC";

 

Thanks Ross

[kickstart]

Hi

 

Yep, that is one way to join tables, putting the matches on the WHERE clause. However it is now an outdated method, and normally they are split into an ON clause. This makes it easier to read and and also has advantages when you need to check against particular values on a column before the join (useful when doing something called an outer join).

 

All the best

 

Keith

[rossbrunton]

Hi Keith,

 

Thank you for your help...if its outdated, I should really get my head around this joining thing....How could I join my 4 or 5 tables using the method you showed me?

 

Thanks

 

Ross

[kickstart]

Hi

 

Like this:-

 

SELECT *

FROM members

JOIN member_stats ON members.member_id = member_stats.member_stats_id

JOIN member_details ON members.member_id = member_details.member_detail_id

WHERE members.member_profile_complete = '1'

ORDER BY members.member_donation DESC, members.member_username ASC

 

You can also use OUTER JOINs which will bring back a row even if there is no matching row on one of the tables. For example say you had a table of people and a table of pets, and want a list of all the people and pets if they have them:

 

People

PeopleId, Name

1, Fred

2, Joe

3, Sid

 

Pets

PetId, PeopleId, PetName

1, 1, Dinky

2, 1, Fluff

3, 1, Kojak

4, 2, Sam

 

You would use:-

 

SELECT Name, PetName

FROM People LEFT OUTER JOIN Pets ON People.PeopleId = Pets.PeopleId

 

That would give you:-

 

Name, PetName

Fred, Dinky

Fred, Fluff

Fred, Kojak

Joe, Sam

Sid, NULL

 

All the best

 

Keith

[rossbrunton]

Hi Keith,

 

Thank you so much! Ive tried the query you created and it works great...I think I understand what its doing, its actually shorter to write too which is good....Im going to try and work with it andthe more I use it the better Ill get to understand what its doing...thanks again!

 

Ross

[kickstart]

Hi

 

No problem.

 

There is one other advantage that comes along when you are using outer joins.

 

In the example I gave above, say you wanted a list of all the people and their pets names, but only wanted the pets named "Dinky".

 

You could try:-

 

SELECT Name, PetName

FROM People LEFT OUTER JOIN Pets

WHERE People.PeopleId = Pets.PeopleId

AND Pets.PetName = 'Dinky'

 

However that would just give you

 

Name, PetName

Fred, Dinky

 

and would miss out on all the other names which didn't have a pet called Dinky.

 

What you could do instead would be:-

 

SELECT Name, PetName

FROM People LEFT OUTER JOIN Pets

ON People.PeopleId = Pets.PeopleId

AND Pets.PetName = 'Dinky'

 

which would only return the right hand side of the join if the pet was called Dinky, but would always return the left hand side:-

 

Name, PetName

Fred, Dinky

Joe, NULL

Sid, NULL

 

This can be quite useful (although some implementation of SQL, such as MS Access, do not support this).

 

All the best

 

Keith