calculations on specific rows in table of database using php

Question

i'm working on a php project that manages teachers in school. but i'm stuck with a problem, i have two tables in my database, first one T1 has on row, second T2 has multiple rows, but they have the same columns number. in a third table T3 i need to fill a column with the total of (cell1 of T1*cell1 of T2) + (cell2 of T1*cell2 of T2)+ (cell3 of T1*cell3 of T2)....to the last column i just couldn't find the right way to do this

this is the part that shows the tables from my db

<?php
$host="localhost";
$user="root";
$pass="";
$bdd="test";
$cnx=mysql_connect($host,$user,$pass);
if(!$cnx)
	echo"connexion echouee"."</br>";
else
	echo"connexion reussie"."</br>";

if (mysql_select_db($bdd))
	echo"base de donnees trouvee"."</br>";
else
	echo"base de donnees introuvable"."</br>";

  $req1="SELECT * FROM `table1`";
  $res1=mysql_query("$req1");
  // printing table rows
  while($row1 = mysql_fetch_row($res1))
  {
      echo "<tr>";
      foreach($row1 as $cell1)
          echo "<td>|$cell1|</td>";
      echo "</tr>";  echo"</br>";
  }
echo "_____</br>";
  $req2="SELECT * FROM `table2`";
  $res2=mysql_query("$req2");
  // printing table rows
  while($row2 = mysql_fetch_row($res2))
  {
      echo "<tr>";
      foreach($row2 as $cell2)
          echo "<td>|$cell2|</td>";
      echo "</tr>";echo"</br>";
      
  }
?>

i intend to use php , but i need mysql to get the content of the tables — Douae Lhrx
– Douae Lhrx, Commented Apr 10, 2019 at 10:56
It's worth noting that the mysql_* family of functions no longer exist in any supported version of PHP. You should probably be using PDO or at a minimum mysqli. Any tutorial using mysql_connect is horribly out of date. — samlev
– samlev, Commented Apr 10, 2019 at 21:28

DinoCoderSaurus · Accepted Answer · 2019-04-10 12:59:31Z

1

As long as it is guaranteed that table1 will return 1 row, here is a suggestion:

Instead of using a while loop to fetch the contents, just fetch the row, so the contents of table1 are in $row1
Change foreach($row2 as $cell2) to a foreach($row2 as $key=>$value) format. This way you will have the index of the corresponding element in $row1
Inside the foreach($row2 as $key=>$value) loop, use an accumulator to calculate "Column I". E.G. $tot += $value * $row1[$key]
"echo" the accumulater column before the </tr>

You also probably want to add an empty <td> in the $row1 loop to make sure that all the rows have the same number of columns.

answered Apr 10, 2019 at 12:59

DinoCoderSaurus

6,5502 gold badges13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ankhzet · Accepted Answer · 2019-04-10 13:00:46Z

0

You can traverse second table and calculate total with nested loop:

$res1 = mysql_query("SELECT * FROM `table1`");
$res2 = mysql_query("SELECT * FROM `table2`");

$row1 = mysql_fetch_row($res1);
$row = 0;

// for each row of second table
while ($row2 = mysql_fetch_row($res2)) {
    $row++;
    $total = 0;

    // for each column of first table's row
    foreach ($row1 as $index => $table1RowValue) {
        // get value of same column in the second table's row
        $table2RowValue = $row2[$index];

        // calculate aggregated value
        $total += $table1RowValue * $table2RowValue;
    }

    // print result
    echo "Line $row: $total</br>";  
}

answered Apr 10, 2019 at 13:00

ankhzet

2,5991 gold badge27 silver badges36 bronze badges

1 Comment

ankhzet Over a year ago

Your'e welcome. Also, consider this comment from other SO user: stackoverflow.com/questions/55610616/…

Collectives™ on Stack Overflow

calculations on specific rows in table of database using php

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related