Sunday 8 September 2019

How to match row value as variable for parameter in sibling results?

I have 2 db calls that run a loop to get all results.

First I'm getting the results relative to current year.

Second I'm getting results relative to previous year (-1).

How can align the month in the output of second loop to same month from the first loop?

public function cargovolumes_cy() {
    global $wpdb;

    $cargodb = new $wpdb('root', 'devpw', 'exdb', 'localhost');

    $currentmonthtxt = date('M');

    $currentyear = date('Y');

    $cargovolume_cy = array();

    foreach($cargodb->get_results(
        "
        SELECT    *, SUM(tonneCount)
        FROM      volumes
        WHERE year = $currentyear
        GROUP BY terminal, year, month
        ORDER BY month desc, year desc
        "
    )  as $key => $row) {
        $tonnages = $row->tonneCount;
        $terminal = $row->terminal;
        $year = $row->year;
        $month = $row->month;
        $cargovolume_cy[] = 
        '<h4 class="cv-terminal-title">'.$terminal.' </h4>'. 
        '<div class="cargovolumes_cy">'.
        '<div class="cargovolumes_cy-dates cvrow-'.$month.'-'.$year.'">'.
        '<span class="cy-month"> '.$month.' </span>'. 
        '<span class="cy-year"> '.$year.' </span></div>'. 
        '<div class="cy-year-bar"><div class="cy-tonnage-bar">&nbsp;</div>'.
        '<span class="cy-tonnage" value="'.$tonnages.'"> '.$tonnages.' </span></div>'.
        '</div>';
    };

    return $cargovolume_cy;

}

public function cargovolumes_ly() {
    global $wpdb;

    $cargodb = new $wpdb('root', 'devpw', 'exdb', 'localhost');

    // $stable = $shipdb->get_results("SELECT * FROM volumes", ARRAY_A);

    $currentmonthtxt = date('M');

    $currentyear = date('Y');

    $cargovolume_ly = array();

    foreach($cargodb->get_results(
        "
        SELECT    *, SUM(tonneCount)
        FROM      volumes
        WHERE year = $currentyear -1
        GROUP BY terminal, year, month
        ORDER BY month desc, year desc
        "
    )  as $key => $row) {
        $tonnages = $row->tonneCount;
        $terminal = $row->terminal;
        $year = $row->year;
        $month = $row->month;
        $cargovolume_ly[] = 
        '<div class="cargovolumes_ly">'. 
        '<div class="cargovolumes_ly-dates">'.
        '<span class="ly-month"> '.$month.' </span>'. 
        '<span class="ly-year"> '.$year.' </span></div>'. 
        '<div class="ly-year-bar"><div class="ly-tonnage-bar">&nbsp;</div>'.
        '<span class="ly-tonnage" value="'.$tonnages.'"> '.$tonnages.' </span></div>'.
        '</div>';
    };

    return $cargovolume_ly;

}

The result should output 2 elements with matched months and years. Instead I'm getting the most recent month in the first output, paired with the last month of the year in the second output.

Below is container element:

@for ($i = 0; $i < min(count($cargovolumes_cy), count($cargovolumes_ly)); $i++)
<div class="page_cargovolumes-info_grid-cy_item">
  {!! $cargovolumes_cy[$i] !!}
  {!! $cargovolumes_ly[$i] !!}
</div>
@endfor



from How to match row value as variable for parameter in sibling results?

No comments:

Post a Comment