Page 1 of 3

Cannot see where my php will not subtract

Posted: Tue May 27, 2008 10:56 pm
by texmansru47
I'm baffled. I have this code that I trying to complete that is to do three things:

1) Collect data from a form and used POST data to take that entry and subtract it against the total current QTY in my MySQL Database.
2) Update the two tables (SNAInv and SNAInvHist(this table is the transactions of the add/subtracts from each item in the main INV db)
3) tell it completed.

The problem is the only thing NOT working is the subtraction... I get a big fat NADA.

Here is the form:

Code: Select all

<html><head><title>Inventory</title></head> <body bgcolor="#C0C0C0"><img border="0" src="logo1.jpg" width="78" height="77">&nbsp;<font face="Arial" color="#000080">Inventory - Managing Inventory Input</font><font face="Arial" color="#000080"><P> <form method="POST" name="Recv" action="queryme.php"> <table width="550" border="0" cellspacing="2" cellpadding="3">  <tr>    <td colspan="2" bgcolor="#000000"><span class="style1">Managing Inventory Control</span></td>  </tr>  <tr>    <td bgcolor="#FFCC66">Part Number: </td>    <td bgcolor="#CCCCCC"><input name="ProdNum" type="text" id="ProdNum"></td>  </tr>  <tr>    <td width="159" bgcolor="#FFCC66">Part Description:</td>    <td width="373" bgcolor="#CCCCCC"><input name="ProdDesc" type="text" id="ProdDesc"></td>  </tr>  <tr>    <td bgcolor="#FFCC66">Amount Consumed: </td>    <td bgcolor="#CCCCCC"><input name="Consumed" type="int" id="Consumed"></td>  </tr>   <tr>    <td>&nbsp;</td>    <td><input type="submit" value="Submit Modifications" /></td>  </tr></table> </form></body></html>
I believe the value is being pulled but I cannot tell. Here is the PHP code for the work required:

Code: Select all

<?php
 
  // Connect to mysql database
 
$con = mysql_connect("localhost","user","password") or die('Connection: ' . mysql_error());;
mysql_select_db("logdata", $con) or die('Database: ' . mysql_error());
 
mysql_select_db("logdata", $con);
 
 
  // Insert form values into database
  
 
  $copy = mysql_fetch_array(mysql_query("SELECT * FROM `SNAInv` WHERE `ProdNum`= '$_POST[ProdNum]'")) or die(mysql_error()); 
// ^^ SELECT THE DATA TO BE COPIED ^^ 
 
$sqlrun = mysql_query("UPDATE `SNAInv` SET `QtyOnHand` = `$copy[QtyOnHand]` - `". $_POST[Consumed] ."`  WHERE `ProdNum` = `$_POST[ProdNum]`");
 
$sql = @mysql_query("INSERT INTO `SNAInvHist` (`ProdNum`, `ProdDesc`, `QtyOnHand`, `Consumed`, `RecvDate`, `ConSumDate`) VALUES ('" . $copy['ProdNum'] . "', '" . $copy['ProdDesc'] . "', '" . $sqlrun['QtyOnHand'] . "', '". $_POST[Consumed] ."', '". $copy[RecvDate] ."', Now())") or die(mysql_error());
 
echo "Inventory For Part Number:\n".$copy[ProdNum]." has been modified and a Transaction has been recorded ";
echo "
 new Quantity is:\n".$sqlrun[QtyOnHand]."";
 
 
mysql_close($con);
?>
<p>Return to Manage Inventory Control</p>
Everything works but the only thing not working is the subtraction part. Each table has the transaction posted but a big fat "0" is posted in the quantity field on the SNAInvHist table and the QTY field in SNAInv is the same as it was.

What am I missing?

Thanks,

Texman

Re: Cannot see where my php will not subtract

Posted: Wed May 28, 2008 2:38 pm
by califdon
You created the SQL UPDATE string for the subtraction, but you never ran it.

Re: Cannot see where my php will not subtract

Posted: Wed May 28, 2008 8:50 pm
by texmansru47
I didn't? I cannot see what I forgot. Would that be the $request statement... like:

if (!mysql_query($sqlrun, $con))
{
die('Error: ' . mysql_error());
}
echo "Inventory Change";

Would that be it?

Texman

Re: Cannot see where my php will not subtract

Posted: Wed May 28, 2008 10:37 pm
by califdon
texmansru47 wrote:I didn't? I cannot see what I forgot. Would that be the $request statement... like:

if (!mysql_query($sqlrun, $con))
{
die('Error: ' . mysql_error());
}
echo "Inventory Change";

Would that be it?

Texman
It's very difficult to read your code. If you post code here again, please use the [ code=php] .... [ /code] tags (without the spaces in the brackets). Now I see that you did execute the query, but you didn't use the " or die(mysql_error())" syntax on that query, which could have showed you if there was an execution error. I recommend that you do that. As a general practice, I also recommend that you do calculations like your subtraction as a separate piece of code, rather than do the calculation when you are forming the query string. Why? So that you can test the value of the variable and avoid awkward statements like this to debug. I would do it more like this:

Code: Select all

$consumed=$_POST['consumed'];
$ProdNum=$_POST['ProdNum'];
$newbalance = $copy['QtyOnHand'] - $consumed;
$sql="UPDATE `SNAInv` SET `QtyOnHand` = $newbalance WHERE `ProdNum` = '$ProdNum'");
$sqlrun=mysql_query($sql) or die(mysql_error());
This greatly simplifies reading and debugging your script.

You also have some incorrect back-ticks (`) and single quotes (') in your code. Back-ticks are used only around table and field names in MySQL. They are different from single quotes ('), which are used around array index names and strings. Look closely at the differences in the code I'm showing you above (and which are enclosed in [ code] tags).

Re: Cannot see where my php will not subtract

Posted: Thu May 29, 2008 10:15 am
by texmansru47
Thank you for your input and critique. Since I'm learning developing on the fly any insight would be great. I will give this a whirl. I was wondering about the calculations in the query but that is the only method I could think of.

Thanks again,

Texman

Re: Cannot see where my php will not subtract

Posted: Thu May 29, 2008 1:02 pm
by califdon
texmansru47 wrote:Thank you for your input and critique. Since I'm learning developing on the fly any insight would be great. I will give this a whirl. I was wondering about the calculations in the query but that is the only method I could think of.

Thanks again,

Texman
It's not that it won't work, but an important part of successful programming of any kind is to write it defensively--that is, expect that it's not going to work the first time, so use techniques that make it easier to debug. Also, you (or perhaps someone else) may need to revise the code at some future date, and believe me, even well-written code can be hard to understand just a few months after you've written it, unless you use good comments, and the way you structure it is especially helpful in those circumstances. Also when asking for help in a forum. A logically structured and properly indented block of code will receive a lot more attention in a forum post than one which may be technically correct, but so difficult to read that people will move on to another post where they are not being asked to spend a lot of time just figuring out what is there.

Good luck.

Re: Cannot see where my php will not subtract

Posted: Thu May 29, 2008 1:11 pm
by RobertGonzalez
Is there an input type="int" in HTML? I have never seen that before.

Also, you are not wrapping your array indeces in quote ($_POST[consumed]) so that is almost certainly throwing errors you are not seeing because of your error reporting level and display errors settings.

You are also not checking for the $_POST array and you are using a database fetch without checking to see if there are actually any rows returned.

I would recommend running the first query then dumping the result to see what is coming back. Then once you know that use the data for the update. Use mysql_error() a lot as it will be your friend when developing (though you are probably going to want to use them for live/production sites).

Develop locally and push finished products into production after testing.

I think that is all the advice I can muster up at the moment. ;)

Re: Cannot see where my php will not subtract

Posted: Mon Jun 02, 2008 10:09 pm
by texmansru47
Thanks for the input but I a newbie (self training) so I'm not familar with the error checking you are talking about.

Here is the code (PHP in question):

Code: Select all

 
 
<?php
// Connect to mysql database
 
$con = mysql_connect("localhost","user","password") or die(`Connection: ` . mysql_error());;
mysql_select_db("logdata", $con) or die(`Database: ` . mysql_error());
 
mysql_select_db("logdata", $con);
 
$copy = mysql_fetch_array(mysql_query("SELECT * FROM `SNAInv` WHERE `ProdNum`= `". $_POST[`ProdNum`] ."`")) or die(mysql_error());
 
$subtract = $copy[`QtyOnHand`] - $_POST[`Consumed`];
 
$sqlrun = mysql_query("UPDATE `SNAInv` SET `QtyOnHand` = '$subtract'  WHERE `ProdNum` = `". $_POST[`ProdNum`] ."`");
 
$sql = @mysql_query("INSERT INTO `SNAInvHist` (`ProdNum`, `ProdDesc`, `QtyOnHand`, `Consumed`, `RecvDate`, `ConSumDate`) VALUES (`" . $copy[`ProdNum`] . "`, `" . $copy[`ProdDesc`] . "`, $subtract, `". $_POST[Consumed] ."`, `". $copy[RecvDate] ."`, Now())") or die(mysql_error());
 
echo "Inventory For Part Number:\n" .$copy[`ProdNum`] ." has been modified and a Transaction has been recorded ";
echo "new Quantity is:\n $subtract;
 
mysql_close($sqlrun,$con);
?>
 
 
Now I'm getting the following Parse Error - and once again I cannot find it.


Parse error: parse error in /var/www/htdocs/queryme.php on line 21

Does this have to do with the (around my array indices) and lack of error checking? At this point I'm clueless.

Thanks,

Texman

Re: Cannot see where my php will not subtract

Posted: Mon Jun 02, 2008 10:21 pm
by texmansru47
I even ran the code as this:

Code: Select all

 
<?php
// Connect to mysql database
 
$con = mysql_connect("localhost","user","password") or die(`Connection: ` . mysql_error());;
mysql_select_db("logdata", $con) or die(`Database: ` . mysql_error());
 
mysql_select_db("logdata", $con);
 
$consumed=$_POST['consumed'];
$ProdNum=$_POST['ProdNum'];
 
$copy = mysql_fetch_array(mysql_query("SELECT * FROM `SNAInv` WHERE `ProdNum`= `$ProdNum`")) or die(mysql_error());
 
$subtract = $copy[`QtyOnHand`] - $consumed;
 
$sql = mysql_query("UPDATE `SNAInv` SET `QtyOnHand` = '$subtract'  WHERE `ProdNum` = '$ProdNum'");
 
$sqlrun=mysql_query($sql) or die(mysql_error());
 
$hist = @mysql_query("INSERT INTO `SNAInvHist` (`ProdNum`, `ProdDesc`, `QtyOnHand`, `Consumed`, `RecvDate`, `ConSumDate`) VALUES (`" . $copy[`ProdNum`] . "`, `" . $copy[`ProdDesc`] . "`, $subtract, `". $_POST[Consumed] ."`, `". $copy[RecvDate] ."`, Now())") or die(mysql_error());
 
echo "Inventory For Part Number:\n" .$copy[`ProdNum`] ." has been modified and a Transaction has been recorded ";
echo "new Quantity is:\n $subtract;
 
mysql_close($sqlrun,$con);
?>
 
and I'm still getting the following error at the >? line of the code.

Parse error: parse error in /var/www/htdocs/queryme.php on line 26

What would create this error? What am I missing to point the error to the last line of the code? This should be so freaking easy and it is a freaking nightmare.

Texman

Re: Cannot see where my php will not subtract

Posted: Mon Jun 02, 2008 10:35 pm
by John Cartwright

Code: Select all

$subtract = $copy[`QtyOnHand`] - $_POST[`Consumed`];
Hint, quotes vs. backticks

Re: Cannot see where my php will not subtract

Posted: Mon Jun 02, 2008 10:41 pm
by texmansru47
??? You mean something like this:

$subtract = "$copy[`QtyOnHand`] - $_POST[`Consumed`]";

OR

$subtract = '". $copy[`QtyOnHand`] ."' - '". $_POST[`Consumed`] ."'";

OR like the last example:

$subtract = '". $copy[`QtyOnHand`] ."' - $consumed;

Not familar with developing with php so there are a great many things I have no idea about.

Thanks,

Texman

Re: Cannot see where my php will not subtract

Posted: Mon Jun 02, 2008 11:02 pm
by RobertGonzalez
You didn't close the quote on the string on line 21. Notice you opened it with a double quote but never closed it. Hence, parse error.

PHP tells you exactly where the issue arises. The rest of the code looked fine.

Re: Cannot see where my php will not subtract

Posted: Mon Jun 02, 2008 11:11 pm
by texmansru47
Okay... now I'm confused. Here is the code on line 21:

$hist = @mysql_query("INSERT INTO `SNAInvHist` (`ProdNum`, `ProdDesc`, `QtyOnHand`, `Consumed`, `RecvDate`, `ConSumDate`) VALUES (`" . $copy[`ProdNum`] . "`, `" . $copy[`ProdDesc`] . "`, $subtract, `". $_POST[Consumed] ."`, `". $copy[RecvDate] ."`, Now())") or die(mysql_error());

It looks like I got them all... where am I missing one?

Texman

Re: Cannot see where my php will not subtract

Posted: Mon Jun 02, 2008 11:22 pm
by RobertGonzalez
It was actually on line 20, which is usually the case with PHP errors:

Code: Select all

echo "new Quantity is:\n $subtract;
PS Please wrap your code in bbCode tags when posting code in the forums. It is one of our rules and it makes reading posts a lot easier. Thanks.

Re: Cannot see where my php will not subtract

Posted: Mon Jun 02, 2008 11:46 pm
by texmansru47
Will do. I corrected the error and now I get a very unfriendly error message... have you seen this one before?

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

This really tells me nothing.

Texman