asked on

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.

Dave Baldwin

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

LezlyPrime

ASKER

I tried that to no avail.

Dave Baldwin

Post your modified code.

LezlyPrime

ASKER

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?

Dave Baldwin

Show me the table structure. Is ItemID an auto-increment column?

LezlyPrime

ASKER

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.

LezlyPrime

ASKER

400343435.jpg

LezlyPrime

ASKER

Here's something you can read.

table structure

Dave Baldwin

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

LezlyPrime

ASKER

I got that one too & still no difference.

LezlyPrime

ASKER

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?

LezlyPrime

ASKER

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.

ASKER CERTIFIED SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LezlyPrime

ASKER

Thanks, I will check it out. PDO was presented in the book I'm using, but I have another one that uses mysqli.

LezlyPrime

ASKER

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.