how should i write this code on calculating with php from an sql database

Question

I am trying to calculate sales of items using some data to get the total from a SQL database using its id to identify each sale and then display it on the screen. for further use.

<?php
$dbhost = 'localhost:3306';
                $dbuser = 'root';
                $dbpass = 'mysql';
                $conn = mysql_connect($dbhost, $dbuser, $dbpass);
                if (! $conn ) {
                die('Could not connect: ' . mysql_error());
                }

    $sql = "select item_num * price + tp AS total from buyers where 
 Ref_code = '" . $result16['ref_code'] . "' ";

                mysql_select_db('sales');

  $retval1 = mysql_query($sql, $conn);

  if (!$retval1) { 
die('Could not get data: ' . mysql_error());
}


 $result15 = mysql_fetch_assoc($retval1); 
?>

 <?php echo $result15['total']; ?> '

I expect it to display the total of each sale as I passed the id to the Query but it is not display anything.

You should not use the `mysql_` functions, see: https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — atymic, Jul 21 '19 at 01:42
Try to print the contents of the $sql variable. It may be an issue with the query — Nadir Latif, Jul 21 '19 at 02:11

Shivendra Singh · Answer 1 · 2019-07-21T09:20:43.367

1) You can store a calculated value in a defined variable '@total' and refer to select query. For more info check - https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

Query will be like.

$sql = "SELECT 
@total := b.item_num * b.price + b.tp as total
FROM buyers b
where 
Ref_code = '" . $result16['ref_code'] . "' ";

2) I think you should use mysqli insted of mysql. For More info - Why shouldn't I use mysql_* functions in PHP?

3) and also need to use prepared statements to prevent SQL Injection problem, For More info check - https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

you can check the below example.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

//store a calculated value in a defined variable total and use in select statement
$query = "SELECT 
@total := b.item_num * b.price + b.tp as total
FROM buyers b
where 
Ref_code = ?";

$ref_code = $result16['ref_code'];
if($stmt = $conn->prepare($query)) {

   $stmt->bind_param("s", $ref_code); 
   $stmt->execute(); 

   /*Binds variables to a prepared statement for result storage
     for more detailscheck -https://www.php.net/manual/en/mysqli-stmt.bind-result.php
     */
   $stmt->bind_result($total);

   while ($stmt->fetch()) {
     echo "total: $total\n";
   }
}

$stmt->close();

score -1 · Answer 2 · answered Jul 28 '19 at 23:47

After so many tries I came across a market cart, then I saw my mistake which is the SQL variable, I use varchar to store my data so the query was not seeing an int or float data so it could not calculate the value and give the desired result. After I change my SQL variable to double then using the formula which I try before now, it worked.

           <?php       
        $sql1 = "select * from buyers where Ref_code = '" . mysql_insert_id() . "' ";
                  mysql_select_db('sales');
                                        $retval1 = mysql_query($sql1, $conn);
                                        if (!$retval1) {
                                            die('Could not get data: ' . 
   mysql_error());
                                        }


                                        $result16 = mysql_fetch_assoc($retval1);


            }





                ?>
                <?php 


  $num1 = $result16['price'];
  $num2 = $result16['item_num'];
  $num3 = $result16['tp'];
  global $num1;
  global $num2;
  global $num3;
  function total()
 {
  global $num1;
  global $num2;
  global $num3;
 $sum1 = $num1 * $num2;
 $sum = $sum1 + $num3;
 echo "$sum";
 }
                 ?>
                <div id ="recipt">
               <p>
   Price: N<?php echo $result16['price'];?> <br><br>

  <b>Total Amount of Item: <u>N<?php total() ?> </u></b> 
    </p>   
   </div>

how should i write this code on calculating with php from an sql database

2 Answers2