Not summing whole column

Question

For reason it's not calculating the SUM whole column of first_payment. I tried "SELECT SUM(first_payment) FROM \Customer Information``, it's returning as unidentified index first_payment. Any reason why?

    <?php
    error_reporting(E_ALL);
    ini_set("display_errors", 1);


    $pay=$_REQUEST['payment']; 

    // MySQL database connection, username, password, database name
    $con=mysqli_connect("localhost","user","pass","db_name");

    // Check connection
    if (mysqli_connect_errno()) {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    if ($pay=="first") 
      $query="SELECT *, SUM(first_payment) FROM `Customer Information`";
else if ($pay=="second") 
      $query="SELECT *, SUM(second_payment) FROM `Customer Information`";
else if ($pay=="third") 
      $query="SELECT *, SUM(third_payment) FROM `Customer Information`";
else if ($pay=="fourth") 
      $query="SELECT *, SUM(fourth_payment) FROM `Customer Information`";


    $result = mysqli_query($con,$query);

    // Executing and error checking of query
    if (!mysqli_query($con,$query)) {
      die('Error: ' . mysqli_error($con));
    }


    while ($row = mysqli_fetch_array($result)) {
    switch($pay)
    {
       case "first":
          echo $row['first_payment'];
          break;
       case "second":
          echo $row['second_payment'];
          break;
       case "third":
          echo $row['third_payment'];
          break;
       case "fourth":
          echo $row['fourth_payment'];
          break;
       default:
          echo "nothing selected";
    }
    }
    mysqli_close($con);
    ?>

Answer 1

1. Use MYSQLI_ASSOC when you are fetching your array from query:

   mysqli_fetch_array($result,MYSQLI_ASSOC);

   OR use mysqli_fetch_assoc() function:

   mysqli_fetch_assoc($result)

2. Use alias for summary column in your query:

For example:

"SELECT *, SUM(first_payment) AS SUMMARY  FROM `Customer Information`";

Also, it would be better to avoid unnecessary connections to the database by checking $pay variable before any action, like this:

<?php
error_reporting(E_ALL);
ini_set("display_errors", 1);

$pay=$_REQUEST['payment']; 
// first of all: let's check $pay variable:
switch($pay)
{
   case "first":
      $row_index = 'first_payment';
      break;
   case "second":
      $row_index = 'second_payment';
      break;
   case "third":
      $row_index = 'third_payment';
      break;
   case "fourth":
      $row_index = 'fourth_payment';
      break;
   default:
      echo "nothing selected";
      return;  // return if nothing is selected, no need to run any database manipulations.
}

// MySQL database connection, username, password, database name
$con=mysqli_connect("localhost","user","pass","db_name");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// prepare query:
$query="SELECT *, SUM(".$row_index.") AS SUMMARY  FROM `Customer Information`";

// Executing and error checking of query
if (!$result = mysqli_query($con,$query)) {
  die('Error: ' . mysqli_error($con));
}
while ($row = mysqli_fetch_array($result,MYSQLI_ASSOC)) {
  echo $row['SUMMARY'];
}
mysqli_free_result($result); // don't forget to free query results
mysqli_close($con);
?>

Answer 2

Further to the answer provided above, you have to know what went wrong:

In your select statement, you selected the sum of the field not the field itself which you tried to retrieve its result, hence the error undefined index...

As the answer above, give an alias to the sum and use it in the while loop to retrieve it.

Answer 3

Change your queries and provide the correct alias names for the sum amount which you use in the code.Try this

if ($pay=="first") 
   $query="SELECT *, SUM(first_payment) AS first_payment FROM `Customer Information`";
else if ($pay=="second") 
   $query="SELECT *, SUM(second_payment) AS second_payment FROM `Customer Information`";
else if ($pay=="third") 
   $query="SELECT *, SUM(third_payment) AS third_payment FROM `Customer Information`";
else if ($pay=="fourth") 
   $query="SELECT *, SUM(fourth_payment) AS fourth_payment FROM `Customer Information`";