SELECT FROM DATABASE WHERE

Question

I have 2 tables, members and appoint. In appoint i have members_id. Is that correct?

$result = mysql_query ("SELECT * FROM members,appoint WHERE members_id=id ORDER BY id");
while ($row = mysql_fetch_array ($result))

This returns me this error "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given"

Any ideas?

i want a join. its not a variable $id, it is the field of table members — user3808157, Jul 06 '14 at 08:48
There you go, you just need to fix which column matches which. In my example it's members.members_id and appoint.id. — Shomz, Jul 06 '14 at 08:50

juergen d · Answer 1 · 2014-07-06T08:59:02.047

1

Use the explicit join syntax which is

SELECT * FROM members
INNER JOIN appoint ON members.id = appoint.members_id
ORDER BY members.id

And better use the table name when refering to columns. If two tables have the same column names then the DB won'T know which one to take.

edited Jul 06 '14 at 08:59

answered Jul 06 '14 at 08:47

juergen d

1

And it will lead to `Error Code: 1052. Column 'id' in where clause is ambiguous` (original query) or `Error Code: 1052. Column 'id' in on clause is ambiguous` (explicit joins) – VMai Jul 06 '14 at 08:56
Good catch. Corrected that :) But not in the `on` clause but in the `order by` – juergen d Jul 06 '14 at 08:59

score 0 · Answer 2 · answered Jul 06 '14 at 08:50

0

You can try this

SELECT m.*, a.* FROM members AS m JOIN appoint AS a ON m.members_id = a.id ORDER BY a.id ASC

answered Jul 06 '14 at 08:50

2 Answers2