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**](https://wiki.php.net/rfc/mysql_deprecation), **no longer maintained** and will be [**removed**](http://php.net/manual/en/function.mysql-connect.php#warning) in the future. You should update your code with [PDO](http://php.net/pdo) or [MySQLi](http://php.net/msqli) to ensure the functionality of your project in the future. — Naftali, Oct 08 '13 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, Oct 08 '13 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, Oct 08 '13 at 18:50
so you have this associated which you got from while loop, now you want to find the sum of third field of each array, am i correct? — , Oct 08 '13 at 18:55
"this should be within $sql". Simple delete grouping from your query. — vp_arth, Oct 08 '13 at 18:58
when you built an associative array using while loop , then why don't just add the last element of each array using foreach loop — , Oct 08 '13 at 19:00
@vp_arth that works, but I need to keep my query this way (I'm using it to plot a linegraph using google chart api) — Lucas Jota, Oct 08 '13 at 19:01
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, Oct 08 '13 at 19:06

geomagas · Accepted Answer · 2013-10-08T19:10:32.683

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 08 '13 at 19:10

answered Oct 08 '13 at 19:00

geomagas

3,230
1
17
27

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 Oct 08 '13 at 19:05
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! – Lucas Jota Oct 09 '13 at 16:55

Retrieving specific data from sql query

1 Answers1