I'm trying to read the taxrate from the database table and use it with values from another table. the database and table are correct as is the field (taxrate) from the table, value is 0.06. Then I'm trying to multiply that value by the value of a field from another table (charges) then update the table . since it doesn't update, I tried echoing the two values but 0 is displayed for both. Will someone advise me?

<?php
mysql_connect("localhost","root","");
mysql_select_db(numbersdb) or die( "Unable to select database"); 
$query = "SELECT taxrate FROM numbdata ";
mysql_fetch_assoc(mysql_query($query));
 $result=mysql_query($query);
// include("getpercent.php");
$taxrate = $_POST['taxrate']; 
echo "taxrate ".$data['taxrate'];

mysql_connect(localhost,root,"");
mysql_select_db(oodb) or die( "Unable to select database");
$query = "SELECT id, tax,charges,datediff(curdate(),duedate) AS dayslate FROM oocust WHERE pd = ' '";
mysql_fetch_assoc(mysql_query($query));
 $result=mysql_query($query);
while($row=mysql_fetch_array($result))
     {
$id=$row['id']; 
$amtdue = $row['amtdue'];
$shipamt = $row['shipamt'];
$charges = $row['charges'];
$tax = $charges * $taxrate;
$amtdue = $charges + $tax + $shipamt;
echo "tax is $tax <br /><br />";
$days_late = ($row['dayslate'] > 0)?$row['dayslate'] : 0; 
$sql = "UPDATE oocust SET tax = " . $tax . ", amtdue = " . $amtdue . ", dayslate = " . $days_late . " WHERE 
id='$id'";
mysql_query($sql) ;
$err=mysql_error();
if($err!="")
     {
  echo "Error in $sql: $err\n";
      }
  }
echo "Invoice Prep completed";
 ?>

1) Why there is two time mysql_connect
2) what is $data, it is nevwe defined above code.

$taxrate = $_POST['taxrate'];
  echo "taxrate " . $data['taxrate'];

3)there should be some variable which hold mysql_fetch_assoc result.

mysql_fetch_assoc(mysql_query($query));

4) so finaly

<?php
  mysql_connect("localhost", "root", "");
  mysql_select_db(numbersdb) or die("Unable to select database");
  
  $query = "SELECT taxrate FROM numbdata ";
  $result = mysql_fetch_assoc(mysql_query($query)); 
  $taxrate = $result['taxrate'];
  echo "taxrate " . $taxrate;  
 
  $query = "SELECT id, tax,charges,datediff(curdate(),duedate) AS dayslate FROM oocust WHERE pd = ' '"; 
  $result = mysql_query($query);
  while ($row = mysql_fetch_array($result))
  {
      $id = $row['id'];
      $amtdue = $row['amtdue'];
      $shipamt = $row['shipamt'];
      $charges = $row['charges'];
      $tax = $charges * $taxrate;
      $amtdue = $charges + $tax + $shipamt;
      echo "tax is $tax <br /><br />";
      $days_late = ($row['dayslate'] > 0) ? $row['dayslate'] : 0;
      $sql = "UPDATE oocust SET tax = " . $tax . ", amtdue = " . $amtdue . ", dayslate = " . $days_late . " WHERE 
id='$id'";
      mysql_query($sql);
      $err = mysql_error();
      if ($err != "")
      {
          echo "Error in $sql: $err\n";
      }
  }
  echo "Invoice Prep completed";
?>

1) Why there is two time mysql_connect
2) what is $data, it is nevwe defined above code.

$taxrate = $_POST['taxrate'];
  echo "taxrate " . $data['taxrate'];

3)there should be some variable which hold mysql_fetch_assoc result.

mysql_fetch_assoc(mysql_query($query));

4) so finaly

<?php
  mysql_connect("localhost", "root", "");
  mysql_select_db(numbersdb) or die("Unable to select database");
  
  $query = "SELECT taxrate FROM numbdata ";
  $result = mysql_fetch_assoc(mysql_query($query)); 
  $taxrate = $result['taxrate'];
  echo "taxrate " . $taxrate;  
 
  $query = "SELECT id, tax,charges,datediff(curdate(),duedate) AS dayslate FROM oocust WHERE pd = ' '"; 
  $result = mysql_query($query);
  while ($row = mysql_fetch_array($result))
  {
      $id = $row['id'];
      $amtdue = $row['amtdue'];
      $shipamt = $row['shipamt'];
      $charges = $row['charges'];
      $tax = $charges * $taxrate;
      $amtdue = $charges + $tax + $shipamt;
      echo "tax is $tax <br /><br />";
      $days_late = ($row['dayslate'] > 0) ? $row['dayslate'] : 0;
      $sql = "UPDATE oocust SET tax = " . $tax . ", amtdue = " . $amtdue . ", dayslate = " . $days_late . " WHERE 
id='$id'";
      mysql_query($sql);
      $err = mysql_error();
      if ($err != "")
      {
          echo "Error in $sql: $err\n";
      }
  }
  echo "Invoice Prep completed";
?>

I get the below message:

taxrate 0.06
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\invoice\invcalc.php on line 9
Invoice Prep completed

I get the following echos telling me that I'm getting the values from both databases but no update:
tax is 9.6
amtdue is 169.6
tax is 5.28
amtdue is 110.28
tax is 3.6
amtdue is 73.6
tax is 1.908
amtdue is 43.708
Invoice Prep completed

<?php 
mysql_connect("localhost", "root", "");  
mysql_select_db(numbersdb) or die("Unable to select database");   
$query = "SELECT taxrate FROM numbdata" ;  
$result = mysql_fetch_assoc(mysql_query($query));   
$taxrate = $result['taxrate'];  
mysql_connect("localhost", "root", ""); 
mysql_select_db(oodb) or die("Unable to select database");   
$query = "SELECT id, shipamt, duedate, charges, dayslate, tax, amtdue FROM oocust WHERE pd = '  '";   
$result=mysql_query($query);
$num=mysql_numrows($result);
while($row = mysql_fetch_array($result))
   {
         $id = $row['id']; 
$shipamt = $row['shipamt'];   
$duedate = $row['duedate'];
$charges = $row['charges'];
$dayslate = $row['dayslate'];
        $tax = $row['tax']; 
 $amtdue = $row['amtdue'];      
$tax = $charges * $taxrate;     
 $amtdue = $charges + $tax + $shipamt; 
$sql = "UPDATE oocust SET
 tax = '" . mysql_real_escape_string($_POST['tax']) . "',
 amtdue = '" . mysql_real_escape_string($_POST['amtdue']) . "',
dayslate = '" . mysql_real_escape_string($_POST['dayslate']) . "'
 WHERE id='".$_POST["id"]."'";
echo "tax is $tax <br />";
echo "amtdue is $amtdue <br />";
 mysql_query($sql) or die("Update query failed.");
 }
echo "Invoice Prep completed";
?>

Change your update query to below code:

$sql = "UPDATE oocust SET
 tax = '" . mysql_real_escape_string($tax) . "',
 amtdue = '" . mysql_real_escape_string($amtdue) . "',
dayslate = '" . mysql_real_escape_string($dayslate) . "'
 WHERE id='".$id."'";

Change your update query to below code:

$sql = "UPDATE oocust SET
 tax = '" . mysql_real_escape_string($tax) . "',
 amtdue = '" . mysql_real_escape_string($amtdue) . "',
dayslate = '" . mysql_real_escape_string($dayslate) . "'
 WHERE id='".$id."'";

thank you so much

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.