Outputting data from MySQL Table to columns in PHP

[Travis959]

Hello,

 

I have a script that processes data from a form, and then outputs data from a MySQL table based on which selections are made on the form.

 

What I'm trying to do is create columns showing data for each month depending on which months the user selects, and then a "Total" column at the end of that. The output first determines the "family" the results are in, then the ID the product is for the parent family, and then it outputs the data based on what ID is listed.

 

Unfortunately, I have no idea how to do this without nesting while loops inside other while loops which I know is bad for server performance - and yes this does take a long time to complete.  Here is some of my code for this to show what I'm doing wrong:

 

$searchdatabase5 = "SELECT field3 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 != 0 AND field4 != '995' GROUP BY field3";
$run5 = mysql_query($searchdatabase5) or die(mysql_error());
while($row5 = mysql_fetch_assoc($run5))
{ 
$field5_3 = $row5['field3'];
echo '<tr><td colspan="0"><hr style="width: 100%; height: 1px; color: #a0a0a0;"></td></tr><tr><td colspan="0" bgcolor="#ccffcc"><strong>FAMILY '. $field5_3 .'</strong><br /></td></tr>';
   $searchdatabase12 = "SELECT field4 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 = '$field5_3' AND field4 != 0 GROUP BY field4";
   $run12 = mysql_query($searchdatabase12) or die(mysql_error());
   while($row12 = mysql_fetch_assoc($run12))
   {	
    $field12_4 = $row12['field4'];
$searchdatabase6 = "SELECT field3,field4,field5,field6 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field4 = '$field12_4' GROUP BY field3,field4,field5";
$run6 = mysql_query($searchdatabase6) or die(mysql_error());
while($row6 = mysql_fetch_assoc($run6))
{ 
	$field6_3 = $row6['field3'];
	$field6_4 = $row6['field4'];
	$field6_5 = $row6['field5'];
	$field6_6 = $row6['field6'];
	echo '<tr><td colspan="0">'. $field6_5 .' - '. $field6_6 .'</td></tr><tr><td valign="middle" align="left" bgcolor="#ccc" width="183px">Units</td>';
	$searchdatabase7 = "SELECT * FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field5 = '$field6_5' ORDER BY year,month ASC";
	$run7 = mysql_query($searchdatabase7) or die(mysql_error());	
	while($row7 = mysql_fetch_assoc($run7))
	{ 	
		$field7_3 = $row7['field3'];
		$field7_4 = $row7['field4'];
		$field7_5 = $row7['field5'];	    
		$field7_9 = $row7['field9'];
		$field7_10 = $row7['field10'];
		$field7_month = $row7['month'];
		$field7_year = $row7['year'];
		$startmonth2 = ltrim($startmonth, '0');	
		echo '<td bgcolor="#e6e6e6" width="95px" nowrap="nowrap" align="right">'; echo $field7_10; echo '</td>';			
		}

 

Here is the output:

 

 

I know the code is very bad, but what would be the best way to recreate what I have in the image?

 

Thank you.

[bluejay002]

If you prefer not to have so many nesting loops, you can alternatively use SQL and use JOIN / UNION. Also, having so many database queries unnecessarily is also not good. Below are some topic links on both (or check google, there's plenty you can find there), please see which applies to you:

 

JOIN: http://www.w3schools.com/sql/sql_join.asp

UNION: http://www.mysqltutorial.org/sql-union-mysql.aspx

[Travis959]

I would assume UNION would be better, but is there an easier way to do what I want? For instance, can I just do one query to select the whole database, and insert that result into an array and display the results with that? If so, I have no clue how to do it this way.

 

[PFMaBiSmAd]

Your goal will be to get all the data you are interested in, using one query, in the order that you want that data, then simply output the information the way you want it when you iterate over the data using a while(){} loop.

 

For your one-time heading information and the 71059B,71060B,... sub-headings, you would simply detect the first pass through the loop (to output the one-time heading) and detect every time the sub-heading changes to (optionally) close out the previous section and start a new section.

 

Based on what I can tell from your queries, you don't even need to do a UNION/JOIN, but your use of non-descriptive column names/variables leaves a lot to be desired in the clarity of what you are trying to do (makes it hard for us to directly help you with the one query that could do this.)

 

Edit: Here is some sample code -

 

<?php
$query = "your query statement here...";
$result = mysql_query($query);
$last_heading = null; // remember the last heading (initialize to null)
while($row = mysql_fetch_assoc($result)){
$new_heading = $row['some_column_name_here...']; // get the column in the data that represents the heading
if($last_heading != $new_heading){
	// heading changed or is the first one
	if($last_heading != null){
		// not the first section, close out the previous section here...
		echo "(optionally) do what is needed to close out the previous section";
	} else {
		// is the first section, output the one-time heading here...
		echo "do what is needed to output the one-time heading";
	}
	$last_heading = $new_heading; // remember the new heading
	// start a new section, output the heading here...
	echo "do what is needed to start a new section";
}
// output each piece of data under a heading here...
echo "handle each row of data under any heading";
}
if($last_heading != null){
// there was at least one section, close out the last section here...
echo "(optionally) do what is needed to close out the last section";
}

 

 

[Travis959]

Ah sorry about that, I know what each field represents in my head so I didn't even think about it when I posted the help.

 

 

I labeled what each field is in the image, so that should hopefully help you get a better understating of the placement of the fields.

[Psycho]

Did you try the code that PFMaBiSmAd provided?

 

Also, knowing what a field means "in your head" is freakin ludicrous. Give your fields descriptive names. It will make your life much easier.

[PFMaBiSmAd]

About the only thing I can tell is -

 

$new_heading = $row['some_column_name_here...']; // get the column in the data that represents the heading

 

in the sample code is your 'Field5'.

[Travis959]

Also, knowing what a field means "in your head" is freakin ludicrous. Give your fields descriptive names. It will make your life much easier.

 

I agree, and I apologize for the messy names of the fields, but the reason for the names is because the names in the database were named the same as the fields in a file that is generated every night in unix that is imported into said database. Unfortunately that was long before my time, and I doubt they will change the names now.

 

Anyways, here is PFMaBiSmAd's code so far:

 

$query = "SELECT * FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 != 0 AND field3 != '90' ORDER BY field3,field4";
$result = mysql_query($query);
$last_heading = null; // remember the last heading (initialize to null)
while($row = mysql_fetch_assoc($result)){
$new_heading = $row['field5']; // get the column in the data that represents the heading
if($last_heading != $new_heading){
	// heading changed or is the first one
	if($last_heading != null){
		// not the first section, close out the previous section here...
		echo '<tr><td colspan="0">'.$row['field5'].'</td></tr><tr><td valign="middle" align="left" bgcolor="#ccc" width="183px">Units</td>';
	} else {
		// is the first section, output the one-time heading here...
		echo '<tr><td colspan="0"><hr style="width: 100%; height: 1px; color: #a0a0a0;"></td></tr><tr><td colspan="0" bgcolor="#ccffcc"><strong>FAMILY '.$row['field3'].'</strong><br /></td></tr>';
	}
	$last_heading = $new_heading; // remember the new heading
	// start a new section, output the heading here...
	echo '</tr>';
}
// output each piece of data under a heading here...
echo '';
}
if($last_heading != null){
// there was at least one section, close out the last section here...
echo "(optionally) do what is needed to close out the last section<br />";
}

 

and the results:

 

 

My question now is how to create columns next to "Units" and input the data? Also need to create the sales and average rows as well under units, but not sure how to go about it using the code above.

 

Thanks.

[Psycho]

I agree, and I apologize for the messy names of the fields, but the reason for the names is because the names in the database were named the same as the fields in a file that is generated every night in unix that is imported into said database. Unfortunately that was long before my time, and I doubt they will change the names now.

The names in the input file do not need to dictate the names of the fields in the database. When the import is done is when you should 'translate' the data to logically named fields.

 

Can you provide an export of the table with some sample data? That would make this much easier.

 

EDIT: A couple questions. Are the values for fiel9 & field10 already summed? For example, in the image above where you have 50441 for January you have values of 15 and 157.5 are those the values from a single DB entry or are there multiple records for 50441 in that minth that need to be summed? Also, is there ALWAYS a value for every month for each field5?

[PFMaBiSmAd]

You can always alias a column in your query to give it a meaningful name -

 

SELECT field5 as whatever, field6 as somethingelse, ...

 

Then reference those aliases in your php code -

 

$row['whatever']  $row['somethingelse']

[PFMaBiSmAd]

how to create columns next to "Units" and input the data?

 

In order to make your code general purpose (so that all you need to do is setup data that defines what should be produced before executing your code), you would use an array that contains the field names that you want to output for each column. You would then iterate over this array of field names and access the corresponding $row[$field] value -

<?php
$fields = array('whatever','somethingelse','nextfield');

foreach($fields as $field){
//do something with the value in $row[$field]
echo $row[$field];
}

 

This method can also be expanded so that the array holds both the field name and the corresponding column heading (Jan, Feb, Mar, ...) and you can dynamically produce the column heading by iterating over the same $fields array. To reuse the same code for a different quarter range of months of your choice, just setup the $fields array with the correct field names and column headings.

[Travis959]

A couple questions. Are the values for fiel9 & field10 already summed? For example, in the image above where you have 50441 for January you have values of 15 and 157.5 are those the values from a single DB entry or are there multiple records for 50441 in that minth that need to be summed? Also, is there ALWAYS a value for every month for each field5?

 

The values are not summed in the database, these need to be done in the PHP. The other values except the averages(this needs to be done in the php as well), are in the database.

 

There is only one record per month for each "company". And yes there is always a value for a month, it should always have at least a 0.

 

I've included the first 500+rows of the table.

 

I really appreciate all the help.

 

Edit: The file uploader on here says the upload folder is full so I uploaded it here: http://www.gamerhandles.com/slssum2.sql.zip

[Psycho]

Alrighty, I was bored today and this was kinda fun. The below script should produce the results you are looking for. I have hard-coded the dates and company names at the top of the script but you can hook in whatever means you are using to determine those values (a form POST I'm guessing).

 

Also, I made one change to the output from what you presented above. I added field4 to the field5 for the headings for each sub-table output. It just made more sense to me. but, you can easily remove that if needed

 

I have tested the code against the db you provided and it appears to be working correctly. But, I have not done a LOT of testing. So, you will want to give it a thorough going over.

 

<?php

mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('tab_test') or die(mysql_error());
$startyear = '2011';
$startmonth = '02';
$endyear = '2012';
$endmonth = '05';
$companyname = 'IMPORTADORA MADURO';


//Function to output parent (or summation) sub-table
function createSeriesTable($dateCodes, $dataArray, $parentCode, $childCode, $colspan, $summation=false)
{
    //Vars to calculate totals
    $unitsTotal = 0;
    $salesTotal = 0;
    //Vars to hold HTML content
    $unitsRow = '';
    $salesRow = '';
    $avgRow   = '';

    foreach($dateCodes as $dateCode)
    {
        $units = isset($dataArray[$dateCode]['units']) ? $dataArray[$dateCode]['units'] : 0;
        $sales = isset($dataArray[$dateCode]['sales']) ? $dataArray[$dateCode]['sales'] : 0;
        $avg = ($units!=0) ? $sales/$units : 0;

        $unitsTotal += $units;
        $salesTotal += $sales;

        $unitsRow .= "<td>{$units}</td>";
        $salesRow .= "<td>" .  number_format($sales, 2) . "</td>";
        $avgRow   .= "<td>" .  number_format($avg, 2) . "</td>";
    }
    $avgTotal =  ($unitsTotal!=0) ?($salesTotal/$unitsTotal) : 0;

    if($summation)
    {
        $title = "Line Total for {$parentCode} : {$childCode}";
        $label_class = 'summation_label';
        $data_class = 'summationTable';
    }
    else
    {
        $title = "{$parentCode} : {$childCode}";
        $label_class = 'data_label';
        $data_class = 'childTable';
    }

    $tableHTML  = "<tr class='{$label_class}'><th colspan='{$colspan}'>{$title}</th></tr>\n";
    $tableHTML .= "<tr class='{$data_class}'><th>Units</td>{$unitsRow}<td>{$unitsTotal}</td></tr>\n";
    $tableHTML .= "<tr class='{$data_class}'><th>Sales</td>{$salesRow}<td>" .  number_format($salesTotal, 2) . "</td></tr>\n";
    $tableHTML .= "<tr class='{$data_class}'><th>Average Price</td>{$avgRow}<td>" .  number_format($avg, 2) . "</td></tr>\n";
    if($summation)
    {
        $tableHTML .= "<tr><td colspan='{$colspan}'> </td></tr>\n";
    }
    return $tableHTML;
}

//Create array of month/year codes and header HTML code
$monthCount = 0;
$datestamp = 0;
$enddatestamp = mktime(12, 0, 0, $endmonth, 1, $endyear);
$dateCodes = array();
$dateHeadersHTML = '';
while($datestamp < $enddatestamp)
{
    $datestamp = mktime(12, 0, 0, $startmonth+$monthCount, 1, $startyear);
    $year  = date('Y', $datestamp);
    $month = date('m', $datestamp);
    $monthName = date('M', $datestamp);
    $dateCodes[] = "{$year}-{$month}";
    $dateHeadersHTML .= "<th>{$monthName}<br>{$year}</th>\n";
    $monthCount++;
}
$table_colpsan = count($dateCodes) + 2;

//Determine the number of months in the report
$report_month_count = ($endyear*12+$endmonth) - ($startyear*12+$startmonth) + 1;
$report_column_count = $report_month_count+2;

$query = "SELECT field3 AS family, field4 AS parent_code, field5 as child_code,
                 month, year, field10 AS units, field9 AS sales
          FROM slssum2
          WHERE company = '$companyname'
            AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth'
            AND field3 != 0
            AND field4 != '995'
          ORDER BY field3, field4, field5, year, month";
$result = mysql_query($query) or die(mysql_error());

//Create flag vars to detect changes
$last_family = '';
$last_parent_code = '';
$last_child_code = '';

while($row = mysql_fetch_assoc($result))
{
    if($last_child_code != $row['child_code'])
    {
        //Display table for last child record set
        if($last_child_code != '')
        {
            $output .= createSeriesTable($dateCodes, $childData, $last_parent_code, $last_child_code, $table_colpsan);
        }
        //Create/reset temp array to hold data to be output
        $childData = array();
        $last_child_code = $row['child_code'];
    }

    if($last_parent_code != $row['parent_code'])
    {
        //Display summation table for last parent
        if($last_parent_code != '')
        {
            $output .= createSeriesTable($dateCodes, $parentData, $last_family, $last_parent_code, $table_colpsan, true);
        }
        //Create/reset temp array to hold data to be summed
        $parentData = array();
        $last_parent_code = $row['parent_code'];
    }

    if($last_family != $row['family'])
    {
        //Create Output for family heading
        $output .= "<tr><th colspan='{$table_colpsan}' class='familyHead'>FAMILY {$row['family']}</th></tr>";
        $last_family = $row['family'];

        //Create summation table for last parent
        //$output .= createSeriesTable($dateCodes, $parentData, $row['parent_code'], $table_colpsan, $row['family']);
    }

    //Set child/parent data using the current record
    $month = "{$row['year']}-{$row['month']}";
    $childData[$month]  = array('units' => $row['units'], 'sales' => $row['sales']);
    $parentData[$month] = array('units'=>($parentData[$month]['units']+$row['units']),
                                'sales'=>($parentData[$month]['sales']+$row['sales']) );
}
//Display last child record set and summation record set
$output .= createSeriesTable($dateCodes, $childData, $last_parent_code, $last_child_code, $table_colpsan);
$output .= createSeriesTable($dateCodes, $parentData, $last_family, $last_parent_code, $table_colpsan, true);

?>
<html>
<head>
    <style>
        .dateHead th { color:green; text-align:center; font-weight:normal; }
        .familyHead { text-align:left;background-color:#33ffbb; padding: 5px; }
        .summation_label th { text-align:left; padding: 5px; font-weight: bold; }
        .data_label th { text-align:left; padding: 5px; font-weight: normal; }
        .childTable th { text-align:left; background-color:#cecece; font-weight:normal; padding:5px; }
        .childTable td { background-color:#efefef; text-align:right; padding:5px;}
        .summationTable th { text-align:left; background-color:#9999ff; font-weight:normal; padding:5px; }
        .summationTable td { background-color:#ccccff; text-align:right; padding:5px;}
    </style>
</head>
<body>

<table border='0'>
  <tr><td><?php echo "{$startmonth}/{$startyear} to {$endmonth}/{$endyear}</td></tr>"; ?></td></tr>
  <tr class='dateHead'><td></td><?php echo $dateHeadersHTML; ?><th>Total</th></tr>
  <tr><td colspan='<?php echo $table_colpsan; ?>'><hr></td></tr>
<?php echo $output; ?>
</table>


<pre>
<?php echo $query; ?>
</pre>

</body>
</html>

[Travis959]

omg you are awesome, that works perfectly.

 

Thank you so much :D

[Travis959]

Sorry to bother again, but I've been requested to add more information to this page and I am unsure of how to go about it with the current code.

 

I made a quick image to show what is requested:

 

 

The sizing of the bottom part is not correct in the image as it should be same width as above tables in the php.

 

But how would I go about adding the "Family Totals" after each family, which just adds all the previous "child_codes" for that family together?

 

And at the end of the report, I need to add up everything together and display in the "SUBTOTALS PRIOR TO ADJUSTMENT". 

 

The next part is trickier, as the "CUSTOMER ALW" in the image is just a field that is decided by because it's field3/family is either 90 or 0. This area can have other fields as long as field3/family is 90 or 0.

 

After that, "Adjustment Totals" need to be added which is everything with field4/parent_code = 995.

 

And finally Grand Total is just subtotals minus adjustment totals.

 

I hate asking for so much help, but this new code is definitely above my current abilities. I am going to try to see if I can get this working, but I'm sure someone could figure this out way faster than me.

 

[Psycho]

As I said, I only did all that because I was bored. It was a lot of work. If you want someone to DO this for you then you should be posting in the freelance forum. I'm certainly not willing to put more work into this out of the goodness of my heart. But, if you are willing to learn I will provide some guidance.

 

1. To get the Family total look at the logic I used generate the totals for the groups for field4. Create a temp array to store the totals for each family group until a change in family is detected. Then use that array to output the results. You can use the same display function I provided, but you will need to modify the last parameter. Right now, that parameter is a simple Boolean to determine whether you are displaying a 'normal' record or a summation for field4. Since you will want to use that same function for more than just two purposes you will want to change that parameter and the logic that uses it to use multiple values.

 

2. To get the subtotal of ALL families, just do the exact same thing as above. Create an array to store totals during the entire process. Once all records have been processed use the array to output the results. Again, you need to modify the function to output the results as you need them for the subtotal

 

3. I really don't understand your "requirements" regarding the "CUSTOMER ALW".

 

4. I also don't understand what you mean by "Adjustment Totals" need to be added. Where do these values come from and what are they added to? Just the ALW values?

 

5. For the grand total I would probably use an array for the ALW and adjustment totals (don't know if it would make sense to have one or two). BUt, then you would just create a foreach() loop to calculate the grand total for each month by comparing the values from the subtotal array and the adjustment arrays. And, again, I would put those value into an array (or more likely I would just change the values in the subtotal array). Then use the existing function to output the results from that array.

 

EDIT: I just noticed that the format of the new tables is different than the originals. Average price and sales are switched. Is this correct? If so, then I would likely do more intensive modifications of the current process (the format of the temp arrays and the display function) to do that.