I have the following problem. I have a pdo statement with bunch of left joins

$statement = $this->connection->prepare(
        "SELECT ROUND(SUM(product.price * cartitem.quantity)) as totalprice, 
                product.productid, product.imagepath, product.name, 
                product.price, cartitem.quantity 
        FROM cart as cart 
            LEFT JOIN cart_item as cartitem ON cartitem.cart_id = cart.cart_id 
            LEFT JOIN product AS product ON cartitem.product_id=product.productid 
        WHERE customer_id=:customerId 
        AND is_ordered = 0 
        GROUP BY product.productid;");

Since it's left join, it is going to return false when there is nothing to output. But if the Cart ID exist, it is going to return me only the cart ID, and the rest columns are going to be NULL

Like this (cartid is returned, the other columns remains NULL because of left join)

array(1) { 
    [0]=> array(14) { 
        ["cart_id"]=> int(78) 
        [0]=> int(78) 
        ["totalprice"]=> NULL 
        [1]=> NULL 
        ["productid"]=> NULL 
        [2]=> NULL 
        ["imagepath"]=> NULL 
        [3]=> NULL 
        ["name"]=> NULL 
        [4]=> NULL 
        ["price"]=> NULL 
        [5]=> NULL 
        ["quantity"]=> NULL 
        [6]=> NULL 
    } 
}

What I want to archive, is when none of the results are NULL and its not false, to run different tasks. As soon as one of the values are null, return false.

Full Code:

/**
 * Get Products from specific Cart
 *
 * @param $customerId
 * @return bool|CartModel
 */
public function getCartProducts($customerId): bool|CartModel
{
    $statement = $this->connection->prepare("SELECT cart.cart_id, ROUND(SUM(product.price * cartitem.quantity)) as totalprice, product.productid, product.imagepath, product.name, product.price, cartitem.quantity FROM cart as cart LEFT JOIN cart_item as cartitem ON cartitem.cart_id = cart.cart_id LEFT JOIN product AS product ON cartitem.product_id=product.productid WHERE customer_id=:customerId AND is_ordered = 0 GROUP BY product.productid;");
    $statement->execute(['customerId' => $customerId]);
    $rows = $statement->fetchAll();
    var_dump($rows);
    if (!empty($rows)) { //IF ITS NULL OR FALSE, SKIP THIS CODE PART, AND RETURN FALSE AT THE END
        $cartModel = new CartModel();
        $totalprice=[];
        //Data Mapper
        foreach ($rows as $row) {
            $cartItemModel = new CartItemModel();
            $productModel = new ProductModel();
            $productModel->setName($row['name']);
            $productModel->setImagepath($row['imagepath']);
            $productModel->setProductid($row['productid']);
            $productModel->setPrice($row['price']);
            $cartItemModel->setProduct($productModel);
            $cartItemModel->setQuantity($row['quantity']);
            $totalprice[]=$row['totalprice'];
            $cartModel->setCartItem($cartItemModel);
        }
        $cartModel->setTotalprice(array_sum($totalprice));
        return $cartModel;
    }
    return false;
}

What I want to make with this? I want to check if the cart exist, if it does, add new products to cart. If not, than make new cart

CodePudding user response：

You will have to somehow check all the columns values, an isset() is a simple way to do that

An isset() will check multiple values and AND the results.

I split it into 2 lines only to make it easier to read and maintain.

$allSet = isset($rows['totalprice'],$rows['totalprice'],
                $rows['imagepath'],$rows['name'],
                $rows['price'],$rows['quantity']
            );
if (! $allSet) {

NOTE, if you use

$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

You will only get the Assoc array and not the Numeric array duplicating everything.

CodePudding user response：

You could swap the condition like below

if (empty($rows)) {
    return false;
 }
$cartModel = new CartModel();
$totalprice=[];
//Data Mapper
foreach ($rows as $row) {
    $cartItemModel = new CartItemModel();
    $productModel = new ProductModel();
    $productModel->setName($row['name']);
    $productModel->setImagepath($row['imagepath']);
    $productModel->setProductid($row['productid']);
    $productModel->setPrice($row['price']);
    $cartItemModel->setProduct($productModel);
    $cartItemModel->setQuantity($row['quantity']);
    $totalprice[]=$row['totalprice'];
    $cartModel->setCartItem($cartItemModel);
}
$cartModel->setTotalprice(array_sum($totalprice));
return $cartModel;

Updated answer :

You could check whether one of $row element is null or not, for example :

if (!empty($rows) && !$rows['name'])