All entries

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

All entries

Post by ursl40 »

Hy!

I have two tables (users, cars) that are connected through a foreign key user_id in cars (= id in users - users.id).

Some users have a car selected for them (cars.user_id = users.id), but some haven't got (cars.user_id = NULL).

I was wondering, how can I get all users (id, name, surname) (with car selected or not) with SELECT? It's a bit tricky, because I need also all the data (id, name) from cars table, also in this query...

I tried with: $query = "SELECT id, name, surname, id, name FROM users, cars";
$query = "SELECT id, name, surname, id, name FROM users, cars WHERE users.id = cars.user_id";


Thanks for Your help!
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: All entries

Post by Darhazer »

use so-called LEFT (outer) JOIN
$query = "SELECT * FROM users LEFT JOIN cars ON cars.user_id = users.id";
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: All entries

Post by ursl40 »

Darhazer, it's working, thanks, but when all users are listed, for users without a car, their ID's are missing:

Users:

name: x // have a car (users.id = cars.user_id)
surname: y
ID: 1

name: y // doesn't have a car (cars.user_id = NULL)
surname: z
ID: // ID is missing

name: w // have a car (users.id = cars.user_id)
surname: z
ID: 3
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: All entries

Post by Darhazer »

Well, at this point you were supposed to actually read the manual on joins
They are missing because the name of the column is the same
There are several ways to fix this, here is one:
Instead of SELECT * user
SELECT users.id, users.name, users.surname, cars.id as car_id, cars.name as car_name
Post Reply