Got problem in table layout

Get Started
1

Hi,

I got problem in my table layout.

my table data came from database using php while loop and foreach.

Here is my scenario.

I need to display the reject type per date.
like this:

Compound-- space—2013-07-24 2013-07-25
Compound Mixing----SC-SP-SH----PG-PT

2013-07-24 has 3 reject types SC SP SH
2013-07-25 has 2 reject types PG PT

i want to display the reject type on the date where its belong.
but in this code:



<table><tr><thead><th>Compound</th><th>2013-07-24</th><th>2013-07-25</th></thead></tr><tr><td>Compound Mixing</td><td>SC</td><td>SP</td><td>SH</td><td>PG</td><td>PT</td></table>

the output is like this:
as you can see the SC and SP was size like the size of the date. but it supposively 3 reject type within 2013-07-24 and 2 for 2013-07-25

Compound-- space—2013-07-24 2013-07-25
Compound Mixing—SC----------SP-----------SH-PG-PT

and here is the php code:


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

$id = "30";
if($id == '30')
{

   //----code for date only----//
   $sql = "SELECT DISTINCT r.reject_date, r.process_id
    FROM op_reject AS r
    JOIN process_list AS p ON (p.process_id = r.process_id)
    WHERE WEEK(reject_date) + 1 = '$id'
    GROUP BY r.reject_date ORDER BY r.reject_date  ASC";


    $res = mysql_query($sql);

    echo "<table>";
    echo "<tr>";
    echo "<thead>";
    echo "<th>Compound</th>";

    while($row = mysql_fetch_assoc($res))
    {
        $report_date[]  = $row['reject_date'];
        $process_[]   = $row['process_id'];
    }

        foreach($report_date AS $report_date)
        {
            echo "<th>$report_date</th>";
        }
        echo "</thead>";
    echo "</tr>";

        $sql_comp = "SELECT DISTINCT p.process_name , r.process_id, r.reject_date
        FROM op_reject AS r
        JOIN process_list AS p ON (p.process_id = r.process_id)
                WHERE WEEK(reject_date) + 1 = '$id' GROUP BY process_name ORDER BY p.process_id 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'];
                $reject_date  = $row_comp['reject_date'];

                echo "<td>$process</td>";


$sql_date = "SELECT DISTINCT r.reject_date
FROM op_reject AS r
JOIN process_list AS p ON (p.process_id = r.process_id)
WHERE WEEK(reject_date) = '$id'
GROUP BY r.reject_date ORDER BY r.reject_date  ASC";
$res_date = mysql_query($sql);

while($row_date = mysql_fetch_assoc($res_date))
{
    $report_date_  = $row_date['reject_date'];


    $sql_rej = "SELECT DISTINCT reject_type FROM op_reject WHERE reject_date = '$report_date_' and process_id = '$process_id'";
        $res_rej = mysql_query($sql_rej);
        $cnt_rej = mysql_num_rows($res_rej);


        if($cnt_rej > 0)
        {
            while($row_rej=mysql_fetch_assoc($res_rej))
            {
                $reject_type = $row_rej['reject_type'];

                echo "<td>$reject_type</td>";
            }
        }
}

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

I hope somebody can help me through this.

Thank you so much.

2

Hi,
I can’t really see the output, maybe you haven’t uploaded the image correctly ? And the code is a little bit whacky… Update your post and maybe we can help you.

3

Hi,

this is my whole code:

weekly_reject.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_reject.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" />

<style type="text/css">
    .display {
    background-color:#9D9D9D;
    color:#00000;
    font-family: Arial;
    font-size: 13px;
    }
    .display th{
    background-color:#4F8E38;
    color:#FFF;
    font-size: 14px;
    font-weight:bold;
    text-align:center;
    padding: 1px 3px;
    }
    .display td{
    background-color:#FFF;
    color:#00000;
    font-weight:bold;
    padding: 2px 4px;
    border:1px solid #E1E1E1;
    }
    .display .total{
    background-color:#F7FFF1;
    color:#000000;
    font-size: 13px;
    font-weight:bold;
    padding: 2px 4px;
    border-top:1px solid #609A2C;
    border-bottom:1px solid #609A2C;
    }
    .display .border {
    border-left:2px solid #385320;
    }
    </style>

<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_reject.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_reject" 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> <a href="remarks.php" title="Remarks"><span>Remarks</span></a></li>
<li id="current"> <a href="weekly_report.php" title="Reports"><span>Reports</span></a></li>
</ul>
</div>
<br/>
<div id="ddcolortabs1">
<ul>
<li><a href="weekly_report.php" title="Weekly Report"><span>Weekly Report</span></a></li>
<li id="current"><a href="weekly_reject.php" title="Weekly Reject"><span>Weekly Reject</span></a></li>
<li><a href="monthly_report.php" title="Monthly Report"><span>Monthly Report</span></a></li>
<li><a href="remarks_report.php" title="Remarks Report"><span>Remarks Report</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>

and the get_weekly_reject.php


<?php
ob_start();
include "connection.php";
/*
Assuming that $id is called by other means but
defined here for testing.  So instead of checking
IF $id is a particular number, we check that it is set
and query for specific week without adjustment in query
*/
$id = "29";

//$id = $_POST['id'];
if(isset($id)){
    /*
    We are building an array of all information with a single query.
    */
    $Reports = array();
    $dates = array();
    $process_names = array();
    $reject_types = array();
    $compound_types = array();
    $rejects = array();
    $reject_total = array();
    $sql = "SELECT
    r.reject_date,
    r.reject,
    r.process_id,
    r.reject_type,
    r.compound_type,
    p.process_name
    FROM op_reject AS r
    JOIN process_list AS p
    ON (p.process_id = r.process_id)
    WHERE WEEK(reject_date) = '$id'
    ORDER BY r.reject_date, compound_type  ASC";
    $res = mysql_query($sql);
    $k=0;
    while($row = mysql_fetch_assoc($res)){
        $Reports[$k]['reject_date'] = $row['reject_date'];
        $Reports[$k]['process_name'] = $row['process_name'];
        $Reports[$k]['reject_type'] = $row['reject_type'];
        $Reports[$k]['compound_type'] = $row['compound_type'];
        $Reports[$k]['reject'] = $row['reject'];
       $k++;
    }

    //| *************************************************** |\\\\
    //| Build functions to return specific values or arrays |\\\\
    //| *************************************************** |\\\\

    // Returns dates array \\\\
    function dates($array){
        $dates = array();
        foreach($array as $k => $v){
            $dates[] = $v['reject_date'];
        }
        $dates = array_unique($dates);
        sort($dates);
        return $dates;
    }

    // Returns process_names array \\\\
    function process_names($array){
        $process_names = array();
        foreach($array as $k => $v){
            $process_names[] = $v['process_name'];
        }
        $process_names = array_unique($process_names);
        sort($process_names);
        return $process_names;
    }

    //*****************************************//
       // Returns number of columns by date \\\\
    //*****************************************//
    function columns($array,$date){
        //process names
        $process_names = array();
        foreach($array as $k => $v){
            if ($v['reject_date'] == $date){
                $process_names[] = $v['process_name'];
            }
        }
        $process_names = array_unique($process_names);
        sort($process_names);
        // get reject_types by process name
        $reject_types = array();
        foreach($process_names as $process_name){
            foreach($array as $k => $v){
                if ($v['reject_date'] == $date &&
                    $v['process_name'] == $process_name){
                    $reject_types[$process_name][$v['reject_type']] = $v['reject_type'];
                }
            }
        }
        //Convert reject_type to count
        $counts = array();
        foreach($reject_types as $reject_type){
            $counts[] = count($reject_type);
        }
        //return largest value
        $max_count = max($counts);
        return $max_count;
    }
    //*****************************************//
     // END Returns number of columns by date \\\\
    //*****************************************//

    // Returns compound_type array for process_name \\\\
    function compound_types($array,$process_name){
        $compound_types = array();
        foreach($array as $k => $v){
            if ($v['process_name'] == $process_name){
                $compound_types[] = $v['compound_type'];
            }
        }
        $compound_types = array_unique($compound_types);
        sort($compound_types);
        return $compound_types;
    }

    // Returns reject_type array for date and process_name \\\\
    function reject_types($array,$reject_date,$process_name){
        $reject_types = array();
        foreach($array as $k => $v){
            if ($v['reject_date'] == $reject_date &&
                $v['process_name'] == $process_name){
                $reject_types[] = $v['reject_type'];
            }
        }
        $reject_types = array_unique($reject_types);
        sort($reject_types);
        return $reject_types;
    }

    // Returns reject amount for cell \\\\
    function searchresults($array,$reject_date,$process_name,$reject_type,$compound_type){
        $result = "";
        foreach($array as $k => $v){
            if ($v['reject_date'] == "{$reject_date}" &&
                $v['process_name'] == "{$process_name}" &&
                $v['reject_type'] == $reject_type &&
                $v['compound_type'] == $compound_type){
                $result = $v['reject'];
            }
        }
        return $result;
    }

    // Returns reject total based on $reject_date,$process_name,$reject_type \\\\
    function resulttotals($array,$reject_date,$process_name,$reject_type){
        $total=0;
        foreach($array as $k => $v){
            if ($v['reject_date'] == $reject_date &&
                $v['process_name'] == $process_name &&
                $v['reject_type'] == $reject_type){
                $total += $v['reject'];
            }
        }
        $total = ($total!=0 ? number_format($total, 2, '.', '') : '');
        return $total;
    }

//echo "<pre>";
//print_r($Reports);
//echo "</pre>";

////Build display data BEFORE output to browser////
$display = "<table border=0 cellpadding=0 cellspacing=0 class=\\"display\\">
        <thead>
            <tr>
                <th>Compound</th>\\r";
                //I'll keep your id identifier in place
                $i=1;
                $dates = dates($Reports);
                foreach($dates as $date){
                    //We'll run our columns function in our date loop to get column count
                    $cols = columns($Reports,$date);

                    $display .= "<th id=col".$i." colspan=\\"$cols\\">$date</th>\\r";

                    $i++;
                }
            $display .= "</tr>
            </thead>";

                $process_names = process_names($Reports);
                foreach($process_names as $process_name){
                    $display .= "<tr>\\r";
                        $display .= "<td>$process_name</td>\\r";

                    foreach($dates as $date){
                           $bor=0;
                         $reject_types = reject_types($Reports,$date,$process_name);
                        $reject_type_count = count($reject_types);
                        $type_for_date = (!empty($reject_types) ? "true" : "false");
                        $cols = columns($Reports,$date);

                        if ($type_for_date == "false"){
                            for($c=0;$c<$cols;$c++){
                                $border = ($bor==0 ? " class=\\"border\\"" : '');

                                $display .= "<td$border> </td>\\r";

                                $bor++;
                            }
                        }else{
                            foreach($reject_types as $reject_type){
                                $border = ($bor==0 ? " class=\\"border\\"" : '');

                                $display .= "<td$border>$reject_type$bor</td>\\r";

                                $bor++;
                            }
                            for($d=$reject_type_count;$d<$cols;$d++){
                                $border = ($bor==0 ? " class=\\"border\\"" : '');

                                $display .= "<td$border> </td>\\r";

                                $bor++;
                            }
                        }
                    }
            $display .= "</tr>\\r";

                $compound_types = compound_types($Reports,$process_name);
                        foreach($compound_types as $compound_type){
                            $display .= "<tr>\\r";
                                $display .= "<td>$compound_type</td>\\r";

                            foreach($dates as $date){
                                $bor=0;
                                $reject_types = reject_types($Reports,$date,$process_name);
                                $reject_type_count = count($reject_types);
                                $type_for_date = (!empty($reject_types) ? "true" : "false");
                                $cols = columns($Reports,$date);
                                 if ($type_for_date == "false"){
                                    for($c=0;$c<$cols;$c++){
                                        $border = ($bor==0 ? " class=\\"border\\"" : '');

                                        $display .= "<td$border> </td>\\r";

                                        $bor++;
                                    }
                                }else{
                                    foreach($reject_types as $reject_type){
                                        $border = ($bor==0 ? " class=\\"border\\"" : '');
                                        $reject = searchresults($Reports,$date,$process_name,$reject_type,$compound_type);
                                        $reject = (!empty($reject) ? $reject : " ");

                                        $display .= "<td$border>$reject</td>\\r";

                                        $bor++;

                                        for($d=$reject_type_count;$d<$cols;$d++){
                                            $border = ($bor==0 ? " class=\\"border\\"" : '');

                                            $display .= "<td$border> </td>\\r";

                                            $bor++;
                                        }
                                    }
                                }
                            }
            $display .= "</tr>\\r";
                        }

           //---total rejects per process and per reject type---//
                        $display .= "<tr>\\r";
                        $display .= "<td class=\\"total\\">Total</td>\\r";

                            foreach($dates as $date){
                                $bor=0;
                                $reject_types = reject_types($Reports,$date,$process_name);
                                $reject_type_count = count($reject_types);
                                $type_for_date = (!empty($reject_types) ? "true" : "false");
                                $cols = columns($Reports,$date);
                                 if ($type_for_date == "false"){
                                    for($c=0;$c<$cols;$c++){
                                        $border = ($bor==0 ? " border" : '');

                                        $display .= "<td class=\\"total$border\\"> </td>\\r";

                                        $bor++;
                                    }
                                }else{
                                    foreach($reject_types as $reject_type){
                                        $border = ($bor==0 ? " border" : '');
                                        $rejectTotal = resulttotals($Reports,$date,$process_name,$reject_type);
                                        $rejectTotal = (!empty($rejectTotal) ? $rejectTotal : " ");

                                        $display .= "<td class=\\"total$border\\">$rejectTotal</td>\\r";

                                        $bor++;

                                        for($d=$reject_type_count;$d<$cols;$d++){
                                            $border = ($bor==0 ? " border" : '');

                                            $display .= "<td class=\\"total$border\\"> </td>\\r";

                                            $bor++;
                                        }
                                    }
                                }
                            }
                        $display .= "</tr>\\r";
                        }
    $display .= "</table>\\r";
}


        if(isset($display)){
            echo "$display";
        }
?>

on my first dummy data sample the layout is right, but when I tried the real data the display is like what I attached, for better understanding I also attached my database.

Thank you so much.

4

Are you seeking help with your PHP or with your layout? If PHP, I’ll move this to the PHP forum. If you are looking for layout help, then remove the PHP and post a working HTML/CSS template that demonstrates the problem, as outlined here: http://www.sitepoint.com/forums/showthread.php?1041498-Forum-Posting-Basics&p=5406274&viewfull=1#post5406274

5

I’m also seeking help in php code.

Thank you

6

That’s a very vague answer that doesn’t help very much. :-/