CODEWITHSUNDEEP
phpmysql
Al Hennessey
Al Hennessey

Reputation: 2445

PHP PDO Query two tables in same query

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

Upvotes: 0

Views: 459

Answers (2)

user3223048
user3223048

Reputation: 163

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"

Upvotes: 0

MonkeyZeus
MonkeyZeus

Reputation: 20737

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

Upvotes: 1

Related Questions