Jump to content

php array sum pdo


newstudents

Recommended Posts

I'm trying to get total price from cart table. The product_price in other table which is product. I'm only getting the latest price not the total price. Thanks

function total_price () {
    $total = 0;
    global $db;
    $ip = getIp();

    $sql = $db->query("SELECT * from cart WHERE ip_add='$ip'");
    $row = $query->fetch(PDO::FETCH_ASSOC);
    if ($row) {
       
    }    

    $no=$sql->rowCount();   // number of rows affected by the last SQL statement  
    if ($no == 0) {
        echo "";                
    } else {
        foreach($sql as $row)
            $product_id = $row["p_id"];
            $sql = $db->query("SELECT product_price from product WHERE product_id='$product_id'");
            $no=$sql->rowCount();   // number of rows affected by the last SQL statement  
            if ($no == 0) {
                echo "";
            } else {
                foreach($sql as $row)
                $product_price = array($row["product_price"]);

                $values = array_sum($product_price );
                $total += $values;
            }
        }
        echo "RM" . $total;
    }
}

I'm trying to get total price from cart table. The product_price in other table which is product. I'm only getting the latest price not the total price. Thanks

Edited by newstudents
Link to comment
Share on other sites

Your issue is that you keep overwriting the array here:

$product_price = array($row["product_price"]);

You should be adding the value to the array:

$product_price = array();
foreach($sql as $row) {
  $product_price[] = $row["product_price"];
}

But you can get the total straight from SQL:

$get_sum = $db->prepare('SELECT SUM(product_price) AS total FROM product WHERE product_id = ?');
$get_sum->execute(array($product_id));

if($data = $get_sum->fetch(PDO::FETCH_ASSOC)) {
  $total = $data['total'];
}

Your code is vulnerable to hacking, you should research prepared statements.

Link to comment
Share on other sites

I would assume product_id is unique, so there's only going to be one record in the database with that ID. That means that the sum of all the query results is only going to be the price of that product.

 

I don't know the structure of your database and I don't know what sum you're trying to get, so I can't help any further until you can clarify.

Link to comment
Share on other sites

ya...product_id is unique. currently i have develop e-commerce website for my uni project. this code are for total price in cart part. in cart table there is 3 column which is p _id, ip_add and qty. in table product there is 6 column and on of that are product_price. so i want to get sum for total price for the item that user want to buy. the total price should get based on the total product that user add to cart. so the price should be get by product_id

Edited by newstudents
Link to comment
Share on other sites

There are several issues with your code. You're overwriting the variable $sql and $row inside the loop that's using those variables for iteration, you're also overwriting the value of $total on each iteration.

 

Your code looks much more complicated than it needs to be. You should read the SQL tutorial. Assuming you don't have a "quantity" field, the function can be as simple as one SQL query with a JOIN and using the SUM() function.

function total_price() {
  global $db;
  $ip = getIp();

  $query = $db->prepare('
    SELECT SUM(p.product_price) AS total FROM cart AS c
    JOIN product AS p ON p.product_id = c.p_id
    WHERE c.ip_add = :ip
  ');
  $query->bindParam(':ip', $ip);
  $query->execute();

  $row = $query->fetch(PDO::FETCH_ASSOC);
  if($row) {
    $total = (float) $row['total'];
  } else {
    $total = 0;
  }

  return $total;
}

In the case that there's a quantity field, just change the SUM() function to multiply price by quantity:

SELECT SUM(p.product_price * c.quantity) AS total FROM cart AS c
JOIN product AS p ON p.product_id = c.p_id
WHERE c.ip_add = :ip
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...