Report

[HasNor]

Dear experties,

Actually I don't know how to specify what I'm trying to do. I only tell you thru this figure. I don't know how to come out report by this way.
How can I do? What kind of method to get report like this in a table?
Please help me out.....

Name List Transaction
.................................................................
Person A.............................Transaction1
............................................Transaction2
............................................Transaction3
............................................Transaction4
Person B.............................Transaction 5
............................................Transaction6
.
.
.
.

[azareth]

you can use html to create the table: http://www.w3schools.com/html/html_tables.asp
if you want to have input boxes in each cells use the <input /> tag
just browse the link and you'll have everything you need to create the table.

[diafol]

Depends how you're getting your data.

Let us assume you have 2 DB tables, one for user, one for transactions. Retrieve the info via INNER JOIN:

$result = mysql_query("SELECT u.username, t.transaction_label FROM users AS u INNER JOIN transactions AS t ORDER BY u.username, t.transaction_date");
$u = '';
$table = "";
if(mysql_num_rows($result)){
    $table="<table>\n\t<thead>\n\t\t<tr>\n\t\t\t<th>Name</th>\n\t\t\t<th>List Transaction</th>\n\t\t</tr>\n\t</thead>\n\t<tbody>"; 
    while($d = mysql_fetch_assoc($result)){
        $table .= "\n\t\t<tr>\n\t\t\t<td>";
        if($d['username'] != $u)$table .= $d['username'];
        $table .= "</td>\n\t\t\t<td>{$d['transaction_label']}</td>\n\t\t</tr>";
        $u = $d['username'];
    }
    $table .= "\n\t</tbody>\n</table>";
}
//then at a point of your chooosing:
echo $table;

[HasNor]

Hi all,

Thanks for reply..azareth, actually I'm using PHP.i've tried to find from there but nothing can help me. Maybe I not really understand from ther..Diafol, actually know how to view the data value, but in this case, I dont want "Person Name" duplicate to the next row if he/she got more than 1 transaction.. only list for one time at the first row. Means I want to show all transaction for each person.

[HasNor]

This is my table code. When I run this code, name of the person those do the transaction will repeat followed by how many transactions. In this case I don't know how to stop repeat the name on next row if transaction done by same person until it change to another person. Anybody can help me?????

 <table width="1365" border="1" align="center" cellspacing="0">
    <tr>
      <td width="64"><strong>Person</strong></td>
      <td width="201"><strong>Transaction</strong></td>
    </tr>
    <?php
    $sql="SELECT u.name, u.id, t.trans, t.person
    FROM user u, transaction t WHERE u.id=t.person ORDER BY u.name ASC";
    $result=mysql_query($sql);
    while($row=mysql_fetch_array($result)) {
    // $id= $row['reqid'];
    ?>
    <tr>
      <td><?php echo $row['name']; ?></td>
      <td><?php echo $row['trans']; ?></td>
    </tr>
    <?php }  ?>
  </table>

[azareth]

this is actually a combination of html and php .. :)

[HasNor]

Thanks Azareth for replry. So any suggestion for me to avoid the repeatation of Person?? When we generate report, it doesn't look like professional.. hurm.. :(

[azareth]

i'm not so sure if i get this right ..
how about you specify in the condition WHERE what transaction you are going to retrieve so that you can't have repetition of name?

[diafol]

HAs NOr - the example I gave just gives each name once - that's why I have the $u = $d['username']. Didi you try the code? All you have to do is change the SQL and the fieldnames to suit your data.

[HasNor]

Thanks for Diafol. I've tried your code and its work. Actually I haven't use code to come out the table. I just select from menu and it become a table. When I run your code, it's come without table line.. how can do for solve this issue?? I never create a table by write some codes..

[diafol]

That's all CSS or even inline html attributes - nothing to do with php really.

table{
    border-collapse:collapse;
}

th, td{
    border: black 1px solid;
}

[HasNor]

Ok, Thanks Mr Diafol. Erm, may I know is it able to me to add another column for Person's ID No but without repeatition on next row?? same as Person Name.

[diafol]

Why not?

<tr>\n\t\t\t<th>Name</th>

becomes

<tr>\n\t\t\t<th>ID</th>\n\t\t\t<th>Name</th>

and

if($d['username'] != $u)$table .= $d['username'];

becomes

if($d['username'] != $u){
    $table .= "{$d['id']}</td>\n\t\t\t<td>{$d['username']}" ;
}else{
    $table .= "</td>\n\t\t\t<td>";
}

[HasNor]

Ok..Thanks Mr Diafol for your helps.. It's work!! Thank you very much..

[HasNor]

Dear Mr Diafol;
I had tried this code but after it run, the list of transaction go to next row.. is it something wrong on my codes?? Need your help lor.. It go to next row starting from Date Respond. hurm...

 <?php
    $result = mysql_query("SELECT r.reqid, r.misRefId, r.priority, r.status, t.respond, t.reqid, t.PIC, t.tran_id, t.date, t.res_time FROM request r, transaction t WHERE r.reqid=t.reqid ORDER BY r.reqid, t.tran_id");
    $u = '';
    $table = "";
    if(mysql_num_rows($result)){
            $table="<table border='1' align='center' cellspacing='0'>\n\t<thead>\n\t\t<tr>\n\t\t\t<th>MIS Ref ID</th>\n\t\t\t<th>Status</th>\n\t\t\t<th>PIC</th>\n\t\t\t<th>Date 
            Respond</th>\n\t\t\t<th>Time</th>\n\t\t\t<th>List Transaction</th>\n\t\t</tr>\n\t</thead>\n\t<tbody>"; 
                while($d = mysql_fetch_assoc($result)){
                 $table .= "\n\t\t<tr>\n\t\t\t<td>";
                if($d['reqid'] != $u) {
                $table .= "{$d['misRefId']}</td>\n\t\t\t<td>{$row3['status_name']}";
                }
                else
                 $table .= "</td>\n\t\t\t<td></td>\n\t\t\t<td>{$d['PIC']}</td><td>{$d['date']}</td><td>{$d['res_time']}</td><td>{$d['respond']}</td>\n\t\t</tr>";
                 $u = $d['reqid'];
                 }
                 $table .= "\n\t</tbody>\n</table>";
                }
                //then at a point of your chooosing:
                echo $table;
    ?>