php loop through MySQL records - make decision based on row value

Question

I am using this code to loop through MySQL rows:

$sql = "SELECT * FROM vwPublicServices2 ORDER BY Service_Date__c, 
Service_Time__c";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table><tr><th>Service Date</th><th>Service Time</th>
    <th>Church</th><th>Service</th><th>Service Leader</th></tr>";

    while($row = $result->fetch_assoc()) {    

       // insert header stuff on change of $date1
       if ($row["ServiceDate"] <> $date1) { 

          echo $row["ServiceDate"]. "(".$date1.")"."<br/>";

          echo "<tr><td>" . $row["ServiceDate"]. "</td><td>" . 
          $row["Service_Time__c"]. "</td><td>" . $row["Location__c"]. "</td>
          <td>" . $row["PublicName"]. "</td><td>" . $row["FullName"]."</td>
          </tr>";

          // set $date1 = row ServiceDate
          $date1 = $row["ServiceDate"];  

        } else {

          //  echo row data to follow on previous - i.e date has not 
          changed
          echo "<tr><td>" . 
          $row["ServiceDate"]. "</td><td>" . 
          $row["Service_Time__c"]. "</td><td>" .
          $row["Location__c"]. "</td><td>" .
          $row["PublicName"]. "</td><td>" . 
          $row["FullName"]."</td></tr>";

        }

     }
     echo "</table>";
  } else {
     echo "0 results";
}

$conn->close();

Every time row["ServiceDate"] changes I want to insert some header data. Here are some of the results:

There are two things I cannot understand - why is the date check 'one date out' i.e in the second line why is 14/05/2017 compared to 13/05/2017 ?
Also, why does the second echo statement not appear ?
I think I am missing some fundamental point with the way the while loop works ! Any help here much appreciated. Thanks.

Here is an illustration of the header insertion on date change using similar data to above. (This web page pulls data from Salesforce via their API and uses a similar date check to the one in the MySQL code above - but it loops through the data with a For..Each loop )

Sorry, the declaration for $date1 is earlier in the code, before database credentials which I omitted. $date1 = "01/01/1900"; — peter300
– peter300, Commented May 12, 2017 at 8:55
Your output is correct, based on the code you wrote. Your date check is visible only when the date changes. I want to be sure I understand exactly what you want to do. You say you want to insert some header data, but you add the same line to the table in both cases. Could you please add some sample data and the desired output? — moni_dragu
– moni_dragu, Commented May 12, 2017 at 9:13
Thanks moni_dragu. Based on the data above I want the boxed header ( Service Date, Church Time etc ) then the data line 13/05/2017. The code should store 13/05/2017. When it reads the next line it sees 14/07/2017 so it outputs the date 14/07/2017 and the boxed header and then the first 14/07 line 09:00 Bickington. It stores 14/05/2017 then reads the next record skipping the boxed header. — peter300
– peter300, Commented May 12, 2017 at 9:18

peter300 · Accepted Answer · 2017-05-12 10:08:07Z

Here is the full code incorporating moni_dragu's code:

<style>
table, th, td {
border: 1px solid black;
}
</style>


<?php
$servername = "****";
$username = "****";
$password = "****";
$dbname = "*****";



$date1="01/01/1900";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM vwPublicServices2 ORDER BY Service_Date__c, 
Service_Time__c";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table>";

    while($row = $result->fetch_assoc()) {    

       // insert header stuff on change of $date1
       if ($row["ServiceDate"] <> $date1) { 

          echo "<tr><th>".$row["ServiceDate"]. "(".$date1.")"."</th></tr>";
          echo "<tr><th>Service Date</th><th>Service Time</th>
          <th>Church</th><th>Service</th><th>Service Leader</th></tr>";

          $date1 = $row["ServiceDate"]; 

        } 

        //  echo row data 
        echo "<tr><td>" . 
        $row["ServiceDate"]. "</td><td>" . 
        $row["Service_Time__c"]. "</td><td>" .
        $row["Location__c"]. "</td><td>" .
        $row["PublicName"]. "</td><td>" . 
        $row["FullName"]."</td></tr>";

    }

 echo "</table>";
 } else {
   echo "0 results";
}

$conn->close();

Thanks again for solving the problem and also for sharpening my understanding of while loops with a MySQL recordset.

moni_dragu · Accepted Answer · 2017-05-12 09:37:36Z

0

You need to output your header only when the date changes. Because the data row needs to be displayed all the time (for each each row) it does not need to be in the if condition at all.

Your code should look something like this:

if ($result->num_rows > 0) {
    echo "<table>";

    while($row = $result->fetch_assoc()) {    

       // insert header stuff on change of $date1
       if ($row["ServiceDate"] <> $date1) { 

           echo "<tr><th>".$row["ServiceDate"]. "(".$date1.")"."</th></tr>";
           echo "<tr><th>Service Date</th><th>Service Time</th>
    <th>Church</th><th>Service</th><th>Service Leader</th></tr>";

            $date1 = $row["ServiceDate"]; 

        } 

          //  echo row data 
          echo "<tr><td>" . 
          $row["ServiceDate"]. "</td><td>" . 
          $row["Service_Time__c"]. "</td><td>" .
          $row["Location__c"]. "</td><td>" .
          $row["PublicName"]. "</td><td>" . 
          $row["FullName"]."</td></tr>";

     }
     echo "</table>";
  } else {
     echo "0 results";
}

answered May 12, 2017 at 9:37

moni_dragu

1,15310 silver badges16 bronze badges

2 Comments

peter300 Over a year ago

Thanks mon_dragu does just what I wanted. I had to remove the } just above the echo"<,/table>"; for some reason, which I am just pondering. Thanks also for explaining where I was going wrong. I'll post the full code below your answer in case it helps someone. Thanks again

moni_dragu Over a year ago

Glad I could help. You need one } before echo "</table>" to close the while loop.

Collectives™ on Stack Overflow

php loop through MySQL records - make decision based on row value

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related