PDO db inner join

Discussion in 'Customization & add-ons' started by raymonda_komar, Jun 6, 2017.

  1. raymonda_komar

    raymonda_komar New Member

    Joined:
    Aug 22, 2006
    Messages:
    9
    What I am trying to do is to list the members city and state if the status = 1 and the state = CA in the amember_members table and if the product_id = 7 and the expire_date is later than today in the member_payments table.

    Any help is greatly appreciated.

    Thank you for the review.

    <?php
    $query = "SELECT DISTINCT city FROM amember_members WHERE status = '1' AND state = 'CA' ORDER BY city
    INNER JOIN amember_payments ON amember_members.member_id WHERE amember_members.status = '1' AND amember_members.state = 'CA' ";
    $stmt = $handler->prepare($query);
    $stmt->execute();
    $result = $stmt->fetchAll();
    foreach ( $result as $row ) {
    ?>
  2. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    1,689
    Hello,

    Try to use this query:
    Code:
    SELECT DISTINCT city FROM amember_members m
        INNER JOIN amember_payments p ON m.member_id = p.member_id
        WHERE m.status = '1'
            AND m.state = 'CA'
            AND p.product_id = 7
            AND p.expire_date > NOW()
        ORDER BY city;
  3. raymonda_komar

    raymonda_komar New Member

    Joined:
    Aug 22, 2006
    Messages:
    9
    As usual aMember does not disappoint. Thank you again.

    If I may how can I add the code to list both product_id 7 and product_id 1 as well?

    Thank you again for your time.

    Ray
  4. caesar

    caesar aMember Pro Developer Staff Member

    Joined:
    Oct 16, 2009
    Messages:
    1,689
    You can use this condition:
    Code:
    p.product_id IN (7, 1)
    Please note aMember version 3 is very old and we strongly recommend to consider migration to latest version (5).

    Best Regards.

Share This Page