MYSQL & PHP select from 2 different tables + make a array of it

Question

I want to make a page where I can see who have purchased and who not, and I want make the people who HAVEN"T purchased into a new array. I searched some mysql function, but I couldn't find how to make the both of them. I know it will be like this:

mysql_query("SELECT id, name, organisation_id
FROM bought, organisation
WHERE id = id
)

but I don't know from here on, I think I need to use join function but how can I use this function in the way I want?

organisations who bought something are in the other table.

example:

$peoplewhodidn'tbuy:

$peoplewhodidbuy:

Bought table:

id product | organisation id | name

organisation table:

organisation id | name | type

Your schema is not speaking for itself at all. How can you determine if someone bought something? — kero
– kero, Commented Feb 20, 2014 at 14:03
Can you show the table structures, and how are you determining people who did/didn't buy ? There might be a possibility of doing it in one query. — Raiyan
– Raiyan, Commented Feb 20, 2014 at 14:08
mysql_* has been deprecated for quite some time now, stop using it — Elias Van Ootegem
– Elias Van Ootegem, Commented Feb 20, 2014 at 14:12
@STP38: Change schools. You and the school have a contract: they teach you X, and you pay them to do so. If they promised you to teach you science, and then told you the sun revolved around the earth, you'd call that a breach of contract — Elias Van Ootegem
– Elias Van Ootegem, Commented Feb 20, 2014 at 14:18

Akhil Sidharth · Accepted Answer · 2014-02-20 14:27:33Z

1

try

SELECT t1.*
FROM organisation t1
LEFT JOIN Bought t2 ON t2.organisation_id = t1.organisation_id
WHERE t2.organisation_id IS NULL

edited Feb 20, 2014 at 14:27

answered Feb 20, 2014 at 14:16

Akhil Sidharth

7461 gold badge6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kickstart Over a year ago

Minor typo I think, but suspect you meant to check t2.organisation_id for null, not the t1 version.

STP38 Over a year ago

@AkhilSidharth what you do mean by t1? organisations or bought?

Kickstart Over a year ago

t1 and t2 are alias' for the table names. So this query has FROM organisation t1 which says that t1 is an alias for organisation, and then t1.organisation_id is referring to organisation.organisation_id. Makes the code smaller and often more readable. Far more common when a particular table is used multiple times in the same select statement.

Ian Kenney · Accepted Answer · 2014-02-20 14:16:37Z

1

Assuming organisation has the 'people' and bought has the purchases

something like:

select
   organisation.id, 
   organisation.name
from 
  organisation 
where 
  id in (
    select organisation_id from bought 
  )

would give the buyers

select 
  organisation.id, 
  organisation.name
from 
  organisation 
where 
  id not in (
    select organisation_id from bought 
  )

for the non buyers

answered Feb 20, 2014 at 14:16

Ian Kenney

6,4561 gold badge27 silver badges45 bronze badges

Collectives™ on Stack Overflow

MYSQL & PHP select from 2 different tables + make a array of it

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related