Organizing MySQL results?

[Kenny Pollock]

My code

<h2>House Income</h2>
<div id="maincontainer">


<table id="maintable">
<thead>

	<tr>
		<td>User</td>
		<td>Broker Pending</td>
		<td>Brokered</td>
		<td>Delivered</td>
		<td>Billed</td>
		<td>Completed</td>
		<td>Total House Income</td>
	</tr>

</thead>
<tbody>

<?php

$hi_query = mysql_query( "
select coalesce(sum(p.amount),0) as amt, 
         coalesce(sum(p.dr_amt),0) AS dr_amt, 
         p.uid AS user, 
         s.status AS statusname, 
         h.status 
  from order_payments p 
  left join order_header h 
    on h.id=p.order_id LEFT JOIN order_status s ON h.status=s.id 
where p.dtime >= '2008-03-08' 
    and p.dtime <= '2008-03-11' 
group by p.uid,
              h.status
" );





while( $hi = mysql_fetch_array( $hi_query ) )
{
$profit = $hi['amt'] - $hi['dr_amt'];
?>

	<tr>
		<td><?php echo $hi['user']; ?></td>
		<td><?php echo $hi['statusname']; ?></td>
		<td><?php echo $profit; ?></td>
		<td></td>
		<td></td>
		<td></td>
		<td></td>
	</tr>

<?php

}

?>
</tbody>
</table>

</div>

 

Gives me

 

I want to turn:

KENNY BROKERED $100

KENNY TO BE BROKERED $200

KENNY PENDING $300

 

Into:

| USER | BROKERED | TO BE BROKERED | PENDING |

| KENNY | $100 | $200 | $300 |

 

On top of that... I'm also asking how I can show all the users from the users table (right now it's selecting the users from the payments table) and show $0.00 and also show $0.00 in the column if they do not have a record with money in that status.

[Kenny Pollock]

Anyone?  ???

[Kenny Pollock]

Someone please help

[teng84]

can you give us your table structure we dont know what to query!

[Barand]

Just for Kenny, or do you want all values in "brokerpending" to have their own column for all users?

[Kenny Pollock]

One row per each user.

 

teng I'm kinda new to this... how can I give you my table structure... can I export from phpMyAdmin?

[Kenny Pollock]

???

[Barand]

here's one way, using an array to accumulate values for each heading

<?php

$initialValues = array('BROKERED' => 0, 'TO BE BROKERED' => 0, 'PENDING' => 0, 'OTHER' => 0);
$sql = "SELECT user, brokerpending, brokered FROM houses ORDER BY user";

$data = array();
$prev = '';
$res = mysql_query($sql) or die (mysql_error());
while (list($u, $bp, $b) = mysql_fetch_row($res))
{
    if ($prev != $u)
    {
        $data[$u] = $initialValues;                     // start with zero values for user
        $prev = $u;
    }
    $bp = strtoupper($bp);
    if (!isset($data[$u][$bp])) $bp = 'OTHER';
    $data[$u][$bp] += $b;                               // accumulate in array
}

echo '<table border="1">';
echo '<tr><th>User</th><th>' . join ('</th><th>', array_keys($initialValues)) . '</th></tr>';  // headings

foreach ($data as $user => $varray)
{
    foreach ($varray as $k=>$v) $varray[$k] = number_format($v, 2);                   // format values
    echo "<tr><td>$user</td><td>" . join ('</td><td>', $varray) . '</td></tr>';       // output values
}
?>

[Kenny Pollock]

Finally we're getting somewhere!

 

Now, I'm posting the code I used to get it to work thus far. I need help getting it to do the following:

• Total: last column shows all the columns added up for each user
  
• Subtract: in each column, the value should be the 'amt' field in the table minus the 'dr_amt' field
  

 

<h2>House Income</h2>
<div id="maincontainer">

<?php

//$profit = $hi['amt'] - $hi['dr_amt'];

$initialValues = array('BROKERED' => 0, 'TO BE BROKERED' => 0, 'PICKED UP' => 0, 'CANCELLED' => 0, 'BILLED' => 0, 'PENDING ON PURPOSE' => 0, 'COMPLETED' => 0 );
$sql = "select coalesce(sum(p.amount),0) as amt, 
         coalesce(sum(p.dr_amt),0) AS dr_amt, 
         p.uid AS user, 
         s.status AS statusname, 
         h.status 
  from order_payments p 
  left join order_header h 
    on h.id=p.order_id INNER JOIN order_status s 
  ON h.status=s.id 
where p.dtime >= '2008-03-08' 
    and p.dtime <= '2008-03-11' 
group by p.uid,
            h.status,
            s.status
";

$data = array();
$prev = '';
$res = mysql_query($sql) or die (mysql_error());

while (list($b, $amt, $u, $bp) = mysql_fetch_row($res))
{
if ($prev != $u)
{
	$data[$u] = $initialValues; // start with zero values for user
	$prev = $u;
}

$bp = strtoupper($bp);
$data[$u][$bp] += $b; // accumulate in array
}

echo '<table id="maintable">';
echo '<thead><tr><td>User</td><td>' . join ('</td><td>', array_keys($initialValues)) . '</td></tr></thead><tbody>'; // headings

foreach ($data as $user => $varray)
{
foreach ($varray as $k=>$v) $varray[$k] = number_format($v, 2); // format values
echo "<tr><td>$user</td><td>" . join ('</td><td>', $varray) . '</td></tr>'; // output values
}

?>
</tbody>
</table>

</div>

 

Thank you!!!

[Barand]

Minor tweaks. Changed lines 38, 42, 46, 48 for your net profit and totals column.

 

<h2>House Income</h2>
<div id="maincontainer">

<?php

//$profit = $hi['amt'] - $hi['dr_amt'];

$initialValues = array('BROKERED' => 0, 'TO BE BROKERED' => 0, 'PICKED UP' => 0, 'CANCELLED' => 0, 'BILLED' => 0, 'PENDING ON PURPOSE' => 0, 'COMPLETED' => 0 );
$sql = "select coalesce(sum(p.amount),0) as amt, 
         coalesce(sum(p.dr_amt),0) AS dr_amt, 
         p.uid AS user, 
         s.status AS statusname, 
         h.status 
  from order_payments p 
  left join order_header h 
    on h.id=p.order_id INNER JOIN order_status s 
  ON h.status=s.id 
where p.dtime >= '2008-03-08' 
    and p.dtime <= '2008-03-11' 
group by p.uid,
            h.status,
            s.status
";

$data = array();
$prev = '';
$res = mysql_query($sql) or die (mysql_error());

while (list($b, $amt, $u, $bp) = mysql_fetch_row($res))
{
if ($prev != $u)
{
	$data[$u] = $initialValues; // start with zero values for user
	$prev = $u;
}

$bp = strtoupper($bp);
$data[$u][$bp] += ($b - $amt); // accumulate in array
}

echo '<table id="maintable">';
echo '<thead><tr><td>User</td><td>' . join ('</td><td>', array_keys($initialValues)) . '</td><td>TOTAL</td></tr></thead><tbody>'; // headings

foreach ($data as $user => $varray)
{
$total = array_sum($varray);
    foreach ($varray as $k=>$v) $varray[$k] = number_format($v, 2); // format values
echo "<tr><td>$user</td><td>" . join ('</td><td>', $varray) . '</td><td>'.number_format($total, 2).'</td></tr>'; // output values
}

?>
</tbody>
</table>

</div>
?>

 

 

[Kenny Pollock]

Works great, thank you!!!!!

[Kenny Pollock]

Alright, need help with a slight modification.

 

At the very bottom of the table, I want the totals for each column displayed underneath. And then a total of each person's total (for a final total).

[Barand]

Create an array for the totals, like the arrays in the body, and accumulate values into the totals array. Print it at the end.