PHP check if value present in while loop and enter value then break

Question

I am using PHPspreadsheet and all works well. However I have an issue when generating a sign-in sheet when no one is working on a particular day THis then puts all entries on my excel template out of sync. Question: How do i check the first value(StaffName) in the loop - If empty then substitute with 'No one Working' and then break the loop and allow it to move on to the next loop Tuesday. Thanks The code is as follows:

$sql="SELECT StaffName, MonStart, MonFInish, MonA FROM print_signin_mon 
where AreaID= '$AreaID'";
$rsSql=db_query($sql,$conn);

$baseRow = 6;
$r=0;

while ($data2 = db_fetch_array($rsSql)){
$row = $baseRow + $r;
$spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1);

$spreadsheet->getActiveSheet()->setCellValue('A' . $row, $r + 1)
    ->setCellValue('B' . $row, $data2['StaffName'])
    ->setCellValue('E' . $row, $data2['MonStart']) 
    ->setCellValue('G' . $row, $data2['MonFInish'])
    ->setCellValue('M' . $row, $data2['MonA']);

    $r= $r+1;
}
$spreadsheet->getActiveSheet()->removeRow($baseRow - 1, 1);

score 0 · Answer 1 · answered Feb 18 '19 at 21:39

0

Don't know if I understood you correctly, but I'll give it a try.

If StaffName is empty, set a different cell value and skip to next iteration:

$sql="SELECT StaffName, MonStart, MonFInish, MonA FROM print_signin_mon where AreaID= '$AreaID'";
$rsSql=db_query($sql,$conn);

$baseRow = 6;
$r=0;

while ($data2 = db_fetch_array($rsSql)){
    $row = $baseRow + $r;
    $spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1);

    if (empty($data2['StaffName'])) {
        $spreadsheet->getActiveSheet()->setCellValue('A' . $row, $r + 1)
            ->setCellValue('B' . $row, 'No one Working');

        $r++;
        continue;
    }

    $spreadsheet->getActiveSheet()->setCellValue('A' . $row, $r + 1)
        ->setCellValue('B' . $row, $data2['StaffName'])
        ->setCellValue('E' . $row, $data2['MonStart']) 
        ->setCellValue('G' . $row, $data2['MonFInish'])
        ->setCellValue('M' . $row, $data2['MonA']);

    $r++;
}

$spreadsheet->getActiveSheet()->removeRow($baseRow - 1, 1);

answered Feb 18 '19 at 21:39

Andrew Larsen

1,257
10
21

Thanks for response - unfortunately it does not give the desired result - Maybe i am asking the wrong question - I need it to check if there is a record and if not enter a dummy ("No one Working") STOP and move to next - should I be checking If record exists ? and if so any insight ? Thanks again – waxies Feb 18 '19 at 22:24
@waxies what result did you get when trying the code above? It's checking if StaffName is empty, if it's empty it will write dummy text "No one Working" on a seperate row before it continues to next. – Andrew Larsen Feb 19 '19 at 08:39
It puts the second (Tuesday information) on the first row of the spreadsheet rather than on the row after the Monday row thereby putting the whole format of the spreadsheet out - appears to ignore the IF statement. I am going to look at it again tonight. Thanks – waxies Feb 20 '19 at 17:53
It appears that the function 'while' checks ($data2 = db_fetch_array($rsSql)) and finds it empty and so 'skips' the all the code below until next statement (i.e. Tuesday) hence the reason the additional if is ignored. I tries putting it before the 'while' but no change. Do I need to check the array to ensure it has data before running the 'while'and if Yes any ideas on how to do that. Thanks – waxies Feb 20 '19 at 22:04

score 0 · Answer 2 · answered Feb 21 '19 at 17:38

After a lot of trial and error I managed to get a result. In order to achieve my objective i took the 'IF' out of the loop and added it after. That way if there is no data the following runs and adds a row, however it always adds a row even when there is data. So not doing what i asked i.e. Check data in loop and enter value then more on. But it gives me a satisfactory result.My thanks to Andrew for input.

//Monday
$sql="SELECT StaffName, MonStart, MonFInish, MonA FROM print_signin_mon where AreaID= 
'$AreaID'";
$rsSql=db_query($sql,$conn);

                $baseRow = 6;
                $r=0;

                while ($data2 = db_fetch_array($rsSql)){
                     $row = $baseRow + $r;
                     $spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1);
//


                     $spreadsheet->getActiveSheet()->setCellValue('A' . $row, $r + 1)

                          ->setCellValue('B' . $row, $data2['StaffName'])
                          ->setCellValue('E' . $row, $data2['MonStart']) 
                          ->setCellValue('G' . $row, $data2['MonFInish'])
                          ->setCellValue('M' . $row, $data2['MonA'])
                                 ->setCellValue('N' . $row, '..');

                        $r= $r+1;
                    }
    //          $spreadsheet->getActiveSheet()->removeRow($baseRow - 1, 1);
    //if (empty($data2['StaffName'])) {
    if(empty(array_column($rsSql,'StaffName'))) {
                    $row = $baseRow + $r;
                    $spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1);
                    $spreadsheet->getActiveSheet()->setCellValue('A' . $row, $r + 1)
                    ->setCellValue('B' . $row, '');

    //$r++;
    $spreadsheet->getActiveSheet()->removeRow($baseRow - 1, 1);
    //continue;
}
//END Monday

PHP check if value present in while loop and enter value then break

2 Answers2