How do I make a mySQL statement with more than one SUM()?

Question

My site adds product identifiers to a session variable (an array called "itemarray") when a button is clicked on the product page. When the user opens their shopping cart, the following code executes:

<?php
    $cart = implode(',', $_SESSION['itemarray']);
    /*Create connection to DB here. DB connection is called $connection.*/
    $result = $connection->query("SELECT SUM(Price) AS Total_Price, SUM(Tax) AS Total_Tax, SUM(Shipping) AS Total_Shipping FROM STOCK_LIST WHERE Product_ID IN ($cart)");
    while ($rows = $result->fetch_assoc()) {
        /*Output query result into table*/
    }
    /*Terminate the connection to the database*/
    $connection->close();
?>

The first line of code goes through the itemarray and appends each element into a comma-delimited list (called $cart). A connection is made to the database (the code for which has been removed because it has already been tested), and a query is submitted which asks for the total price, tax, and shipping costs of the items in $cart. A while loop is then run which echos the contents of the $results variable into a table (the code for which has been removed because it has already been tested).

The issue is that the calculated totals aren't being output. The implode function works as expected, the connection is being made, and the table is being created (just without the output from the query), so I can only assume that the query is the problem.

Check out my solution below. Basically you need to update your implode code to wrap data in single quotes. — Fakhruddin Ujjainwala, Feb 28 '16 at 18:14

score 1 · Answer 1 · answered Feb 28 '16 at 18:48

I changed the implode command to:

$cart = implode('\', \'', $_SESSION['itemarray']);

This puts the list into the following format:

1', '2

Where 1 and 2 are both product IDs. In order to add the single quotes to beginning and end of the string, I then added:

$cart = "'" . $cart . "'";

Adding this second line of code changes the contents of $cart into this:

'1'. '2'

This can now be used in my SQL statement.

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

I would strongly recommend you using binding parameters as it will save your site from SQL injection attack (which I assume would be crucial for your shop site). Try to adopt the solution from this question.

edited May 23 '17 at 11:50

Community

1
1

answered Feb 28 '16 at 18:25

max

2,757
22
19

An excellent point more suited to a comment than an answer. Could probably even flag this question as a dupe of your link. – Feb 28 '16 at 19:17
1

@Terminus if it has some code, it would be the perfect answer but I was lazy to write it. Also, however, the described issue could be solved with the suggested link, the question is actually about something different. If I judged all questions with such attitude I would flag almost half of them :) – max Feb 28 '16 at 20:15

How do I make a mySQL statement with more than one SUM()?

2 Answers2