Need help in displaying data per date and shift from database

PHP
1

Hi,

I created a report form to display the output per date/shift. But i got problem in displaying output per date/shift

here is my code:


<?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\
";
        }
        }
    else
    {
     header("HTTP/1.0 404 Not Found", true, 404);
    }
}

?>

and here is the get_weekly_data.php where find the code for displaying report:


<?php
ob_start();
include "connection.php";
if($_POST["id"])
{
    $sql = "SELECT r.op_number, p.process_name, r.report_date, s.shift_type
    FROM op_reports AS r
    JOIN shift_list AS s ON (s.shift_id = r.report_shift)
    JOIN process_list AS p ON (p.process_id = r.process_id)
            WHERE WEEK(report_date) + 1 = '" . ($_POST["id"]) . "' ORDER BY r.report_date, s.shift_type DESC";
    $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'];
        $process_name = $row['process_name'];

        echo "<th>$report_date/$report_shift</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_name = $row_comp['process_name'];
        $op_number    = $row_comp['op_number'];
        $process_id   = $row_comp['process_id'];
        $shift_id     = $row_comp['report_shift'];

        echo "<td>$process_name</td>";
        echo "<td style='text-align:center;'>Output</td>";

        $comp = "SELECT DISTINCT o.compound_type, SUM(o.compound_output) AS compound_output, o.process_id, o.shift_id
 FROM op_output AS o WHERE process_id = '$process_id' GROUP BY o.process_id, o.compound_type";

        $c = mysql_query($comp);

        echo "<tr>";
        while($co = mysql_fetch_assoc($c))
        {
            $compound_type   = $co['compound_type'];
            $compound_output = $co['compound_output'];
            $process         = $co['process_id'];
            $shift           = $co['shift_id'];

        if($process_id = $process)
        {
            echo "<td>$compound_type</td>";
            echo "<td>$compound_output</td>";
        }
            echo "</tr>";
        }



    echo "</tr>";
    }
    echo "</table>";




 }
?>

from my code, I got a problem in displaying output per date/shift all the output was only displayed on the first shift.


Any help is highly appreciated.

Thank you so much.
2

Define “got problem”. Does it do something you didnt expect? Didnt do something you were expecting?

3

I got problem in displaying the output based on his date/shift, because on my posted code, it results that the output are all display on the first date/shift which is wrong. I what to display the output on the column that the output came from that date/shift.

Thank you

4

Hi,

I try to fix my problem on displaying output per date/shift.

here is my new code:


<?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
 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'];
         
            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
 FROM op_output AS o WHERE process_id = '$process_id' AND o.compound_type = '$compound_type' GROUP BY o.shift_id, compound_type ORDER BY o.shift_id ASC";
            $res_output = mysql_query($sql_output);
            //in this loop I encountered problem on displaying output based on shift.
            while($row_output = mysql_fetch_assoc($res_output))
            {
            $compound_output = $row_output['compound_output'];
            $shift           = $row_output['shift_id'];

            if($shift == 1)
            {
                echo"<td>$compound_output</td>";
            }
            else
            {
                echo "<td></td>";
            }
            
            if($shift == 2)
            {
                echo"<td>$compound_output</td>";
            }
            else
            {
                echo "<td></td>";
            }
            
            if($shift == 3)
            {
                echo"<td>$compound_output</td>";
            }
            else
            {
                echo "<td></td>";
            }
            
            if($shift == 4)
            {
                echo"<td>$compound_output</td>";
            }
            else
            {
                echo "<td></td>";
            }
            if($shift == 5)
            {
                echo"<td>$compound_output</td>";
            }
            else
            {
                echo "<td></td>";
            }
          
            }
            echo "</tr>";    
        }
             

    }   
    echo "</tr>";
    echo "</table>"; 
    
    
    

 }
?>

Please see attached files for your reference of what output layout I want to happen and the output displayed by this code.

Thank you so much.