Select values from 2 tables based on 1 variable.

Question

Expecting this to simple and I'm missing something obvious.

I have 2 tables.

Car:

CarID, dealerID, info1, info2, info3

Enquiries:

enqID, carID, dealerID, info1, info2, info3

and 1 variable to filter with:

$_SESSION['dealerID']

The enquiries are offers for the car someone has submitted, so their email and price etc.

I need to get the details of the Car and the details of the Enquiry/Offer in one query. So select cars where dealerID = session and then the same, but based of them results.

I have tried:

 $query = $db->query
        ("SELECT * 
          FROM cars, enquiries 
          WHERE cars.dealerID     = ".$_SESSION['dealerID']."
          AND  enquiries.dealerID = ".$_SESSION['dealerID']."");

But this just returns all the cars with matching dealerID as the session.

Will I need to do 1 queries and an if loop through the array? So query getting all cars from dealer, then loop through each $row['dealerID'], until I find matches with dealerID in enquiries?

Sidenote: Use `'".$_SESSION['dealerID']."'` for both. You need extra single quotes. — Funk Forty Niner, Apr 25 '14 at 12:46

score 2 · Accepted Answer · answered Apr 25 '14 at 12:50

2

I am not sure I understood you well but try this

SELECT * 
FROM cars INNER JOIN enquiries 
ON cars.CarID=enquiries.carID
WHERE cars.dealerID     = '".$_SESSION['dealerID']."'";

answered Apr 25 '14 at 12:50

CodeBird

3,883
2
20
35

I had a feeling OP would need some type of JOIN, but I have a terrible time with those myself. I can't wrap my head around those lol – Funk Forty Niner Apr 25 '14 at 12:51
@Fred-ii- thanks for the +1, it is just practice, and they are very important and useful :) – CodeBird Apr 25 '14 at 12:53
1

You're welcome. I'm sure they are, but I tried to understand JOINs, and for the life of me, I can't understand them. I guess I'll just have to try harder and practice more slowly. – Funk Forty Niner Apr 25 '14 at 12:54
sometimes it is just psychological, maybe you're just thinking they're hard and scares you... They're really easy, keep that in mind @Fred-ii- – CodeBird Apr 25 '14 at 12:55
If you're not sure if you understand the question well, why not ask for more info with a comment? Unless a user hasn't been seen for a while of course. – Chris Wesseling Apr 25 '14 at 12:56
1

I am not sure I understood WELL, it still means I understood and answered based on what I understood. @ChrisWesseling – CodeBird Apr 25 '14 at 12:57
1

I've read quite a few tutorials on them, and TBH they are either not well-explained or don't give enough information. @CodeBird I'm a persistent and determined person and I haven't said I'll "give up" ;-) I just need more time than others. – Funk Forty Niner Apr 25 '14 at 12:57
1

@Fred-ii- "An Introduction to Database Systems", ISBN 0-321-19784-4 is a great primer. – Chris Wesseling Apr 25 '14 at 13:07
@ChrisWesseling Thanks Chris. I see it's written by an old pro :) The guy seems to know his stuff quite well. Plus, being quite an (*highly*) educated man. – Funk Forty Niner Apr 25 '14 at 13:09
1

-1 normally comes with an explanation.... @ChrisWesseling it looks like a nice book, not cheap though lol – CodeBird Apr 25 '14 at 13:15
Awww yeah, works thank you, I had a feeling I'd need JOINs but find them confusing, with left and right and inner joins. Wishful thinking I could avoid them. – Vereonix Apr 25 '14 at 13:19
@CodeBird Good (*quality, well-written*) books usually are not cheap lol - I found a copy at our local University which I could probably borrow till I can buy one. Visit http://en.wikipedia.org/wiki/Christopher_J._Date and http://en.wikipedia.org/wiki/Special:BookSources/0321197844 where I found the resources. – Funk Forty Niner Apr 25 '14 at 13:31

score 1 · Answer 2 · answered Apr 25 '14 at 12:58

1

Firstly, take care of SQL Injection , your code is not secure. Prefer use prepared statement (http://www.php.net/pdo.prepared-statements)

For your query, you need to define the columns insteed of using the star as

SELECT cars.*, enquiries.* 
          FROM cars, enquiries 
          WHERE ( cars.dealerID     = ? AND  enquiries.dealerID =  ? );

or more precise to not duplicate carId

SELECT cars.carID, cars.dealerID, cars.info1, cars.info2, cars.info3, enquiries.enqID, enquiries.dealerID, enquiries.info1, enquiries.info2, enquiries.info3
          FROM cars, enquiries 
          WHERE ( cars.dealerID     = ?  and enquiries.dealerID =  ? );

answered Apr 25 '14 at 12:58

hgregoire

111
1
3

:) The algorithm for the **Related** column for questions [tag:php] & [tag:mysql] converges on [this injection question](http://stackoverflow.com/q/60174/383793). – Chris Wesseling Apr 25 '14 at 13:02
It still selects just all cars with the matching dealerID, pretty sure I need to use JOINs, I just find them confusing. – Vereonix Apr 25 '14 at 13:15

score 0 · Answer 3 · answered Apr 25 '14 at 12:49

0

You need to join the tables on a proper join key. I believe CarId is the right key in this case:

SELECT * 
FROM cars c join
     enquiries e
     ON e.CarId = c.CarId
WHERE c.dealerID     = ".$_SESSION['dealerID']." AND
      e.dealerID = ".$_SESSION['dealerID']

answered Apr 25 '14 at 12:49

Gordon Linoff

1,242,037
58
646
786

@ChrisWesseling . . . I'm not sure, but the accepted answer is essentially the same as this. – Gordon Linoff Apr 25 '14 at 13:35

score 0 · Answer 4 · answered Apr 25 '14 at 12:50

You need to join the tables, if you want to take data from both the tables with conditions on both the tables:-

SELECT * 
FROM cars left join
  enquiries ON enquiries.CarId = cars.CarId
WHERE cars.dealerID     = ".$_SESSION['dealerID']."
  AND
  enquiries.dealerID = ".$_SESSION['dealerID']

Select values from 2 tables based on 1 variable.

4 Answers4