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"; ?> PHP:
$query = SELECT `taxrate` FROM `dbname` ... And use the same for the next section to.. SELECT `id`, `name`, etc and so on.. The field names will have reserved value, so you should have to use tilt ( ` ) symbol. Its not single quotes. You can find it below the escape key. Hope that helps. Cheers.
that didn't work. As you can see from the echo, the below code gives me the taxrate but I get the below message: <?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 = ' '"; $stat = @mysql_fetch_assoc(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"; ?> PHP:
Does $taxrate get successfully echoed in the second code? Also, do not suppress errors when you're trying to figure out what's wrong with a piece of code. You should turn all error reporting on. Edit: Also, echo what $result is. You're getting the error either because $result is either null or some value that isn't being accepted my mysql_fetch_array which needs to be either a valid mysql resource or array. The value could be null due to a failed Mysql query, just a side note.
with the below code I get the message: <?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; confirmed 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_fetch_assoc(mysql_query($query)); while($row = mysql_fetch_array($result)) echo "result " . $result; { $id = $row['id']; $shipamt = $row['shipamt']; $duedate = $row['duedate']; $charges = $row['charges']; $dayslate = $row['dayslate']; $tax = $row['tax']; $amtdue = $row['amtdue']; echo "tax is $tax <br /><br />"; $tax = $charges * $taxrate; $amtdue = $charges + $tax + $shipamt; $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"; ?> PHP:
Well, aren't you returning an associative array? Why would you use while($row = mysql_fetch_array($result)), you would need to use the second parameter of while($row = mysql_fetch_array($result, MYSQL_ASSOC)) for that. Try using this: while($row = mysql_fetch_assoc($result)) PHP:
I echo the values from both databases but no update: <?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"; ?> PHP: