PHP/MySQL - UPDATE database issue

I am trying to connect to my database & update a record. I have already succeeded with inserting a record.

This is the code that calls the update procedure:

edit_extra($category_id, $itemName, $itemDescription, $itemPrice, $itemActive, $itemValue, $itemID);

When I pass my cursor over the vars in debug mode I can see the correct values. Here is the UPDATE proc:

 
function edit_extra($categoryID, $extraName, $extraDescription, $extraPrice, $extraActive, $extraValue, $extraID) {
	 global $db;
	 $query = 'UPDATE menuitems
	             SET CategoryID = :categoryID,			        Name = :extraName,
                          Description = :extraDescription,		        Price = :extraPrice,			        Active = :extraActive;			        Value = :extraValue;
                       WHERE ItemID = :extraID';
	 try {
		 $statement = $db->prepare($query);
		 $statement->bindValue(':categoryID', $categoryID);
		 $statement->bindValue(':extraName', $extraName);
		 $statement->bindValue(':extraDescription', $extraDescription);
		 $statement->bindValue(':extraPrice', $extraPrice);
		 $statement->bindValue(':extraActive', $extraActive);
		 $statement->bindValue(':extraValue', $extraValue);
		 $statement->bindValue(':extraID', $extraID);
		 $statement->execute();
		 $rowct = $statement->rowCount();
		 $statement->closeCursor();
	 } catch (PDOException $e){
			 // do something here
			 $error_msg = $e->getMessage();
	 }
}

Open in new window


When I pass my cursor over the vars in this proc, again, the correct values are there. I've doubled checked the value for the $extraID & it is there!

But what I've discovered through testing, is that every field of every record in the recordset changes to the new value I input, except for the Value field. For some reason that field doesn't update, even though the new value for the field has been passed into the query.

I'm stumped & out of ideas of where to look & would love any suggestions.

Thanks.
LezlyPrimeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Could it be that you are not identifying the type of variable?  See the examples on http://us3.php.net/manual/en/pdostatement.bindvalue.php where they use PDO::PARAM_* constants.  http://us3.php.net/manual/en/pdo.constants.php
0
LezlyPrimeAuthor Commented:
I tried that to no avail.
0
Dave BaldwinFixer of ProblemsCommented:
Post your modified code.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

LezlyPrimeAuthor Commented:
function edit_extra($categoryID, $extraName, $extraDescription, $extraPrice, $extraActive, $extraValue, $extraID) {
    global $db;
    $query = 'UPDATE menuitems
              SET CategoryID = :categoryID,
                  Name = :extraName,
                  Description = :extraDescription,
                  Price = :extraPrice,
                  Active = :extraActive;
                  Value = :extraValue;
              WHERE ItemID = :extraID';
    try {
        $statement = $db->prepare($query);
        $statement->bindValue(':categoryID', $categoryID, PDO::PARAM_INT);
        $statement->bindValue(':extraName', $extraName, PDO::PARAM_STR);
        $statement->bindValue(':extraDescription', $extraDescription, PDO::PARAM_STR);
        $statement->bindValue(':extraPrice', $extraPrice);
        $statement->bindValue(':extraActive', $extraActive, PDO::PARAM_INT);
        $statement->bindValue(':extraValue', $extraValue, PDO::PARAM_STR);
        $statement->bindValue(':extraID', $extraID, PDO::PARAM_INT);
        $statement->execute();
        $rowct = $statement->rowCount();
        $statement->closeCursor();

    } catch (PDOException $e){
        // do something here
        $error_msg = $e->getMessage();
    }
}

Open in new window


In addition to this, I tried hard coding the ItemID & I got the same result. It isn't seeing the ItemID.

I recall that the Value field is a unique field, which is why it doesn't get updated. So that clears that up. Now, how come it's not recognizing my ItemID? Especially when I can see it in debug?
0
Dave BaldwinFixer of ProblemsCommented:
Show me the table structure.  Is ItemID an auto-increment column?
0
LezlyPrimeAuthor Commented:
Update: There is a typo here:

Active = :extraActive;

After changing it to a comma, it no longer changes all the records, but it does not change the record it's supposed to, either.
0
LezlyPrimeAuthor Commented:
0
LezlyPrimeAuthor Commented:
Here's something you can read.

table structure

0
Dave BaldwinFixer of ProblemsCommented:
I didn't notice the ';' before but there was also one after ':extraValue' which didn't belong there.  Maybe that is cutting off the query at that point now.
$query = 'UPDATE menuitems
              SET CategoryID = :categoryID,
                  Name = :extraName,
                  Description = :extraDescription,
                  Price = :extraPrice,
                  Active = :extraActive,
                  Value = :extraValue
              WHERE ItemID = :extraID';

Open in new window

0
LezlyPrimeAuthor Commented:
I got that one too & still no difference.
0
LezlyPrimeAuthor Commented:
On update: I've played around with this but am still at a loss. I've posted on another board, too. Below is the code as it stands currently. I get no errors with this code, but the record does not get updated.

 
function edit_extra($categoryID, $extraName, $extraDescription, $extraPrice, $extraActive, $extraValue, $extraID) {
	global $db;
	$query = 'UPDATE menuitems
						SET CategoryID = :categoryID,
								Name = :extraName,
								Description = :extraDescription,
								Price = :extraPrice,
								Active = :extraActive,
								Value = :extraValue,
						WHERE ItemID = :extraID';
	try {
			$statement = $db->prepare($query);
			$statement->bindValue(':categoryID', $categoryID, PDO::PARAM_INT);
			$statement->bindValue(':extraName', $extraName, PDO::PARAM_STR);
			$statement->bindValue(':extraDescription', $extraDescription, PDO::PARAM_STR);
			$statement->bindValue(':extraPrice', $extraPrice);
			$statement->bindValue(':extraActive', $extraActive, PDO::PARAM_INT);
			$statement->bindValue(':extraValue', $extraValue, PDO::PARAM_STR);
			$statement->bindValue(':extraID', $extraID, PDO::PARAM_INT);
			$statement->execute();
			$rowct = $statement->rowCount();
			$statement->closeCursor();

	} catch (PDOException $e){
			// do something here
			$error_msg = $e->getMessage();
	}
}

Open in new window


Again, when I pass my cursor over :extraID on line #9, there is a value there, but nothing happens.

Thanks for hanging tough.

ps - Is there a way to increase points on a question?
0
LezlyPrimeAuthor Commented:
Ok, so it was a syntax error. ~blush. An extra comma in the SET clause after the Value parameter.

I guess that changes my question to: Why didn't I get a SQL syntax error?

Anyway, thanks for your time on this Dave Baldwin.
0
Dave BaldwinFixer of ProblemsCommented:
Because the default mode is to not tell you about them?  http://us.php.net/manual/en/pdo.error-handling.php  While I have used PDO just to see if it would work, I normally the 'mysql' driver which is more direct about these things.  'mysqli' is a newer driver that is also object oriented if you want.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LezlyPrimeAuthor Commented:
Thanks, I will check it out. PDO was presented in the book I'm using, but I have another one that uses mysqli.
0
LezlyPrimeAuthor Commented:
Considering how this played out, I am very satisfied with the solution. While I solved my original issue, the solution presented here has helped me with other database issues I have encountered since asking this question.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Content Management

From novice to tech pro — start learning today.