WHILE loop with BREAK

trangbn · August 9, 2011

I am trying insert a break into my loop statement. I created a report for users to pull data based on a date range. The report pulls a list of names and the last line shows the total number of names and sum of gross margins for a particular time frame. The problem is the last line (with the count and sum) is also repeating information from the previous line (shown below). I would like it to be blank except for the count and sum. Any suggestions would be great. I've tried a few different breaks but cannot get the logic right. If there is another way besides a break, let me know.

Current Display

Name Total Hires State Zipcode Gross Margin

Joe 1 MN 111111 3.00

Sally 1 MN 222222 4.00

(Blank) 2 MN 222222 7.00

What I would like it to display as

Name Total Hires State Zipcode Gross Margin

Joe 1 MN 111111 3.00

Sally 1 MN 222222 4.00

(Blank) 2 (Blank-------->) 7.00

<?php

include("db_connect.php");
include("functions.php");

$_GET=sanitize($_GET);

$query = "SELECT COUNT(consultant.flow_id),
			consultant.flow_id,
			full_name, 
			client_index, 
			sales_manager_index, 
			role, 
			street, 
			city, 
			consultant.state, 
			zip, 
			start_date,
			SUM(gross_margin)
	FROM contract, consultant 
	WHERE contract.deleted='false' AND " .  "consultant.deleted='false' AND contract.flow_id=consultant.flow_id 
		AND start_date>=str_to_date('" . $_GET['start_date_begin'] . "', '%m/%d/%Y') 
		AND start_date<=str_to_date('" . $_GET['start_date_end'] . "', '%m/%d/%Y') 
		AND status='active'
		GROUP BY full_name WITH ROLLUP";


?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Hire Report</title>
<link rel='stylesheet' type='text/css' href='styles/page.css' />
<script type='text/javascript' src='javascript/sortable.js'></script>
</head>

<body>
<table class='sortable' id='sortabletable' rules=cols>
<thead><tr><th>Name</th>
<th>Total Hires</th>
<th>Client</th>
<th>Sales Manager</th>
<th>Role</th>
<th>Street</th>
<th>City</th>
<th>Zipcode</th>
<th>State</th>
<th>Start Date</th>
<th>Total Gross Margin</th></tr>
<tbody></thead>


  
<?
//output the search results

$result = mysql_query($query);
while($row = mysql_fetch_array($result))

{
echo "<td><a target='_blank' href='http://www.ajasa.com/cams/us_form.php?flow_id=". $row['flow_id'] . "'>" . $row['full_name'] . "</a></td>
<td>" , $row['COUNT(consultant.flow_id)'] , "</td>
<td>" , $row['client_index'] , "</td>
<td>" , $row['sales_manager_index'] , "</td>
<td>" , $row['role'] , "</td>
<td>" , $row['street'] , "</td>
<td>" , $row['city'] , "</td>
<td>" , $row['zip'] , "</td>
<td>" , $row['state'] , "</td>
<td>" , date("m/d/Y", strtotime($row['start_date'])) , "</td>
<td>" , $row['SUM(gross_margin)'] , "</td> </tr>";
}

?>

Psycho · August 9, 2011

Based upon the code you provided I don't see how you are even getting that last line that provides a summation of the values in the query. Did you leave some code out?

EDIT: nm, I see you are using WITH ROLLUP. I don't use that and instead do the summary calculations in PHP. Let me take a look at what gets returned with that.

Psycho · August 9, 2011

Looking at the manual, a ROLLUP will provide a null value for all fields - except those that are GROUP BY modifiers. Not, sure why state is in the summation record. Personally, I would do the summation logic in PHP (it would be less work). Otherwise you have to use if/else logic on all the values that shouldn't be displayed for the summation data. For example, I suspect you are still creating the hyperlink for that last line, but the text of the hyperlink is empty so you don't see the link. That's not a good idea.

I would do the following:

<?php

include("db_connect.php");
include("functions.php");

$_GET=sanitize($_GET);

$query = "SELECT COUNT(consultant.flow_id) AS flow_id_count, consultant.flow_id, full_name, 
			client_index, sales_manager_index, role, street, city, consultant.state, zip, 
			start_date,	SUM(gross_margin) AS gross_margin_sum
	FROM contract, consultant 
	WHERE contract.deleted='false' AND " .  "consultant.deleted='false' AND contract.flow_id=consultant.flow_id 
		AND start_date>=str_to_date('" . $_GET['start_date_begin'] . "', '%m/%d/%Y') 
		AND start_date<=str_to_date('" . $_GET['start_date_end'] . "', '%m/%d/%Y') 
		AND status='active'
		GROUP BY full_name";


?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Hire Report</title>
<link rel='stylesheet' type='text/css' href='styles/page.css' />
<script type='text/javascript' src='javascript/sortable.js'></script>
</head>

<body>
<table class='sortable' id='sortabletable' rules=cols>
<thead><tr><th>Name</th>
<th>Total Hires</th>
<th>Client</th>
<th>Sales Manager</th>
<th>Role</th>
<th>Street</th>
<th>City</th>
<th>Zipcode</th>
<th>State</th>
<th>Start Date</th>
<th>Total Gross Margin</th></tr>
<tbody></thead>


  
<?
//output the search results
$total_count = 0;
$total_margin = 0;
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
    $recordDate = date("m/d/Y", strtotime($row['start_date']));
    echo "<tr>\n";
echo "<td><a target='_blank' href='http://www.ajasa.com/cams/us_form.php?flow_id={$row['flow_id']}'>{$row['full_name']}</a></td>\n";
echo "<td>{$row['flow_id_count']}</td>\n";
echo "<td>{$row['client_index']}</td>\n";
echo "<td>{$row['sales_manager_index']}</td>\n";
echo "<td>{$row['role']}</td>\n";
echo "<td>{$row['street']}</td>\n";
echo "<td>{$row['city']}</td>\n";
echo "<td>{$row['zip'}</td>\n";
echo "<td>{$row['state']}</td>\n";
echo "<td>{$recordDate}</td>\n";
echo "<td>}$row['gross_margin_sum']}</td>\n";
    echo "</tr>\n";

    $total_count += $row['flow_id_count'];
    $total_margin += }$row['gross_margin_sum'];
}

echo "<tr><td></td><td>{$total_count}</td><td colspan=\"8\"></td><td>{$total_margin}</td></tr>\n";
?>

trangbn · August 9, 2011

Wow thanks so much that is exactly what I needed! I had to take some {, [ out but it's virtually the same code. It does work better without the WITH ROLLUP. Do you mind explaining your logic so I can use this as a learning experience and utilize it again? I am new to php and have never used php to do the totals.

Thanks!

Trang

Psycho · August 9, 2011

There really isn't much to the logic. To create a "total" of all the records in the result set, just create a variable(s) to hold the total before you start processing the records. Then, when you process the records, increase the value of that variable(s) based upon the value of the current record. I have added comments to the code and also revised it further to be more structures. I put ALL the processing code at the head of the page and in the HTML I only echo one variable for the results. By structuring your code in this way it gives you a lot more flexibility. For instance you can have one script that gets/processes the data then have different "output" files based on the situation.

Also, I may have had some typos, but the {} were by design. You can put variables within strings that are defined with double quotes. But, in some instances (such as referring to an array value) you need to enclose the variable in {} to have it interpreted correctly.

Lastly, instead of checking the date separately against the begin and end dates separately, look into using the BETWEEN operator for the query.

EDIT: OK, another thing. Don't use the string values "true" and "false" as your database values. use a tinyint type field and store 0 (false) or 1 (true). Those values are logically handled as Boolean true/false. Then you never need to compare the value to a string.

<?php

include("db_connect.php");
include("functions.php");

$_GET=sanitize($_GET);

$query = "SELECT COUNT(consultant.flow_id) AS flow_id_count, consultant.flow_id, full_name, 
                 client_index, sales_manager_index, role, street, city, consultant.state, zip, 
                 start_date, SUM(gross_margin) AS gross_margin_sum
          FROM contract
          JOIN consultant ON contract.flow_id=consultant.flow_id
          WHERE contract.deleted='false'
            AND consultant.deleted='false'
            AND start_date>=str_to_date('{$_GET['start_date_begin']}', '%m/%d/%Y')
            AND start_date<=str_to_date('{$_GET['start_date_end']}', '%m/%d/%Y')
            AND status='active'
          GROUP BY full_name";
$result = mysql_query($query);

//Create vars to store the totals for flow_id count and gross_margin
$total_count = 0;
$total_margin = 0;

//Process the results into HTML output code
$htmlOutput = ''; //Var to hold the html code
while($row = mysql_fetch_array($result))
{
    //Define the record date for display
    $recordDate = date("m/d/Y", strtotime($row['start_date']));
    //Process the record into HTML code and store in output var
    $htmlOutput .= "<tr>\n";
    $htmlOutput .= "<td><a target='_blank' href='http://www.ajasa.com/cams/us_form.php?flow_id={$row['flow_id']}'>{$row['full_name']}</a></td>\n";
    $htmlOutput .= "<td>{$row['flow_id_count']}</td>\n";
    $htmlOutput .= "<td>{$row['client_index']}</td>\n";
    $htmlOutput .= "<td>{$row['sales_manager_index']}</td>\n";
    $htmlOutput .= "<td>{$row['role']}</td>\n";
    $htmlOutput .= "<td>{$row['street']}</td>\n";
    $htmlOutput .= "<td>{$row['city']}</td>\n";
    $htmlOutput .= "<td>{$row['zip'}</td>\n";
    $htmlOutput .= "<td>{$row['state']}</td>\n";
    $htmlOutput .= "<td>{$recordDate}</td>\n";
    $htmlOutput .= "<td>}$row['gross_margin_sum']}</td>\n";
    $htmlOutput .= "</tr>\n";

    //Increate the totals based upon current record values
    $total_count += $row['flow_id_count'];
    $total_margin += }$row['gross_margin_sum'];
}

//Create html output for the final, summary line
$htmlOutput .= "<tr><td></td><td>{$total_count}</td><td colspan=\"8\"></td><td>{$total_margin}</td></tr>\n";

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Hire Report</title>
<link rel='stylesheet' type='text/css' href='styles/page.css' />
<script type='text/javascript' src='javascript/sortable.js'></script>
</head>

<body>
<table class='sortable' id='sortabletable' rules=cols>
    <thead>
    <tr>
    <th>Name</th>
<th>Total Hires</th>
<th>Client</th>
<th>Sales Manager</th>
<th>Role</th>
<th>Street</th>
<th>City</th>
<th>Zipcode</th>
<th>State</th>
<th>Start Date</th>
<th>Total Gross Margin</th>
    </tr>
    </thead>
    <tbody>
    <?php echo $htmlOutput; ?>
    </tbody>
</table>

</body>
</html>

trangbn · August 10, 2011

Thanks for taking the time to do this. It is much appreciated!

Sign In

WHILE loop with BREAK

Recommended Posts

trangbn

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

trangbn

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

trangbn

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information