Nested While with two tables 1

[lexer]

Hi all,

Please, If somebody can help me with this, I want to use two nested while with two Mysql tables, These are the Mysql Tables:

register Table:

Ext Elapse cost number
0 5 250 02762083002
210 1 30 2342231
236 10 300 2050198
210 1 30 54192223
0 2 60 7400380
0 4 200 02762083002
210 2 60 2342232
236 5 150 2050199

structure Table:

Ext
0
204
222
226
236
210

I want to search for every Ext field in the structure Table that appears in the register Table and totalize the information for every Ext field in structure table (I want to get totalelapse and totalcost), For doing that I’m using two Mysql Querys and Two PHP nested While, This is my PHP program:


<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("calls", $con);

// First Query register table
$query = "SELECT register.Ext, register.elapse, register.cost, register.number, structure.Ext ".
"FROM register, structure ".
"WHERE register.Ext = structure.Ext ORDER BY structure.Ext ";

// Second Query structure table
$query2 = "SELECT structure.Ext ".
"FROM structure ".
"ORDER BY structure.Ext ";

//$row_co = mysql_fetch_assoc($con);
$result = mysql_query($query) or die(mysql_error());
$result2 = mysql_query($query2) or die(mysql_error());

// First While for Selecting Ext from structure
while($row = mysql_fetch_array($result2)){
// Second While to search for fields on register
while($row = mysql_fetch_array($result)){

//If structure.Ext = register.Ext totalize Elapse and Cost for each Ext
if ($row['structure.Ext'] == $row['register.Ext'])
{
echo $row['Ext']. " - ". $row['number']. " - ". $row['elapse']. " - ". $row['cost'];
echo "<br />";
//Totalizing Elapse
$totalelapse=$row['elapse'] + $totalelapse;
//Totalizing Cost
$totalcost=$row['cost'] + $totalcost;
}
}
echo "Total Elapse ". $totalelapse. " - "."Total Cost ".$totalcost ;
echo "<br />";
$totalelapse=0;
$totalcost=0;
}
?>


This is the output fot the above program:

0 - 02762083002 - 5 - 250
0 - 7400380 - 2 - 60
0 - 02762083002 - 4 - 200
210 - 2342232 - 2 - 60
210 - 2342231 - 1 - 30
210 - 54192223 - 1 - 30
236 - 2050199 - 4 - 120
236 - 2050198 - 10 - 300
Total Elapse 29 - Total Cost 1050
Total Elapse 0 - Total Cost 0
Total Elapse 0 - Total Cost 0
Total Elapse 0 - Total Cost 0
Total Elapse 0 - Total Cost 0
Total Elapse 0 - Total Cost 0
Total Elapse 0 - Total Cost 0
Total Elapse 0 - Total Cost 0

This should be the output that I think I would get from the above program:

Ext Duracion Costo numero
0 5 250 02762083002
0 2 60 7400380
0 4 200 02762083002
Total 11 510
210 1 30 2342231
210 1 30 54192223
210 2 60 2342232
Total 4 120
236 10 300 2050198
236 5 150 2050199
Total 19 570

I dont know If the nested while's and querys are Ok?

 

[jpadie]

do you need the data as well as the summary?

 

[lexer]

Yes jpadie, I need to see the detail data and the summary

 

[jpadie]

would this work for you?

<?php
$con = mysql_connect("localhost","root","");
if (!$con) {
	die('Could not connect: ' . mysql_error());
}

mysql_select_db("calls", $con) or die(mysql_error());

$query = "	SELECT 
				structure.Ext as a,
				register.elapse as b, 
				register.cost as c, 
				register.number as n
			FROM 
				structure
			JOIN
				(register) 
				ON 
				(register.Ext = structure.Ext)
			ORDER BY 
				structure.Ext ASC";

$result = mysql_query($query) or die(mysql_error());

$output = array();
while($row = mysql_fetch_assoc($result)){
	$output[$row['a']]['data'][] = array('elapse'=>$row['b'], 'cost'=>$row['c'], 'number'=>$row['n']);
	if (!isset($output[$row['a']]['totalelapse'])) $output[$row['a']]['totalelapse'] = 0;
	if (!isset($output[$row['a']]['totalcost'])) $output[$row['a']]['totalcost'] = 0;
	$output[$row['a']]['totalelapse'] = $row['b'] + $output[$row['a']]['totalelapse'];
	$output[$row['a']]['totalcost'] = $row['c'] + $output[$row['a']]['totalcost'];
}
echo "<pre>" . print_r($output, true) . "</pre>";
//i'm sure you can format the output on your own!!
?>

 

[lexer]

Thanks jpadie, I tried the program and It works!!, I`m going to try to format the output as I want it