Ask a Question Register Login

php - Nestled While loops?

727

I have a bit of code:

while($row = mysqli_fetch_array($Sat)){
    echo "['".$row['Month']."',".$row['Store A'].",".$row['Store B'].",".$row['Store C']."],";

Which works fine for cases when a user has Store A, B & C - however, I want to expand this to people who will have a varying amount of stores.

I know the array $Sat has headers, so, is it possible to count the headers and whilst they don't equal Month (Month needs to go first) echo out the header name so I feed the associated rows into the format I need for my next step.

The final output should look like['Month',Store A,Store B,Store C] (if the user had the three stores, or maybe['Month',Store A,Store C] if they had just A & C etc..

I've been trying to substitute the code with a variable that builds it but that doesn't seem to work.

I'm not very well experienced in PHP (or coding really) and just trying to adapt something to work internally at work a bit better and save my sanity = )

So, if my database was:

+-------+---------+---------+---------+
| Month | Store A | Store B | Store C |
+-------+---------+---------+---------+
| May-19|      0.0|      0.0|      0.0|
| Jun-19|     12.5|      8.3|      0.0|
| Jul-19|     10.5|     14.3|      0.0|
+-------+---------+---------+---------+

The required output would be:['May-19',0.0,0.0,0.0],['Jun-19',12.5,8.3,0.0],['Jul-19',10.5,14.3,0.0], to feed into Google Charts.

Original SQL query is:

$sql_prep = "SELECT d.store_list AS NUMB, s.name AS NAME FROM data d INNER JOIN store_list s ON d.store_list=s.store_id WHERE d.status != '' AND d.store_list IN (" . $user_stores . ") GROUP BY store_list";
$sql_code = $conn->query($sql_prep);
    while($row = $sql_code->fetch_assoc()) {
    $select = $select . "CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = " . $row['NUMB'] . " THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = " . $row['NUMB'] . " THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  '" . $row['NAME'] . "',";
}
....
$sql = "SELECT
    " . $select . "
    DATE_FORMAT(date_submitted,'%b-%y') AS 'Month'
FROM data 
WHERE store_list IN (" . $user_stores . ")
GROUP BY YEAR(date_submitted), MONTH(date_submitted)
ORDER BY YEAR(date_submitted) ASC, MONTH(date_submitted) ASC";

which would compile to:

SELECT
    CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = 1 THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = 1 THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  'Store A',
    CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = 2 THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = 2 THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  'Store B',
    CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = 3 THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = 3 THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  'Store C',
    DATE_FORMAT(date_submitted,'%b-%y') AS 'Month'
FROM data 
WHERE store_list IN (1,2,3)
GROUP BY YEAR(date_submitted), MONTH(date_submitted)
ORDER BY YEAR(date_submitted) ASC, MONTH(date_submitted) ASC

and output:

+---------+---------+---------+-------+
| Store A | Store B | Store C | Month |
+---------+---------+---------+-------+
|      0.0|      0.0|      0.0| May-19|
|     12.5|      8.3|      0.0| Jun-19|
|     10.5|     14.3|      0.0| Jul-19|
+---------+---------+---------+-------+

I've found I can edit my original code:

while($row = mysqli_fetch_array($Sat)){
    echo "['".$row['Month']."',".$row['Store A'].",".$row['Store B'].",".$row['Store C']."],";

to

while($row = mysqli_fetch_array($Sat)){
    echo "['".$row['Month']."',".$row[0].",".$row[1].",".$row[2]."],";

So this get's around the naming, I just need to have in there as many rows as I have stores now, without building a giant "IF THEN" query. I still feel this is a nestled while or foreach that would do this?

Thank you

825

Answer

Solution:

Try this code below, i did found some type errors in sql syntax:

    $sql_prep = "SELECT d.store_list AS NUMB, s.name AS NAME FROM data d INNER JOIN store_list s ON d.store_list=s.store_id WHERE d.status != '' AND d.store_list IN (" . $user_stores . ") GROUP BY store_list";
$sql_code = $conn->query($sql_prep);
$select = '';
    while($row = $sql_code->fetch_assoc()) {
        $select .= ", CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = " . $row['NUMB'] . " THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = " . $row['NUMB'] . " THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  '" . $row['NAME'] . "' "; -- right here
    }
.....
$sql = "SELECT DATE_FORMAT(date_submitted,'%b-%y') AS 'Month'
" . $select . "
FROM data 
WHERE store_list IN (" . $user_stores . ")
GROUP BY YEAR(date_submitted), MONTH(date_submitted)
ORDER BY YEAR(date_submitted) ASC, MONTH(date_submitted) ASC";

Try this code, it suppose to produce this:

+-------+---------+---------+---------+
| Month | Store A | Store B | Store C |
+-------+---------+---------+---------+
| May-19|      0.0|      0.0|      0.0|
| Jun-19|     12.5|      8.3|      0.0|
| Jul-19|     10.5|     14.3|      0.0|
+-------+---------+---------+---------+

with arrays:

['May-19',0.0,0.0,0.0],['Jun-19',12.5,8.3,0.0],['Jul-19',10.5,14.3,0.0]

to echo full array you need this:

$result = [];
$index = 1;
while($row = mysqli_fetch_array($Sat)){
    //we know for sure that index 0 has value Month, then..
    $temp = [$row['Month']];
    $number = count($row) - 1;
    //loop thru rest of rows start from index 1
    for($i = 0; $i < $number; $i++){
        //push rows in to temporary array
        array_push($temp, $row[$i]);
    }
    //merge result in main array
    $result[$index] = $temp;
    $index++;
}
var_dump($result);

That's what i get:

array (size=5)
  0 => 
    array (size=5)
      0 => string 'moth1' (length=5)
      1 => float 10
      2 => float 20
      3 => float 30.6
      4 => float 45.2
  1 => 
    array (size=6)
      0 => string 'moth2' (length=5)
      1 => float 10
      2 => float 20
      3 => float 30.6
      4 => float 45.2
      5 => float 45.8
  2 => 
    array (size=5)
      0 => string 'moth3' (length=5)
      1 => float 10
      2 => float 20
      3 => float 30.6
      4 => float 45.2
  3 => 
    array (size=7)
      0 => string 'moth4' (length=5)
      1 => float 10
      2 => float 20
      3 => float 30.6
      4 => float 45.2
      5 => float 56.2
      6 => float 56.3
  4 => 
    array (size=5)
      0 => string 'moth5' (length=5)
      1 => float 10
      2 => float 20
      3 => float 30.6
      4 => float 45.2

tested data:

    $rows = [
       ['Month' => 'moth1', 10.0, 20.0, 30.6, 45.2],
       ['Month' => 'moth2', 10.0, 20.0, 30.6, 45.2, 45.8],
       ['Month' => 'moth3', 10.0, 20.0, 30.6, 45.2],
       ['Month' => 'moth4', 10.0, 20.0, 30.6, 45.2, 56.2, 56.3],
       ['Month' => 'moth5', 10.0, 20.0, 30.6, 45.2],
   ];
652

Answer

Solution:

GOT IT!

Thanks to @Serghei Leonenco for talking this through, I used some of your ideas in building this, but this works exactly as needed for N stores. Cheers, couldn't've done it without you!

<?php
//dynamically build chart queries
$sql_prep = "SELECT d.store_list AS NUMB, s.name AS NAME FROM data d INNER JOIN store_list s ON d.store_list=s.store_id WHERE d.status != '' AND d.store_list IN (" . $user_stores . ") GROUP BY store_list";
$sql_code = $conn->query($sql_prep);
$select = '';
    while($row = $sql_code->fetch_assoc()) {
        $select .= ", CAST(((SUM(CASE WHEN LOWER(`q3`) = 'no' AND store_list = " . $row['NUMB'] . " THEN 1 ELSE 0 END)/SUM(CASE WHEN store_list = " . $row['NUMB'] . " THEN 1 ELSE 0 END))*100) as decimal (10,1)) AS  '" . $row['NAME'] . "' ";
        $cstores = $cstores . "'" . $row['NAME'] . "', " ;

    }
?>

<!--https://google-developers.appspot.com/chart/interactive/docs/gallery/linechart-->
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
  google.charts.load('current', {'packages':['corechart']});
  google.charts.setOnLoadCallback(drawChart1);

  function drawChart1() {
    var data = google.visualization.arrayToDataTable([
    ['Month', <?php echo $cstores ?>],
    <?php 

        $sql = "SELECT
                QUOTE(DATE_FORMAT(date_submitted,'%b-%y')) AS 'Month'
                ". $select ."
            FROM data 
            WHERE store_list IN (" . $user_stores . ")
            GROUP BY YEAR(date_submitted), MONTH(date_submitted)
            ORDER BY YEAR(date_submitted) ASC, MONTH(date_submitted) ASC";

        $Sat = mysqli_query($conn,$sql);
        foreach( $Sat as $key => $array ) {
            echo("[");
            foreach( $array as $attribute => $value ) {
                echo $value. ",";
            }
            echo("],");
        }
    ?>

People are also looking for solutions to the problem: How to used aweber api to add new subscriber usign php and aweber OAuth 2.0 Examples

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.