Retrieving specific data from sql query

Question

Let's say I have the following query, wich gives me the amount of coupons downloaded and redeemed, grouped by date: [EDIT](I can't modify this query)[/EDIT]

$sql = "SELECT DATE(datetime) ,
                           SUM(CASE WHEN datetime!=''  THEN 1 ELSE 0 END) As downloaded ,
                           SUM(CASE WHEN used = 1 AND DATE(datetime) != '' THEN 1 ELSE 0 END) AS redeemed

                     FROM Promotion_Redeem
                     WHERE datetime BETWEEN '2013-10-01' AND '2013-10-04'
                     GROUP BY DATE(datetime)";

How can I get the sum of downloaded and the sum of redeemed? this should be within $sql, somewhere... below is the result for this query:

row number 1
array (size=3)
0 => string '2013-10-01' (length=10)
1 => string '126' (length=3)
2 => string '11' (length=2)
row number 2
array (size=3)
0 => string '2013-10-02' (length=10)
1 => string '106' (length=3)
2 => string '5' (length=1)
row number 3
array (size=3)
0 => string '2013-10-03' (length=10)
1 => string '228' (length=3)
2 => string '12' (length=2)
row number 4
array (size=3)
0 => string '2013-10-04' (length=10)
1 => string '149' (length=3)
2 => string '9' (length=1)

[EDIT]bove you can see I get 4 rows, each one whith an array... I want, for instance, the sum of the third field from each of these arrays... In my example, this would equals to 37 (that means, 37 coupons redeemed)[/EDIT] I got this data structure after using this:

while ($row = mysql_fetch_row($result)) {
            echo "row number ".++$i;
            var_dump($row);

        }

Thanks in advance

There is no more support for mysql_* functions, they are officially deprecated, no longer maintained and will be removed in the future. You should update your code with PDO or MySQLi to ensure the functionality of your project in the future. — Naftali
– Naftali, Commented Oct 8, 2013 at 18:39
Not sure what you need... Sum of all received data? Get you your results if you delete GROUP BY section? — vp_arth
– vp_arth, Commented Oct 8, 2013 at 18:45
@vp_arth in my question you can see I get 4 rows, each one whith an array... I want, for instance, the sum of the third field from each of these arrays... In my example, this would equals to 37 (that means, 37 coupons redeemed) — Lucas Jota
– Lucas Jota, Commented Oct 8, 2013 at 18:50
when you built an associative array using while loop , then why don't just add the last element of each array using foreach loop — user1899563
– user1899563, Commented Oct 8, 2013 at 19:00
You have $row in your loop iterations... make a accumulator variables and increment it by $row[2] ($row[1] for other) in the loop... — vp_arth
– vp_arth, Commented Oct 8, 2013 at 19:06

geomagas · Accepted Answer · 2013-10-08 19:10:32Z

1

Modify your php loop like so:

$downloaded=0;
$redeemed=0;
while ($row = mysql_fetch_row($result)) {
        echo "row number ".++$i;
        $downloaded+=$row[1];
        $redeemed+=$row[2];
        var_dump($row);
    }
echo "Downloaded: $downloaded<br>";    
echo "Redeemed: $redeemed<br>";

edited Oct 8, 2013 at 19:10

answered Oct 8, 2013 at 19:00

geomagas

3,2801 gold badge19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lucas Jota Over a year ago

I need to group them by date, as I'm using it to plot a LineGraph with google charts api... What I'm trying to do is to avoid a second query just to get this sum

Lucas Jota Over a year ago

I've used your solution (@bhawin commented the same thing), but now when I want to use $result again, later in code, it's hold a null value... perhaps I'll need to use mysql_data_seek() to handle that... thanks!

Collectives™ on Stack Overflow

Retrieving specific data from sql query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related