Need help on displaying data inside table using loop

newphpcoder · July 9, 2013

Hi,

I have form that displaying report per week. First thing to do is choose what week then automatically display the date/shift that week ranges.

here is my weekly_report.php

<?php
    error_reporting(0);
 session_start();
  ob_start();
  date_default_timezone_set("Asia/Singapore");
 
  include('connection.php');  
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<title>Weekly Report</title>
<head>
<link rel="stylesheet" type="text/css" href="op_report.css" />
<script type="text/javascript" src="jquery.js"></script>
<script type='text/javascript' src='jquery.autocomplete.js'></script>
<link rel="stylesheet" type="text/css" href="jquery.autocomplete.css" />

<script type="text/javascript">
//----auto complete week--//

$().ready(function() {
    $("#week_selected").autocomplete("get_week_list.php", {
       width: 115,
        matchContains: true,
        mustMatch: true,
        selectFirst: false
    });
    
    $("#week_selected").result(function(event, data, formatted) {
        $("#week_number").val(data[1]);
    });
    });
    
/*AJAX*/
function AJAX(){
        var xmlHttp;
        try{
            xmlHttp=new XMLHttpRequest(); // Firefox, Opera 8.0+, Safari
            return xmlHttp;
            }
        catch (e){
            try{
                xmlHttp=new ActiveXObject("Msxml2.XMLHTTP"); // Internet Explorer
                return xmlHttp;
                }
            catch (e){
                try{
                    xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
                    return xmlHttp;
                    }
                catch (e){
                    alert("Your browser does not support AJAX!");
                    return false;
                    }
                }
            }
        }   
        
  //-----get weekdata from week---//
function getweekdata()
        {
           //  if (window.event.keyCode==13 || window.event.keyCode==10) {
            divid = "week_data";
            var url = "get_weekly_data.php";
            var str = "id=" + document.getElementById("week_number").value;
            
            var xmlHttp = AJAX();
            xmlHttp.onreadystatechange =  function(){
            if(xmlHttp.readyState > 0 && xmlHttp.readyState < 4){
               // document.getElementById(divid).innerHTML=loadingmessage;
                }
            if (xmlHttp.readyState == 4) {
                if (xmlHttp.status == 200) {
                    var jsonart = xmlHttp.responseText;
                    document.getElementById(divid).innerHTML = jsonart;
                    }
                }
            }
            xmlHttp.open("POST", url, true);
            xmlHttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
            xmlHttp.setRequestHeader("Content-length", str.length);
            xmlHttp.setRequestHeader("Connection", "close");
            xmlHttp.send(str);
          
       // }
        }           
</script>
</head>
<body onload=document.getElementById("week_selected").focus();>
<form name="weekly_report" action="" method="post">
<div id="ddcolortabs">
<ul>
<li> <a href="index.php" title="Operator's Shift Report"><span>Operator's Shift Report</span></a></li>
<li id="current"> <a href="weekly_report.php" title="Reports"><span>Reports</span></a></li>
</ul>
</div>
<br/>
<div>
<table>
<tr>
<td style="border: none;">Type Week:</td>
<td><input type="text" name="week_selected" id="week_selected" value="" size="15" onkeyup="getweekdata();"></td>
</tr>
</table>
</div>

<input type="hidden" name="week_number" id="week_number">

<div id='week_data'>
</div>
</form>
</body>
</html>

//get_week_list.php

<?php
ob_start();
include "connection.php";
$q = strtolower($_GET["q"]);

if ($q == '') {
   header("HTTP/1.0 404 Not Found", true, 404);   
}

//else (!$q) return;
else{
$sql = "select week_id, week_number from week_list where week_number LIKE '$q%'";
$rsd = mysql_query($sql);

$cnt = mysql_num_rows($rsd);

    if($cnt > 0)
    {
        while($rs = mysql_fetch_array($rsd)) {
            $pid = $rs['week_id'];
            $pname = $rs['week_number'];
            echo "$pname|$pid\n";
        }
        }
    else
    {
     header("HTTP/1.0 404 Not Found", true, 404);   
    }
}

?>

and here is my get_weekly_data.php // which display the data for that week.

<?php
ob_start();
include "connection.php";

if($_POST["id"])

{    

   $sql = "select r.report_date, s.shift_type FROM op_reports AS r, shift_list AS s WHERE WEEK(report_date) + 1 = '" . ($_POST["id"]) . "'
   GROUP BY shift_type ORDER BY shift_id ASC";
    $res = mysql_query($sql);
    
    echo "<table>";
    echo "<tr>";
    echo "<th>Comp</th>";

    while($row = mysql_fetch_assoc($res))
    {
        $report_date  = $row['report_date'];
        $report_shift = $row['shift_type'];
                
        echo "<th>$report_date/$report_shift</th>";   

        
    }
    echo "</tr>";
    
     $sql_r = "select r.report_date, s.shift_type FROM op_reports AS r, shift_list AS s WHERE WEEK(report_date) + 1 = '" . ($_POST["id"]) . "'
   GROUP BY shift_type ORDER BY shift_type DESC";     
    $res_r = mysql_query($sql_r);
    
    echo "<tr>";
    echo "<th></th>";
    
    while($r = mysql_fetch_assoc($res_r)){
    
        echo "<th>Output</th>";
        
    }
    echo "</tr>";
    
    
    $sql_comp = "SELECT DISTINCT p.process_name , r.process_id, r.report_shift
    FROM op_reports AS r
    JOIN process_list AS p ON (p.process_id = r.process_id)
            WHERE WEEK(report_date) + 1 = '" . ($_POST["id"]) . "'  GROUP BY process_name ORDER BY p.process_name ASC";     
    $res_comp = mysql_query($sql_comp);
    
    
    echo "<tr>";

     while($row_comp = mysql_fetch_assoc($res_comp))
    {
        $process      = $row_comp['process_name'];
        $process_id   = $row_comp['process_id'];
      
        echo "<td>$process</td>";
 
        $comp = "SELECT DISTINCT o.compound_type, o.process_id, o.shift_date
 FROM op_output AS o WHERE process_id = '$process_id' GROUP BY o.shift_id, compound_type ORDER BY compound_type ASC";

        $c = mysql_query($comp);
        
        echo "<tr>";
        while($co = mysql_fetch_assoc($c))
        {
            $compound_type   = $co['compound_type'];
            $process_i       = $co['process_id'];
            $shift_date      = $co['shift_date'];  
         
            echo "<td>$compound_type</td>";

            $sql_output = "SELECT DISTINCT o.compound_type, SUM(o.compound_output) AS compound_output, o.process_id, o.shift_id, o.shift_date
 FROM op_output AS o WHERE process_id = '$process_id' AND o.compound_type = '$compound_type' GROUP BY o.shift_id, o.shift_date, compound_type ORDER BY o.shift_id ASC";
            $res_output = mysql_query($sql_output);
            
            while($row_output = mysql_fetch_assoc($res_output))
            {
            $compound_output = $row_output['compound_output'];
            $shift           = $row_output['shift_id'];
            $compound        = $row_output['compound_type'];
            $date            = $row_output['shift_date'];
           
            if($shift == 1 && $date == $shift_date)
            {
                echo"<td>$compound_output</td>";
            }
            else
            {
                echo "<td></td>";
            }
            
            if($shift == 2 && $date == $shift_date)
            {
                echo"<td>$compound_output</td>";
            }
            else
            {
                echo "<td></td>";
            }
            
            if($shift == 3 && $date == $shift_date)
            {
                echo"<td>$compound_output</td>";
            }
            else
            {
                echo "<td></td>";
            }
            
            if($shift == 4 && $date == $shift_date)
            {
                echo"<td>$compound_output</td>";
            }
            else
            {
                echo "<td></td>";
            }
            if($shift == 5 && $date == $shift_date)
            {
                echo"<td>$compound_output</td>";
            }
            else
            {
                echo "<td></td>";
            }
               
            }
            echo "</tr>";
            
        }
    }   
    echo "</tr>";
    echo "</table>";
 }
?>

I attached my sample screenshots that display by this code and also a format that I need to display and the database.

My problem now is on displaying of output per date/shift.

It's been todays that I tried to fixed this. I hope somebody can help me.

Thank you so much

sample updated.zip

Edited July 9, 2013 by newphpcoder

Sign In

Need help on displaying data inside table using loop

Recommended Posts

newphpcoder

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information