Unable to display report based on quarter

[Sophia_1]

Hi everyone, am trying to display report based on quarter ie Quarter1,Quarter2 etc. However,after keying in record for Quarter2, the report below does not display Quarter2 record but still Quarter1 record. Please advise how to make the report display Quarter2 record if the current month is Jun etc. Thanks a lot.

        <?php
         session_start(); 
            $_SESSION['userid']; // it will print the userid value

        require_once("tcpdf_6_2_11/tcpdf/tcpdf.php");
        // create new PDF document
         $pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, "A4", true, 'UTF-8', false);
        // set document information
        $pdf->SetCreator(PDF_CREATOR);
        $pdf->SetAuthor('SD');
        $pdf->SetTitle('UOP');
        $pdf->SetSubject('PDF');
        $pdf->SetKeywords('PDF'); 
        // set default monospaced font
        $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
        //set margins
        $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
        //set auto page breaks
        $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
        //set image scale factor
        $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
        //set some language-dependent strings
        $pdf->setLanguageArray($l);
        // ---------------------------------------------------------
        // set default font subsetting mode
        $pdf->setFontSubsetting(true);
        // Set font
        // dejavusans is a UTF-8 Unicode font, if you only need to
        // print standard ASCII chars, you can use core fonts like
        // helvetica or times to reduce file size.
        $pdf->SetFont('dejavusans', '', 10, '', true);
        // Add a page
        // This method has several options, check the source code documentation for more information.

        $pdf->AddPage();
        // Set some content to print
        //'<table border="1">';
        $tbl_header = '<table border="1"><thead><tr><th>KRA</th><th>KPI</th><th>Measures</th> <th>Last Reported</th><th>Latest Actual</th></tr></thead>';
        $tbl_footer = '</table>';
        $tbl ='report';
        $con=mysqli_connect("localhost","user","","pq");
        // Check connection
        if (mysqli_connect_errno())
          {
          echo "Failed to connect to MySQL: " . mysqli_connect_error();
          }
        $result = mysqli_query($con,"select * FROM report where userid='".$_SESSION['userid']."'");

        while($row = mysqli_fetch_array($result))
          {
               If($row['Quarter1']==1) {  
                $kra = $row['kra'];
                $kpi = $row['kpi'];
                $measure = $row['measure'];
                $progress1 = $row['progress1'];
                $nprogress1 = $row['nprogress1'];

                $tbl .= '<tr><td>' . $kra . '</td><td>' . $kpi . '</td><td>' . $measure . '</td><td>' . $progress1 . '</td><td>' . $nprogress1 . '</td></tr>';
        }
         Else If($row['Quarter2']==2) {  
                $kra = $row['kra'];
                $kpi = $row['kpi'];
                $measure = $row['measure'];
                $progress1 = $row['progress1'];
                $nprogress2 = $row['nprogress2'];


                $tbl .= '<tr><td>' . $kra . '</td><td>' . $kpi . '</td><td>' . $measure . '</td><td>' . $progress1 . '</td><td>' . $nprogress2 . '</td></tr>';
        }

          }
        // Print text using writeHTMLCell()
        $pdf->writeHTML($tbl_header . $tbl . $tbl_footer, true, false, false, false, '');
        // ---------------------------------------------------------
        // Close and output PDF document
        // This method has several options, check the source code documentation for more information.
        $pdf->Output('', 'I');
        //============================================================+
        // END OF FILE
        //============================================================+
        ?> 

[pritaeas]

Without knowing how your data (query result) looks it's very hard to answer.

[Sophia_1]

Hi, thanks for your reply. Attached please find the report result. The latest actual suppose to display the result for Quarter2 but it still display Quarter1. Appreciate for your advise. Thanks.

[pritaeas]

That shows your report. I'd like to know what your database table and records in it look like.

[Sophia_1]

Hi, below is the query. The query name is report. Please advise. Thanks.

    <h2><b>UOP</b></h2>
    <?php
    $con=mysqli_connect("localhost","user","","pq");
    // Check connection
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    $result = mysqli_query($con,"SELECT * FROM report where userid='".$_SESSION['userid']."'");
    echo "<table border='1'>
    <tr>
    <th >KRA</th>
    <th >KPI</th>
    <th >Measures</th>
    <th >Last Reported</th>
    <th >Latest Actual</th>
    </tr>";

    while($row = mysqli_fetch_array($result)) {
      echo "<tr>";
      If($row['Quarter1']==1) {  
      echo "<td >" . $row['kra'] . "</td>";
      echo "<td >" . $row['kpi'] . "</td>";
      echo "<td >" . $row['measure'] . "</td>";
      echo "<td >" . $row['progress1'] . "</td>";
      echo "<td >" . $row['progress1'] . "</td>";
     }
    elseif ($row['Quarter2']==2) {
     echo "<td>" . $row['kra'] . "</td>";
      echo "<td >" . $row['kpi'] . "</td>";
      echo "<td >" . $row['measure'] . "</td>";
      echo "<td >" . $row['progress1'] . "</td>";
      echo "<td >" . $row['progress2'] . "</td>";
      }
    elseif ($row['Quarter3']==3) {
     echo "<td >" . $row['kra'] . "</td>";
      echo "<td >" . $row['kpi'] . "</td>";
      echo "<td >" . $row['measure'] . "</td>";
      echo "<td >" . $row['progress2'] . "</td>";
      echo "<td >" . $row['progress3'] . "</td>";
     }
     else {
     echo "<td>" . $row['kra'] . "</td>";
      echo "<td >" . $row['kpi'] . "</td>";
      echo "<td >" . $row['measure'] . "</td>";
      echo "<td >" . $row['progress3'] . "</td>";
      echo "<td >" . $row['progress4'] . "</td>";
      }
       echo "</tr>";
    } 
    echo "</table>";
    mysqli_close($con);
    ?>

[cereal]

I think Pritaeas was asking for the database table structure, so open a MySQL client and run:

show create table `report`;

And return the result here, then run an example query to show us how your data was stored. The best would if you setup an SQLFiddle with some data:

• http://sqlfiddle.com/

In which you include the create table statement, the insert statements, and possibly a query.

[Johnny1]

The code you posted shows:

if( $row['Quarter1']==1 ){...}
else if($row['Quarter2']==2){...}
else if($row['Quarter3']==3){...}

Since $row contains "keys" corresponding to the DB table, the above code suggests that you have:

Table Name: report
Fields: userid, Quarter1, Quarter2, Quarter3,kra,kpi,...

The problem is that when you execute SELECT * FROM report ..., then all the quarter columns will be retrieved. So if you get result similar to:

userid,Quarter1,Quarter2,Quarter3,kra,kpi,...
alpha, 1, NULL, 3,'kra val 1','kpi val 1'
alpha, 1, 2, 3,'kra val 2','kpi val 2'
alpha, 1, 2, NULL,'kra val 3','kpi val 3'

then you will see only records for Quarter1 because it is the first "if" clause. Since you are using "if-elseif" clauses, them the subsequent tests don't get executed because the first "if" evaluates to true. With the given table structure, if you need to view the data for all the quarters, you need to use just "if" clauses (not "else if").

On another note, if a given record can have at most one quarter, a better approach would be to have just a single Quarter field/column instead of (Quarter1,Quarter2,etc) and then value if Quarter would be just the quarter number. So the table values above would just:

userid, Quarter, kra, kpi...
alpha, 1, 'kra val 1', 'kpi val 1'
alpha, 2, 'kra val 2', 'kpi val 2'
alpha, 1, 'kra val 3', 'kpi val 3'

Since there is just a single Quarter field, now you can use the "if-elseif" clause. Furthermore, with this approach, you can filter the data upon querying:
"SELECT * FROM report WHERE Quarter=" . intval($_POST['quarter']) . " and userid='" . $_SESSION['userid'] . "'";

[Sophia_1]

Hi everyone, thanks a lot for your advise.Really appreciate it very much. Another thing, i wanted to display the report ie the KRA record will be displayed on 1st row, KPI record will be displayed on 2nd row and Measures,Last Reported,Latest Actual records will be displayed on 3rd row.Currently the KRA record is displayed on the 1st row but only on the Measure column. How to make the KRA record displayed on the centre of the 1st row? Thanks a lot for your advise.

    <?php
     session_start(); 
        $_SESSION['userid']; // it will print the userid value

    //require_once('../tcpdf/config/lang/eng.php');
    require_once("tcpdf_6_2_11/tcpdf/tcpdf.php");
    // create new PDF document
     $pdf = new TCPDF("LANDSCAPE", PDF_UNIT, "A4", true, 'UTF-8', false);
    // set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor('SD');
    $pdf->SetTitle('UOP');
    $pdf->SetSubject('PDF');
    $pdf->SetKeywords('PDF'); 
    // set default monospaced font
    $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);
    //set margins
    $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
    //set auto page breaks
    $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
    //set image scale factor
    $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
    //set some language-dependent strings
    $pdf->setLanguageArray($l);
    // ---------------------------------------------------------
    // set default font subsetting mode
    $pdf->setFontSubsetting(true);
    // Set font
    // dejavusans is a UTF-8 Unicode font, if you only need to
    // print standard ASCII chars, you can use core fonts like
    // helvetica or times to reduce file size.
    $pdf->SetFont('dejavusans', '', 10, '', true);
    // Add a page
    // This method has several options, check the source code documentation for more information.

    $pdf->AddPage();
    // Set some content to print
    //'<table border="1">';
    $tbl_header = '<table border="1"><thead><tr><th>Measures</th>
    <th>Last Reported</th><th>Latest Actual</th></tr></thead>';
    $tbl_footer = '</table>';
    $tbl ='report';
    $con=mysqli_connect("localhost","user","","pq");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
    if ($_POST) {   
    $result = mysqli_query($con,"select * FROM report where userid='".$_SESSION['userid']."'");

    while($row = mysqli_fetch_array($result))
      {
          If($_POST['quarterly']==1 && $_POST['year'] == $row['year']) {  
            $kra = $row['kra'];
            $kpi = $row['kpi'];
            $measure = $row['measure'];
            $progress1 = $row['progress1'];
            $progress1 = $row['progress1'];

            $tbl .= '<tr><td>KRA' . $kra . '</td><td></td><td></td><td></td></tr><tr><td>KPI' . $kpi . '</td><td></td><td></td><td></td></tr><tr><td>' . $measure . '</td><td>' . $progress1 . '</td><td>' . $progress1 . '</td></tr>';
    }
      If($_POST['quarterly']==2 && $_POST['year'] == $row['year']) { 
            $kra = $row['kra'];
            $kpi = $row['kpi'];
            $measure = $row['measure'];
            $progress1 = $row['progress1'];
            $progress2 = $row['progress2'];

            $tbl .= '<tr><td>KRA' . $kra . '</td><td></td><td></td><td></td></tr><tr><td>KPI' . $kpi . '</td><td></td><td></td><td></td></tr><tr><td>' . $measure . '</td><td>' . $progress1 . '</td><td>' . $progress2 . '</td></tr>';
    }

    }
     }

    // Print text using writeHTMLCell()
    $pdf->writeHTML($tbl_header . $tbl . $tbl_footer, true, false, false, false, '');
    // ---------------------------------------------------------
    // Close and output PDF document
    // This method has several options, check the source code documentation for more information.
    $pdf->Output('', 'I');
    //============================================================+
    // END OF FILE
    //============================================================+
    ?>