associative array

[Philippe-M]

Hello, I've this request

function getStatsMois($bpcord, $bpaadd, $annee, $mois, $tsicod, $type) {
    $query  = "SELECT SUM(sinvoiced.netpri*sinvoiced.qty) AS netpri, MONTH(sinvoicev.invdat) as mois";
    $query .= " FROM sinvoicev";
    $query .= " INNER JOIN sinvoiced ON sinvoicev.num = sinvoiced.num";
    $query .= " WHERE";
    $query .= "  sinvoicev.invtyp = '".$type."' AND";
    $query .= "  sinvoicev.bpcinv = '".$bpcord."' AND";
    $query .= "  sinvoicev.bpaadd = '".$bpaadd."' AND";
    $query .= "  YEAR(sinvoicev.invdat) = '".$annee."'";
    if(!empty($tsicod)) {
      $query .= " AND sinvoiced.tsicod = '".$tsicod."'";
    }
    if(empty($mois)) {
      $query .= " AND MONTH(sinvoicev.invdat) BETWEEN '01' AND '12'";
      $query .= " GROUP BY MONTH(sinvoicev.invdat)";
    } else {
      $query .= " AND MONTH(sinvoicev.invdat) = '".$mois."'";
    }

    $stat = $this->database->query($query)->fetchAll(PDO::FETCH_ASSOC);

    return $stat;
  }

     $data = $this->getStatsMois($client[0]['bpcnum'], $client[0]['bpaadd'], date('Y')-1, '', '', '2');
      print_r($data);

Result :

Array
(
    [0] => Array
        (
            [netpri] => 68.6400
            [mois] => 1
        )

    [1] => Array
        (
            [netpri] => 455.2200
            [mois] => 2
        )

    [3] => Array
        (
            [netpri] => 455.2200
            [mois] => 4
        )

    [4] => Array
        (
            [netpri] => 242.9500
            [mois] => 10
        )
)

I would need the returned array is this form

Array
(
    [1] => Array
        (
            [netpri] => 68.6400
            [mois] => 1
        )

    [2] => Array
        (
            [netpri] => 455.2200
            [mois] => 2
        )

    [4] => Array
        (
            [netpri] => 455.2200
            [mois] => 4
        )
    [10] => Array
        (
            [netpri] => 242.9500
            [mois] => 10
        )
)

It's possible with Medoo ?

[Philippe-M]

The solution

  public function getStatsMois($bpcord, $bpaadd, $annee, $mois, $tsicod, $type) {
    $query  = "SELECT MONTH(sinvoicev.invdat) as mois, SUM(sinvoiced.netpri*sinvoiced.qty) AS netpri";
    $query .= " FROM sinvoicev";
    $query .= " INNER JOIN sinvoiced ON sinvoicev.num = sinvoiced.num";
    $query .= " WHERE";
    $query .= "  sinvoicev.invtyp = '".$type."' AND";
    $query .= "  sinvoicev.bpcinv = '".$bpcord."' AND";
    $query .= "  sinvoicev.bpaadd = '".$bpaadd."' AND";
    $query .= "  YEAR(sinvoicev.invdat) = '".$annee."'";
    if(!empty($tsicod)) {
      $query .= " AND sinvoiced.tsicod = '".$tsicod."'";
    }
    if(empty($mois)) {
      $query .= " AND MONTH(sinvoicev.invdat) BETWEEN '01' AND '12'";
      $query .= " GROUP BY MONTH(sinvoicev.invdat)";
    } else {
      $query .= " AND MONTH(sinvoicev.invdat) = '".$mois."'";
    }

    ------- > $stat = $this->database->query($query)->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP);

    return $stat;
  }