Cannot see where my php will not subtract

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

texmansru47
Forum Commoner
Posts: 42
Joined: Mon May 12, 2008 11:27 am

Cannot see where my php will not subtract

Post 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
Last edited by RobertGonzalez on Thu May 29, 2008 1:06 pm, edited 1 time in total.
Reason: Please use bbCode [php] tags when posting code in the forums
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Cannot see where my php will not subtract

Post by califdon »

You created the SQL UPDATE string for the subtraction, but you never ran it.
texmansru47
Forum Commoner
Posts: 42
Joined: Mon May 12, 2008 11:27 am

Re: Cannot see where my php will not subtract

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Cannot see where my php will not subtract

Post 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).
texmansru47
Forum Commoner
Posts: 42
Joined: Mon May 12, 2008 11:27 am

Re: Cannot see where my php will not subtract

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Cannot see where my php will not subtract

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Cannot see where my php will not subtract

Post 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. ;)
texmansru47
Forum Commoner
Posts: 42
Joined: Mon May 12, 2008 11:27 am

Re: Cannot see where my php will not subtract

Post 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
Last edited by texmansru47 on Mon Jun 02, 2008 10:14 pm, edited 1 time in total.
texmansru47
Forum Commoner
Posts: 42
Joined: Mon May 12, 2008 11:27 am

Re: Cannot see where my php will not subtract

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Cannot see where my php will not subtract

Post by John Cartwright »

Code: Select all

$subtract = $copy[`QtyOnHand`] - $_POST[`Consumed`];
Hint, quotes vs. backticks
texmansru47
Forum Commoner
Posts: 42
Joined: Mon May 12, 2008 11:27 am

Re: Cannot see where my php will not subtract

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Cannot see where my php will not subtract

Post 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.
texmansru47
Forum Commoner
Posts: 42
Joined: Mon May 12, 2008 11:27 am

Re: Cannot see where my php will not subtract

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: Cannot see where my php will not subtract

Post 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.
texmansru47
Forum Commoner
Posts: 42
Joined: Mon May 12, 2008 11:27 am

Re: Cannot see where my php will not subtract

Post 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
Post Reply