PHP PDO Query two tables in same query

Question

i have this query

$sth = $db->prepare(
    'SELECT shout_id FROM shout_info'
    . ' WHERE shout_location = "3"'
    . ' AND (shout_sex = "0" OR shout_sex = ?)'
    . ' AND (shout_age = "0" OR shout_age = ?)'
    . ' AND shout_location_spec = ? AND user_id <> ?'
    . ' ORDER BY date_created DESC LIMIT 15'
);

        $sth->bindValue(1, $user_sex);
        $sth->bindValue(2, $sql_general_age);
        $sth->bindValue(3, $user_workplace);
        $sth->bindValue(4, $user_id);
        $sth->execute();
        $workplace_array = $sth->fetchAll(PDO::FETCH_ASSOC);

Its pretty long, but i was wondering how i could also do a check from a column in another table the check would be AND shout_approved = "1" however shout_approved is in the table shout_status, the primary id for shout_status is shout_id, just like in shout_info, so i am not sure how to check this table using the same id as is being checked in the original query. Sorry i am trying to explain as clearly as possible.

Let me know if you need anything else

MonkeyZeus · Accepted Answer · 2014-02-22 00:15:54Z

1

You will need a LEFT OUTER JOIN for this:

SELECT
    s.shout_id
FROM
    shout_info s
        LEFT OUTER JOIN
            shout_status ss
        ON
            ss.shout_id = s.shout_id
WHERE
    s.shout_location = "3" AND
    (s.shout_sex = "0" OR s.shout_sex = ?) AND
    (s.shout_age = "0" OR s.shout_age = ?) AND
    s.shout_location_spec = ? AND
    s.user_id <> ? AND
    ss.shout_approved = "1"
ORDER BY
    s.date_created DESC
LIMIT
    15

answered Feb 22, 2014 at 0:15

MonkeyZeus

20.8k4 gold badges41 silver badges83 bronze badges

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

Comments

user3223048 · Accepted Answer · 2014-02-22 00:20:58Z

0

This should work:

SELECT 
shout_id 
FROM 
shout_info 
WHERE shout_location = "3" 
AND (shout_sex = "0" OR shout_sex = ?)
AND (shout_age = "0" OR shout_age = ?) 
AND shout_location_spec = ? AND user_id <> ?
ORDER BY date_created DESC LIMIT 15'
Join shout_approved  on shout_approved.shout_id = shout_id 
where shout_approved = "1"

answered Feb 22, 2014 at 0:20

user3223048

1637 bronze badges

1 Comment

MonkeyZeus Over a year ago

Although your query might work, you should always use explicit JOINs otherwise the DBMS has to potentially do A LOT more legwork just to figure out the type of JOIN you are implying and can easily increase query times exponentially.

Collectives™ on Stack Overflow

PHP PDO Query two tables in same query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related