Functions.php

General PHP Help
#1

I was using the following code repeatedly


$query="SELECT * FROM holdings WHERE type='14'";
$result = mysqli_query($conn, $query) or die('Unable to shares'.mysqli_error($conn));
while ($row = mysqli_fetch_assoc($result)) {
    $code=$row['name'];
    $id=$row['id_holding'];
    

    $query_buy="SELECT * FROM buy WHERE id_holding=$id";
      $result_buy = mysqli_query($conn, $query_buy) or die('Unable to shares'.mysqli_error($conn));
      while ($row_buy = mysqli_fetch_assoc($result_buy)) {
      $buy_qty=$row_buy['buy_qty'];
      $buy_rate=$row_buy['buy_rate'];
      $buy_total=$buy_rate*$buy_qty;
      $n++;
      echo "<table><td>$n.$code.</td>";
        echo  "<td>".number_format($buy_qty,0)."</td>";
        echo  "<td style='text-align: right;'>".number_format($buy_rate,2)."</td>";
        echo  "<td  style='text-align: right;'>".number_format(($buy_total),2)."</td>";
        $buy_total_share+=$buy_total;
        echo "<td>".$buy_total_share."</table>";        
}

}

so I made it a function as follows

   
        function buyrate($id) {
          $conn = mysqli_connect('localhost', 'root', '12May@61', 'investments');
          
            $query_buy="SELECT * FROM buy WHERE id_holding=$id";
              $result_buy = mysqli_query($conn, $query_buy) or die('Unable to shares'.mysqli_error($conn));
              while ($row_buy = mysqli_fetch_assoc($result_buy)) {
              $buy_qty=$row_buy['buy_qty'];
              $buy_rate=$row_buy['buy_rate'];
              $buy_total=$buy_rate*$buy_qty;
                echo  "<td><center>".number_format($buy_qty,0)."</td>";
                echo  "<td style='text-align: right;'>".number_format($buy_rate,2)."</td>";
                echo  "<td  style='text-align: right;'>".number_format(($buy_total),2)."</td>";
                $buy_total_share+=$buy_total;
                $_SESSION['buy_total_share']=$buy_total_share;
        }

the only thing that is not working is “$buy_total_share+=$buy_total;”
in the first instance, it does the totaling correctly bu as a function It simply returns the last value!
also I have to add the $conn statement in every function and the include config.php does not work!
Help Please

#2

here are some general points about the code -

  1. don’t run queries inside of loops or call a function that preforms a query inside of a loop. this is extremely inefficient. use a single (LEFT) JOIN query to get all the data that you want at one time
  2. don’t use or die(…) for error handling. you should use exceptions for database error handling (this is the default setting now in php8+)
  3. don’t use SELECT *. list out the columns you are selecting in a query
  4. don’t copy variables to other variables for nothing. just use the original variables that data is in
  5. you can calculate the buy_total in the sql query
  6. your html markup is broken/incomplete. you need to validate the resulting web pages at validator.w3.org

some function specific points -

  1. your application should make one database connection, then supply it to any function that needs it as a call-time parameter
  2. you should separate the database specific code, that knows how to query for and fetch data, from the presentation code, that knows how to produce the output from the data. when using functions, these should be performed by separate functions
  3. functions should return the result they produce to the calling code
  4. the reason the buy_total_share isn’t working is because you are starting over with a new local $buy_total_share variable in each function call. if you separate the code as suggested, you can calculate the sum by operating on the fetched data all at one time, using php’s built-in array functions.
#3

Just learnt about LEFT JOIN thanks,
understood rest. Thanks again for pointing out these errors, will keep them in mind.
However your reply about the function has left me totally confused, would you kindly give an example so I can understand? How do I use the array function to do the addition?
Thanks again

#4

If you calculate the buy_total in the query and fetch all the rows of data into a php variable, e.g. $data, you can calculate the buy_total_share using -

$buy_total_share = array_sum(array_column($data,'buy_total'));
#5

$buy_total_share = array_sum(array_column($data,‘buy_total’)); should the
‘buy_total’ be ‘$buy_total’?
I think I am missing some step

Please see the revised code, this gives the total correctly as it should for each “$buy_total=$buy_rate*$buy_qty;” to the array

How do I sum this?

function buyrate($id) {
          $total=[];
          $conn = mysqli_connect('localhost', 'root', '12May@61', 'investments');
           $query_buy="SELECT * FROM buy WHERE id_holding=$id";
              $result_buy = mysqli_query($conn, $query_buy) ;
              while ($row_buy = mysqli_fetch_array($result_buy)) {
              $buy_qty=$row_buy['buy_qty'];
              $buy_rate=$row_buy['buy_rate'];
              $buy_total=$buy_rate*$buy_qty;
                 }
          array_push($total,$buy_total);
         $total=array_sum($total);
          echo $total.<"br">;
#6

As previously posted -

Note: mysqli has a fetch_all() statement. No looping is required to fetch the data.

Sponsor our Newsletter | Privacy Policy | Terms of Service