How to calculate the sum of number array elements from multiple database rows using php?

Question

I need to count the number of elements that exist in the column of a MySQL database row for multiple rows and them add them all together. This is the code I used to count the number of array elements (which are numbers separated by commas):

$result = substr_count($count_fetch['micro_analysis'], ",") + 1;

But now I need to do this for each row, which could vary depending on the query. I need to add the $result of each row together to get a final sum of all the rows. I used the following code but I get the incorrect value, can someone please point me in the right direction?

$sql = "SELECT * FROM samples_database WHERE order_id = $order_id";

      $count_query = mysqli_query($conn, $sql);
      $count_fetch = mysqli_fetch_assoc($count_query);

      foreach ($count_fetch as $row) {
         $result = substr_count($count_fetch['micro_analysis'], ",") + 1;
         $end_result += $result;

      }

      echo $end_result;

Can you not use MySQL COUNT() or SUM() functions to do the math for you? — T. Altena
– T. Altena, Commented Dec 10, 2018 at 9:55

Nigel Ren · Accepted Answer · 2018-12-10 10:04:23Z

1

You are just fetching 1 row and then trying to count over that row, instead you need to loop over the rows and add the fields in that...

  $sql = "SELECT * FROM samples_database WHERE order_id = $order_id";

  $end_result = 0;
  $count_query = mysqli_query($conn, $sql);
  while( $row = mysqli_fetch_assoc($count_query)) {
     $end_result += substr_count($row['micro_analysis'], ",") + 1;
  }

  echo $end_result;

answered Dec 10, 2018 at 10:04

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

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

Comments

oreopot · Accepted Answer · 2018-12-10 10:07:00Z

1

Replace your code with the following:

$sql = "SELECT * FROM samples_database WHERE order_id = $order_id";

      $count_query = mysqli_query($conn, $sql);
      $count_fetch = mysqli_fetch_assoc($count_query);
      $end_result=0;//initialize variable to 0
      foreach ($count_fetch as $k => $row) {
         if($k == 'micro_analysis'){
           $result = substr_count($row, ",") + 1; //change over here
           $end_result += $result;
         }
      }

      echo $end_result;

edited Dec 10, 2018 at 10:07

answered Dec 10, 2018 at 9:56

oreopot

3,4592 gold badges23 silver badges31 bronze badges

6 Comments

Roelof Coertze Over a year ago

I get an error ` Illegal string offset 'micro_analysis'`

oreopot Over a year ago

before foreach

oreopot Over a year ago

add the following code: echo "<pre>"; print_r($count_fetch);die();

Roelof Coertze Over a year ago

Array (     [sample_id] => 16     [env_sam_id] => 181207-9-1     [order_id] => 21     [c_sam_id] => Test     [sample_group] => water     [sample_type] => Test     [sample_condition] => Test     [sample_temp] => Test     [storage_location] => 1     [s_status] => Analysis Pending     [s_time1] => 17:36:11     [s_date1] => 2018-12-07     [micro_analysis] => 1,2,3     [chem_analysis] => 1,2 )

Roelof Coertze Over a year ago

For some reason it provides the incorrect answer. The answer that @Nigel Ren gave worked for me. Thank you anyways!

|

Collectives™ on Stack Overflow

How to calculate the sum of number array elements from multiple database rows using php?

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related