Jump to content

Is there a way to put this in one query?

aeroswat

By aeroswat
in PHP Coding Help

 Share

Recommended Posts

aeroswat Advanced Member

aeroswat

Posted
Posted

I'm envisioning a JOIN, a GROUP BY with 2 arguments, and a count...

 

Here's my tables

tblSchools (2 fields):

-SystemName

-Account

 

tblOrders (2 fields):

-SystemName

-Format

 

I want to be able to display the amount of each format broken down by account like this

                  Format 1          Format 2            Format 3          Total

Acct 2            583                  324                    432              some #

Acct 3            612                  432                    623              some #

Acct 4            127                  471                    435              some #

Total            some #            some #              some #        some #

 

The formats will not be in alphabetical order so I will have to put my results in an array and access them that way somehow. I'm thinking this will be somewhat like what my SQL statement will look like

SELECT o.*, s.*, COUNT(*) FROM tblOrders AS o INNER JOIN tblSchools AS s ON o.SystemName = s.SystemName GROUP BY s.Acct, o.Format

Will that give me what I need?

 

Also I am wondering what would be the best way to access these? Should I insert each row of the result in a php array or should i create a multidimensional php array with the account number as the first dimension and the format as the second dimension with the value being the count?

 

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

Getting the data will be easy, the problematic issue is displaying the results because the number of columns will be determined by the Account with the most Formats and that is not known until you have processed all the records. In other words, when you create the first row you won't know how many columns to create. So, you may need to pre-process the data from the query first.

 

One thing I don't understant is what the totals at the bottom are supposed to represent. The ones on the right will be the total of all formats for an account, but the totals at the bottom don't mean anything because they will change based upon the order of the formats listed for each account.

 

Anyway, see what this does for you (not tested):

<?php

//Query the data
$query = "SELECT s.Account, o.Format
          FROM tblSchools s
          JOIN tblOrders ON o.SystemName = s.SystemName
          ORDER BY s.Account, o.Format";
$result = mysql_query($query) or die(mysql_error());

//Put data into array
$accountData = array();
while($row = mysql_fetch_assoc($result))
{
    if(!isset($accountData[$row['Account']]))
    {
        $accountData[$row['Account']] = array();
    }
    $accountData[$row['Account']][] = $row['Format'];
}

//Determine maximum formats
$maxFormats = 0;
foreach($accountData as $accountFormats)
{
    if(count($accountFormats)>$maxFormats)
    {
        $maxFormats = count($accountFormats);
    }
}

//Create the header row
$tableOutput .= "<tr>\n";
$tableOutput .= "<th></th>\n";
for($i=0; $i<$maxFormats; $i++)
{
    $tableOutput .= "<th>Format {$i}</th>\n";
}
$tableOutput .= "<th>Total</th>\n";
$tableOutput .= "</tr>\n";

//Create the data rows
$columnTotals = array();
$tableOutput = '';
foreach($accountData as $account => $accountFormats)
{
    $subTotal = 0;
    $tableOutput .= "<tr>\n";
    $tableOutput .= "<th>{$account}</th>\n";
    for($i=0; $i<$maxFormats; $i++)
    {
        $format = '';
        if (isset($accountFormats[$i]))
        {
            $format = $accountFormats[$i];
            $subTotal += $accountFormats[$i];
            $columnTotals[$i] += $accountFormats[$i];
        }
        $tableOutput .= "<td>{$format}</td>\n";
    }
    $tableOutput .= "<td>{$subTotal}</td>\n";
    $tableOutput .= "</tr>\n";
}

//Create the bottom total row
$tableOutput .= "<tr>\n";
for($i=0; $i<$maxFormats; $i++)
{
    $tableOutput .= "<td>{$columnTotals[$i]}</td>\n";
}
$tableOutput .= "<td>" . array_sum($columnTotals) . "</td>\n";
$tableOutput .= "</tr>\n";

?>
<html>
<head></head>
<body>

<table>
  <?php echo $tableOutput; ?>
</table>

</body>
</html>

Link to comment
Share on other sites
aeroswat Advanced Member

aeroswat

Posted
  • Author
Posted

Thank you very much for the well thought out reply. I should clarify a little more because I think a little bit was not unveiled in the beginning. There are set formats. It will always be Format A, Format B, Format C for each of the accounts. Also I am displaying this in a pdf so i apologize for the extra effort you put forth with the table creation. I am using the query I posted above and it is pulling the correct information however it seems I do not know how to access the count. The field names are different but let me show you the code I have so far...

 

	$counts = array();
for($i=2;$i<5;$i++) {
	$counts[$i] = array();
}

$qry0 = "SELECT o.FormatNeeded, s.Account, COUNT(*) FROM tblOrders AS o INNER JOIN tblSchools AS s ON o.SchoolSystem = s.systemName GROUP BY s.Account, o.FormatNeeded";
$result0 = mysql_query($qry0);

if($result0) {
	while($row = mysql_fetch_assoc($result0)) {
		$counts[$row['Account']][$row['FormatNeeded']] = $row['Count(*)'];;
	}
}

$pdf=new FPDF('P', 'mm', 'Letter');
$pdf->SetLineWidth(1);
$pdf->SetAutoPageBreak(0);

$pdf->AddPage();
$pdf->SetMargins(3, 5, 3);
$pdf->SetY(5);
$pdf->SetFont('Times','',14);
$pdf->Cell(205,5,'TABLE XIV: AIRCB-' . date('Y') . 'ORDER STATISTICS',0,1,'C');
$pdf->SetY(24);
$pdf->Cell(150,5,'                                                                                                            Material Value');			
$pdf->SetY(30);
$pdf->Line($pdf->GetX(),$pdf->GetY(),$pdf->GetX()+210,$pdf->GetY());
$pdf->Ln(5);

$pdf->Cell(75,5,'LEA (CITY AND COUNTY)');
$pdf->Cell(20,5,'' . $counts[4]['BR']);
$pdf->Cell(20,5,$counts[4]['LP']);
$pdf->Cell(20,5,$counts[4]['AU']);
$pdf->Cell(20,5,$counts[4]['ET']);
$pdf->Cell(20,5,$counts[4]['EQ']);
$pdf->Cell(20,5,$counts[4]['PA']);
$pdf->Cell(20,5,$counts[4]['SU']);
$pdf->Ln(5);
$pdf->Cell(75,5,'AIDB');
$pdf->Cell(20,5,$counts[2]['BR']);
$pdf->Cell(20,5,$counts[2]['LP']);
$pdf->Cell(20,5,$counts[2]['AU']);
$pdf->Cell(20,5,$counts[2]['ET']);
$pdf->Cell(20,5,$counts[2]['EQ']);
$pdf->Cell(20,5,$counts[2]['PA']);
$pdf->Cell(20,5,$counts[2]['SU']);
$pdf->Ln(5);
$pdf->Cell(75,5,'PRIVATE AND OTHER');
$pdf->Cell(20,5,$counts[3]['BR']);
$pdf->Cell(20,5,$counts[3]['LP']);
$pdf->Cell(20,5,$counts[3]['AU']);
$pdf->Cell(20,5,$counts[3]['ET']);
$pdf->Cell(20,5,$counts[3]['EQ']);
$pdf->Cell(20,5,$counts[3]['PA']);
$pdf->Cell(20,5,$counts[3]['SU']);

 

I have replaced the $row['Count(*)'] with a test marker just to ensure that it was setting the correct spots and it does. The problem i am running into is I cannot access the Count in the array with that. Am I supposed to use something else to access the count?

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.