loop problem - The MySQL Relational Database

Posted by bschultz (bschultz), 30 June 2003

Hi all,

I have a table cell in every table called 'T1'. I have a script that will take the value of T1 across every table and add up the value. Here's that code

Code:

<?php

$DBhost = "";
$DBuser = "";
$DBpass = "";
$DBName = "";

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
mysql_select_db("$DBName") or die("Unable to select database $DBName");

$tablenames = array(list all table names here);
// (fill in the rest of the table names)

$column_name = "T1"; // column you want the database-wide sum of

$overall_sum = 0;
foreach ($tablenames as $table) {
$query = "SELECT SUM(".$column_name.") as sum FROM ".$table." ";
$result_array = mysql_fetch_array(mysql_query($query));
$overall_sum += $result_array[sum];
}

echo "Total: $overall_sum ";

?>

That code needs to be changed slightly to get it to add up multiple table cells

Code:

$column_name = "T1"; // column you want the database-wide sum of

$overall_sum = 0;
foreach ($tablenames as $table) {
$query = "SELECT SUM(".$column_name.") as sum FROM ".$table." ";
$result_array = mysql_fetch_array(mysql_query($query));
$overall_sum += $result_array[sum];
}

echo "Total: $overall_sum ";

// end of cell

$column_name2 = "T2"; // column you want the database-wide sum of

$overall_sum2 = 0;
foreach ($tablenames as $table) {
$query2 = "SELECT SUM(".$column_name2.") as sum FROM ".$table." ";
$result_array2 = mysql_fetch_array(mysql_query($query2));
$overall_sum2 += $result_array2[sum];
}

echo "Total: $overall_sum2 ";

// end of cell

I have 141 cells in this one particular db. I've tried (without much success) to build a loop for this, so that I don't have to copy/paste that copy 141 times.

Here's what I've tried
Code:

$i=0;
while ($i < 141) {

$column_name = "T$i"; // column you want the database-wide sum of
$overall_sum = 0;
foreach ($tablenames as $table) {
$query = "SELECT SUM(".$column_name.") as sum FROM ".$table." ";
$result_array = mysql_fetch_array(mysql_query($query));
$overall_sum += $result_array[sum];

}

++$i;
}

echo "Total: $overall_sum ";

echo "Total: $overall_sum1 ";

echo "Total: $overall_sum2 ";

echo "Total: $overall_sum3 ";
?>

and this
Code:

$i=0;
while ($i < 141) {

"T$i" = mysql_result($result,$i,"T$i");

$column_name$i = "T$i"; // column you want the database-wide sum of
$overall_sum$i = 0;
foreach ($tablenames as $table) {
$query$i = "SELECT SUM(".$column_name$i.") as sum FROM ".$table." ";
$result_array$i = mysql_fetch_array(mysql_query($query$i));
$overall_sum$i += $result_array$i[sum];

}

++$i;
}

?>

and neither have worked...Am I even on the right track?

Brian

Posted by admin (Graham Ellis), 30 June 2003

You're on the right track, but not quite there - you need to put your outputs into an array too - such as $overall_sum[$i] (note the extra square brackets. Then use a loop (or whatever) to print those out later in your code.

Posted by waygood (waygood), 30 June 2003

Dont forget that your queries need to be valid, so if the fields dont exist in the table the code will stop.

The following will handle this as it reads the table structure to see if the field exists first.
Quote:

$tables=array("table1","table2","table3");
$overall_sum = 0;

reset($tables);
while (list ($table_key, $table_val) = each ($tables))
{
$describe_sql="DESCRIBE ".$tables[$table_key];
$describe_result=array_from_mysql_query($describe_sql);

reset($describe_result);
while (list ($field_key, $field_val) = each ($describe_result))
{
// insert the loop for T1-147 here
if($describe_result[$field_key]['Field']=="T1")
{
// field name exists in table so process the SUM of it
$sum_sql="SELECT SUM(" . $describe_result[$field_key]['Field'] . ") AS sum FROM " . $tables[$table_key];
$sum_result=array_from_mysql_query($sum_sql);
$overall_sum+=$sum_result[0]['sum'];
}
// and end it here
}
}

I cant be bothered to do the code for looping through the field names as I am about to finish and go home.

array_from_mysql_query is my own function for handling retrieving an array from a query, as it does other things aswell. Replace it with your own and change the referencing as required.

Posted by bschultz (bschultz), 30 June 2003

Waygood,

Thanks for the code...I'll give it a try. I've been trying this though, and it's gotten me one step closer, but it has other problems:

Code:

------------

but it just keeps pooping up the following:

Total:
Total:
Total:
Total:
Total:
Total:
Total:
-------------

severall THOUSAND times and then this message is at the bottom:

Fatal error: Maximum execution time of 30 seconds exceeded in \apache\htdocs\php\math.php on line 32

There are only 15 rows in each table...and there are 25 tables...can anyone get me looking in the right direction as to what is wrong?

Brian

Posted by waygood (waygood), 1 July 2003

you are running your queries wrong.

mysql_query returns a reference to the results from the query.
When you do mysql_fetch_array, mysql_fetch_row or other such functions, you reference the result from the query, and not run the query again. In effect you will never get to the second row, as you keep re-initializing it.

you need to :-
$sql="SELECT * FROM table";
$reference=mysql_query($sql);

while($row = mysql_fetch_array($reference))
{
echo "Total: ".$row['T1']." ";
$i++;
}

Posted by waygood (waygood), 1 July 2003

There was a few faults with your code, but here is a re-write
Code:

<?php

$DBhost = "";
$DBuser = "";
$DBpass = "";
$DBName = "";

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
mysql_select_db("$DBName") or die("Unable to select database $DBName");

$tablenames = array("bsocc_8_26", "bsocc_8_28", "bsocc_9_2", "bsocc_9_9",
"bsocc_9_11", "bsocc_9_15", "bsocc_9_16", "bsocc_9_18", "bsocc_9_22",
"bsocc_9_23", "bsocc_9_25", "bsocc_9_27", "bsocc_9_29", "bsocc_10_2",
"bsocc_10_4", "bsocc_10_7", "bsocc_playoffs_1", "bsocc_playoffs_2",
"bsocc_playoffs_3", "bsocc_playoffs_4", "bsocc_playoffs_5", "bsocc_playoffs_6",
"bsocc_playoffs_7", "bsocc_playoffs_8", "bsocc_playoffs_9", "bsocc_playoffs_10");
// (fill in the rest of the table names)

$overall_sum = 0;
//** seperate answers : $overall_sum = array(0, 0, 0, 0, 0, 0, 0 ,0 ,0 ,0 ,0 ,0 ,0, 0, 0);
$base_query="SELECT SUM(T1), SUM(T2), SUM(T3), SUM(T4), SUM(T5), SUM(T6), SUM(T7), SUM(T8), SUM(T9), SUM(T10), SUM(T11), SUM(T12), SUM(T13), SUM(T14), SUM(T15) FROM ";

foreach ($tablenames as $table)
{
$query = $base_query.$table;

// only 1 row will ever be returned and we only use it once, so it is okay to do this
$result_array = mysql_fetch_row(mysql_query($query));
$table_sum=0;

//step through each of the rows and add them up to give a total for the whole table
for($loop=0;$loop<sizeof($result_array);$loop++)
{
$table_sum+= $result_array[$loop];
//** seperate answers : accumulated by using $table_sum[$loop]+=$result_array[$loop];
}
$overall_sum += $table_sum;
echo "Table ".$table." sum is : ".$table_sum." \n";
}
echo " \n";
echo "Overall Total: ".$overall_sum." \n";
//** use loop to step through totals array and output each answer.
?>

Posted by bschultz (bschultz), 30 July 2003

I've added to the problem here...I now want 270 colums added up. That means that I will have to simplfy the solution that waygood provided to me (thanks, by the way!)

I'm getting a parse error on the last row of the script. Here is the part that I'm building the loop for.

Code:

for($a = 1; $a < 270; $a++){
for($b = 1; $b < 270; $b++){
for($c = 1; $c < 270; $c++){
for($d = 1; $d < 270; $d++){
for($e = 1; $e < 270; $e++){
for($f = 1; $f < 270; $f++){
for($g = 1; $g < 270; $g++){
for($h = 1; $h < 270; $h++){
for($i = 1; $i < 270; $i++){
{
}
$column_name[$a] = "T[$b]"; // column you want the database-wide sum of

$overall_sum[$c] = 0;
foreach ($tablenames as $table) {
$query[$d] = "SELECT SUM(".$column_name[$e].") as sum FROM ".$table." ";
$result_array[$f] = mysql_fetch_array(mysql_query($query[$g]));
$overall_sum[$h] += $result_array[$i][sum];
}

// end of cell

?>

I'm starting to get VERY frustrated with this. When I get frustrated, I don't think properly...anything jump out at anyone?

Posted by admin (Graham Ellis), 30 July 2003

Wow - 9 nested loops. You'll be running the inner code about 7625597484987000000000 times. I don't think that's what you want

...

Syntax errors at the very end of a script in PHP are usually caused by blocks not being closed - you need a lot of } characters to match the {s you have on the end of each for ... but I think that's the symptom and I think you're logic is flawed somewhere - sorry. I would guess that most of your loops are redundant (I certainly hope so), and some of the variable should be refereneced multiple times in the inner code loop(s).

Posted by bschultz (bschultz), 30 July 2003

I swear it would be easier and quicker for me to copy and paste this code
--------------
$column_name = "T1"; // column you want the database-wide sum of

$overall_sum = 0;
foreach ($tablenames as $table) {
$query = "SELECT SUM(".$column_name.") as sum FROM ".$table." ";
$result_array = mysql_fetch_array(mysql_query($query));
$overall_sum += $result_array[sum];
}

echo "Total: $overall_sum ";
--------------------------

270 times and manually change the variable names!

Posted by admin (Graham Ellis), 30 July 2003

If it's that code 270 times, then you need just ONE loop around it going from 0 to 269 (or from 1 to 270)

Code:

for ($colnum =1; $colnum <=270; $colnum++) {
$column_name = "T".$colnum;

(The rest of your code)

}

Are you looking for a single super-total or 270 totals, one for each time around the loop? If a single super-total is needed, move
$overall_sum = 0;
above the loop I've just given you. If you want an array of 270 totals, change each reference to $overall_sum in your existing code to $overall_sum[$colnum].

This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.