loop with mysql in php

Question

I am still fairly new to mysql. I want to create a graph using data from mysql in php. Everything works so far except for I only get the last row of data. I am selecting sales and margin from a table for the last 15 months.

    $cmd = "
    select format(sum(b.sales_value)/1000,0) as 'sales',
    format(sum(b.margin)/1000,0) as 'margin',
    date_format(b.Date,'%Y-%b') as 'month'
    from cc_sales.groups a, cc_sales.sales b
    where a.cat_name = b.prod_cat
    and (b.date between date_add(last_day(date_add(now(), interval -15 MONTH)),interval 1 DAY) And now()) 
    and (a.module = 'IN')
    and a.cat_group = $group
    group by date_format(b.Date,'%Y-%b')
    order by date_format(b.Date,'%Y-%c')
     ";

    $result = $conn->query($cmd);
    $chart [ 'chart_data' ][ 0 ][ 0 ] = "Item Group";
    $chart [ 'chart_data' ][ 1 ][ 0 ] = "Sales";
    $chart [ 'chart_data' ][ 2 ][ 0 ] = "Margin";

    for ($i = 0; $i <= $result; $i++){
        while($row = $result->fetch_assoc()) {
    $chart [ 'chart_data' ][ 0 ][ $i +1 ] = $row['month'];
    $chart [ 'chart_data' ][ 1 ][ $i +1 ] = $row['sales'];
    $chart [ 'chart_data' ][ 2 ][ $i +1 ] = $row['margin'];
        }}

Can anyone please tell me what I am doing wrong?

Have you run your query in mysql, is that give you more than one row? — Shaunak Shukla
– Shaunak Shukla, Commented Jul 10, 2017 at 8:51

Ivo P · Accepted Answer · 2017-07-10 08:44:59Z

1

you are looping twice:

 $i = 0;
 while($row = $result->fetch_assoc()) {
    $chart [ 'chart_data' ][ 0 ][ $i +1 ] = $row['month'];
    $chart [ 'chart_data' ][ 1 ][ $i +1 ] = $row['sales'];
    $chart [ 'chart_data' ][ 2 ][ $i +1 ] = $row['margin'];
    $i++;    
}

answered Jul 10, 2017 at 8:44

Ivo P

1,7101 gold badge9 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

loop with mysql in php

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related