CODEWITHSUNDEEP
mysqlsqldatabasepdo
dasmikko
dasmikko

Reputation: 706

Access variable in sql subquery

Hi everyone at stackoverflow!

I'm making a SQL string where I get the newest order (Only one) for each plan.

My SQL string currenly looks like this right now:

$sql = "SELECT
            o.order_id,
            p.plan_id,
            u.user_id,
            m.module_id,
            c.title AS category_title,
            m.title
        FROM 
            (SELECT
                user_id,
                status,
                order_id,
                payment_method_id,
                time_created
            FROM
                orders
            ORDER BY
                order_id DESC) AS o
        JOIN
            plans AS p
        ON
            p.user_id = o.user_id
        JOIN
            modules AS m
        ON
            p.module_id = m.module_id
        JOIN
            categories AS c
        ON
            m.category_id = c.category_id
        JOIN
            users AS u
        ON
            p.user_id = u.user_id
        WHERE
            p.status = :status
        AND
            p.payment = 0
        AND
            o.status = :status
        AND
            p.subscription_id != ''
        GROUP BY
            p.plan_id";

// Here i get the status variable
$stmt->bindParam(':status', $status, PDO::PARAM_INT);

What I'm trying to do is:

$sql = "SELECT
            o.order_id,
            p.plan_id,
            u.user_id,
            m.module_id,
            c.title AS category_title,
            m.title
        FROM 
            (SELECT
                user_id,
                status,
                order_id,
                payment_method_id,
                time_created
            FROM
                orders
            WHERE
                module_id = p.module_id
            AND
                user_id = p.user_id
            LIMIT 1
            ORDER BY
                order_id DESC) AS o
        JOIN
            plans AS p
        ON
            p.user_id = o.user_id
        JOIN
            modules AS m
        ON
            p.module_id = m.module_id
        JOIN
            categories AS c
        ON
            m.category_id = c.category_id
        JOIN
            users AS u
        ON
            p.user_id = u.user_id
        WHERE
            p.status = :status
        AND
            p.payment = 0
        AND
            o.status = :status
        AND
            p.subscription_id != ''
        GROUP BY
            p.plan_id";

I'm a huge trouble with accessing p.module_id and p.user_id variables in the subquery.

My question is: How do i access p.module_id and p.user_id in my subquery? Or is there a better way to do this?

Upvotes: 0

Views: 1389

Answers (1)

Adarsh Rajput
Adarsh Rajput

Reputation: 1276

you cant do that... Try this:

$sql = "SELECT o.order_id, p.plan_id, u.user_id, m.module_id, c.title AS category_title, m.title
    FROM 
        (SELECT user_id, status, order_id, module_id, payment_method_id, time_created
           FROM orders ORDER BY order_id DESC) AS o
    JOIN
        plans AS p ON p.user_id = o.user_id
    JOIN
        modules AS m ON p.module_id = m.module_id AND m.module_id=o.module_id
    JOIN
        categories AS c ON m.category_id = c.category_id
    JOIN
        users AS u ON p.user_id = u.user_id
    WHERE
        p.status = :status AND p.payment = 0 AND o.status = :status AND p.subscription_id != ''
        AND p.user_id is not null AND m.module_id is not null
    GROUP BY p.plan_id";

Try with LIMIT 1 at last of query also....

Here you want to get record from orders table which have user_id of plans and module_id of modules, those you can add in join conditions AND ensure those are not null in WHERE clause of main query. As user_id condition is already there that's why I only added module_id in modules table's join condition AND two where conditions.

Upvotes: 1

Related Questions