Problem selecting rows from 2 tables..

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
orbdrums
Forum Commoner
Posts: 82
Joined: Wed Sep 14, 2011 11:42 pm

Problem selecting rows from 2 tables..

Post by orbdrums »

Hey all,

I am trying to get rows from 2 tables in the same query. I have a common field in both tables called "member_id". The following error message is being returned when I run the query and I don't know why.

Invalid query: Column 'member_id' in where clause is ambiguous Whole query: SELECT * FROM login_hist,logout_hist WHERE `member_id` = '4' ORDER BY `login_date` DESC, `login_time` DESC

This is the php code that generates the error:

Code: Select all

$query = "SELECT * FROM login_hist,logout_hist WHERE `member_id` = '$mbr_id' ORDER BY `login_date` DESC, `login_time` DESC";

Thanks!!!
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problem selecting rows from 2 tables..

Post by Celauran »

First of all, don't SELECT *. Figure out which columns you need and select those.

If you've got two columns with the same name in two tables, just specify which table you're using.

Code: Select all

SELECT cola, colb, colc
FROM tablea, tableb
WHERE tablea.cola = 'some value'
orbdrums
Forum Commoner
Posts: 82
Joined: Wed Sep 14, 2011 11:42 pm

Re: Problem selecting rows from 2 tables..

Post by orbdrums »

Okay, so what if the field names are different in the tables? My first table (login_hist) has 'login_date' and 'login_time' and the second table (logout_hist) has 'logout_date' and 'logout_time'. I'm not sure whether this is a database structure problem or a php syntax problem.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: Problem selecting rows from 2 tables..

Post by Celauran »

It's the member_id that's ambiguous because it exists in both tables. The other columns shouldn't give you any trouble. You can always specify table.column, though.
orbdrums
Forum Commoner
Posts: 82
Joined: Wed Sep 14, 2011 11:42 pm

Re: Problem selecting rows from 2 tables..

Post by orbdrums »

I don't understand what you're saying. Should I get something you're not saying?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Problem selecting rows from 2 tables..

Post by pickle »

When a column is ambiguous, it means MySQL can't determine which column you mean, because it exists multiple times in the tables you're querying. If a column isn't ambiguous, it means it only exists once and MySQL knows which column you mean.

To make an ambiguous column unambiguous, you need to give MySQL more information about the column so it knows exactly which one you mean. So, modify your query like so:

Code: Select all

SELECT * FROM login_hist,logout_hist WHERE `login_hist`.`member_id` = '$mbr_id' ORDER BY `login_date` DESC, `login_time` DESC";
...or specify `logout_hist` if you need.

Also, I'm moving this to the "Databases" forum.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
orbdrums
Forum Commoner
Posts: 82
Joined: Wed Sep 14, 2011 11:42 pm

Re: Problem selecting rows from 2 tables..

Post by orbdrums »

Getting closer. login_hist has the correct number of records but logout_hist has every record from every user because there is no filter. How do I include only those records from logout_hist that are the same as login_hist. That is, equal to the current member id. Thanks again for your help.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Problem selecting rows from 2 tables..

Post by pickle »

That'd be in the WHERE clause. Set a condition that filters rows from `logout_hist`.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
orbdrums
Forum Commoner
Posts: 82
Joined: Wed Sep 14, 2011 11:42 pm

Re: Problem selecting rows from 2 tables..

Post by orbdrums »

Everything seems to be working correctly now. Thanks for the help!
Post Reply