Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    Need help in displaying data per date and shift from database

    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 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 Code:
    <?php
    ob_start
    ();
    include 
    "connection.php";
    $q strtolower($_GET["q"]);

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

    //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"true404);   
        }
    }

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

    PHP Code:
    <?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.
    Attached Files Attached Files

  • #2
    Senior Coder whizard's Avatar
    Join Date
    Jan 2005
    Location
    Philadelphia, PA, USA
    Posts
    1,662
    Thanks
    14
    Thanked 76 Times in 76 Posts
    What is the problem that you have?
    PHP Tip: If you want to use short tags (<? or <?=$var) then make sure short_open_tag is set to "1". It really helps.

    Don't forget to save everyone time and mark your thread as Resolved :)

    "Also note that it is your responsibility to die() if necessary."

    DON'T USE THE MYSQL_ EXTENSION

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by whizard View Post
    What is the problem that you have?
    Hi,

    my problem is i don't know how can i display the output of compound based on his date/shift.

    In the above code all the output was displayed on the 1st column shift which is wrong.

    Thank you so much.

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Hi,

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

    here is my new code:

    PHP 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.
    Attached Files Attached Files


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •