(PHP) SQL Statement returns NULL-CodePudding

I'm trying to return values of these tables, but some return NULL.

SQL returns this

PHP Code

$query = $PDO->prepare("SELECT s.user_id, s.order_date, s.total_order_price, s.status_id,
    op.id, op.product_id, op.amount, op.order_id, op.price,
    p.product_name, st.status_name FROM orders s
    LEFT JOIN order_products op ON s.id = op.product_id
    LEFT JOIN products p ON op.product_id = p.product_id
    LEFT JOIN status st ON s.status_id = st.id WHERE user_id = $user_id");

$query->execute();
$row = $query->fetch();

CodePudding user response：

First of all, I recommend you use some ORM, but if you don't want, you should fix your code like this (for prevent SQL injections). Use at least prepared statements (https://www.php.net/manual/en/pdo.prepare.php).

$q = $PDO->prepare("SELECT s.user_id, s.order_date, s.total_order_price, s.status_id,
op.id, op.product_id, op.amount, op.order_id, op.price,
p.product_name, st.status_name FROM orders s
LEFT JOIN order_products op ON s.id = op.product_id
LEFT JOIN products p ON op.product_id = p.product_id
LEFT JOIN status st ON s.status_id = st.id WHERE user_id = :user_id");
//use bind parameters
$q->bindValue(':user_id', $user_id);
$q->execute();

Then you should check how many rows you have, if any. And then get all results with fetchAll

//check count of results
if ($q->rowCount() > 0){
    $result = $q->fetchAll(PDO::FETCH_ASSOC);
    print_r($result);
}