Page 2 of 3
Re: Cannot see where my php will not subtract
Posted: Tue Jun 03, 2008 12:44 am
by RobertGonzalez
That means you SQL is jacked up.
Instead of passing the entire SQL query as a string, try setting into a multiline variable like this:
Code: Select all
<?php
$sql = "SELECT *
FROM `mytable`
WHERE `myfield` = '$queryvar'
LIMIT 10";
?>
Then make sure you echo that var out when die()ing to see what is actually being sent to the database server.
Re: Cannot see where my php will not subtract
Posted: Tue Jun 03, 2008 4:50 pm
by texmansru47
That means you SQL is jacked up.
I guess I do not follow? I can run the commands in MySQL to subtract (I have to use a whole number not the call from the form as I have in my current code) and it works fine... basically everything works fine except this subtract function. The Parse Error I receive is a clueless error, much like I have bee seeing in this path of learning php. What sucks is earlier on before I required all the other data to be queried and loaded to additional tables the subtract WORKED... so I cannot figure out what syntax I have applied that this system just does not like.
Additionally when I check the table after the query is ran I get a value of "0" for the QtyOnHand. So it is doing something, but not the the right something.
Thanks,
Texman
Re: Cannot see where my php will not subtract
Posted: Tue Jun 03, 2008 5:10 pm
by RobertGonzalez
Read through this code, top to bottom and check the comments. I hope this helps you a little bit at least:
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());
// Insert form values into database
/**
* Something to note... you really should be checking this POST variable for:
* - isset
* - is valid
* - is clean
*/
$sql = "SELECT * FROM `SNAInv` WHERE `ProdNum`= '$_POST[ProdNum]'";
$result = mysql_query($sql) or die('Query Error: ' . mysql_error());
$copy = mysql_fetch_array($result);
// Now to the next query
/**
* Looking at this now, you really don't the first query at all
* Same rules for the POST var though
* And I think you problem was with the lack of quotes around your post var
* and the use of backticks where you should not have had them
*/
$sql = "UPDATE `SNAInv`
SET `QtyOnHand` = `QtyOnHand` - $_POST[Consumed]
WHERE `ProdNum` = '$_POST[ProdNum]";
$result = mysql_query($sql) or die('SQL error" ' . mysql_error());
// You should check affeceted rows when doing an insert/update/delete query
if (mysql_affected_rows() == 0) {
// There was a problem
}
/**
* Ok, from the query below it looks like you need the new quantity so you will
* have to think of a better way to get to it.
*
* Perhaps somewhere above you could make a var called $newquant and set it
* equal to `QtyOnHand` - $_POST[Consumed] and use that in your update query
* above and in this query below.
*/
/**
* Ok, you did need the first query for this one here
*/
$sql = "INSERT INTO `SNAInvHist` (
`ProdNum`,
`ProdDesc`,
`QtyOnHand`,
`Consumed`,
`RecvDate`,
`ConSumDate`
)
VALUES (
'$copy[ProdNum]',
'$copy[ProdDesc]',
'$newquant', '$_POST[Consumed]',
'$copy[RecvDate]',
Now())";
$result = mysql_query($sql) 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$newquant";
mysql_close($con);
?>
<p>Return to Manage Inventory Control</p>
Re: Cannot see where my php will not subtract
Posted: Tue Jun 03, 2008 5:13 pm
by Weirdan
Everah, you forgot about escaping strings that goes into sql queries.
Re: Cannot see where my php will not subtract
Posted: Tue Jun 03, 2008 5:16 pm
by RobertGonzalez
Crap. You are right weirdan. Don't forget the escaping of vars pushed to your queries.
Re: Cannot see where my php will not subtract
Posted: Tue Jun 03, 2008 7:33 pm
by texmansru47
Guys you are awesome in helping me with this learning experience of mine... I truly appreciate it. One thing, on your comment:
Crap. You are right weirdan. Don't forget the escaping of vars pushed to your queries.
I have no idea what that is. I'm searching that as I type this. Still learning the lingo so I do apologize.
Thanks again,
Texman
Re: Cannot see where my php will not subtract
Posted: Tue Jun 03, 2008 10:58 pm
by RobertGonzalez
Look into
mysql_real_escape_string().
Edit | For a start, that is.
Re: Cannot see where my php will not subtract
Posted: Tue Jun 03, 2008 11:10 pm
by texmansru47
Gotcha... will do. One thing. I lost the errors with your modified code I had to make a couple of adjustments. But I noticed one thing. in the initial query (the one where I need to collect the QtyOnHand value represented to the ProdNum the user entered) does not seems to be working. I isolated the code and tried to see what was pulling and I get a blank screen.
Here is the code:
Code: Select all
$sql = "SELECT * FROM `SNAInv` WHERE `ProdNum`= '$_POST[ProdNum]'";
$result = mysql_query($sql) or die('Query Error: ' . mysql_error());
$copy = mysql_fetch_array($result);
$newquant = $sql[QtyOnHand] - $_POST[Consumed];
It appears not to be locating the data in the database or (more likely) it is not retrieving any data. Each time I run it I get the $newquant as negative whatever the user enters. So I know I have to work on this query, but is it very confusing.
Re: Cannot see where my php will not subtract
Posted: Wed Jun 04, 2008 12:02 am
by RobertGonzalez
Sorry, I think my little "exercise for you to do" was lost in my translation.
What I did was created a $newquant var later in the script with the hopes that you would go back and set that var using the values I put in the comments. I can guarantee that the code I posted will not work out of the box, but with just a little bit of massaging it will work as expected.

Re: Cannot see where my php will not subtract
Posted: Wed Jun 04, 2008 3:32 pm
by califdon
texmansru47 wrote: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
It
should tell you everything you need to correct your error. It tells you that it's a syntax error, rather than a logic error or failure to connect, or whatever. It tells you which line of your script the syntax error is on. It tells you the exact place in your SQL statement where it failed to understand you. And it even recommends that you read the appropriate manual to learn the correct syntax. What else did you expect it to tell you? (And this is no doubt the most common error that you will ever see in a script that includes MySQL commands.)
Re: Cannot see where my php will not subtract
Posted: Wed Jun 04, 2008 5:33 pm
by Weirdan
califdon wrote:It tells you which line of your script the syntax error is on.
Well, it tells anything but that. That's why it's recommended to add __FILE__ . ':' . __LINE__ to the error message.
Re: Cannot see where my php will not subtract
Posted: Wed Jun 04, 2008 7:26 pm
by texmansru47
Well here is line 1 as indicated in the error:
I guess I can see your point. But where is the "1" as indicated? Maybe I'm just not seeing it. According to the reference manual I have for my MySQL Version my setup of <?php should be right.
So I say again... it is not telling me much.
Re: Cannot see where my php will not subtract
Posted: Wed Jun 04, 2008 7:28 pm
by texmansru47
BTW the error:
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
Is the exact error I get. There is no line or file information at all oustide of the "near "1" at line 1" BS.
Re: Cannot see where my php will not subtract
Posted: Wed Jun 04, 2008 7:42 pm
by califdon
Remember, this is a MySQL error message, so it's not even looking at a line like <?php, it's complaining about your SQL syntax. It looks to me like maybe you are including a file with a mysql_query() call that's maybe on Line 1, so that's the only line that MySQL sees ? ? I don't have an immediate explanation of why it says Line 1, but clearly it's not referring to line 1 of that file. You will probably need to trace where the error is being encountered, by placing echo statements in various places to see at what point it breaks down. Perhaps you already know how much of your script is executing, if any, and what part is not.
Re: Cannot see where my php will not subtract
Posted: Wed Jun 04, 2008 7:49 pm
by texmansru47
Sounds good. I will start to place echo statements to see where I'm failing.