CODEWITHSUNDEEP
phpsqlpdo
AndrewCBailey89
AndrewCBailey89

Reputation: 93

Basic SQL Update statement not working in PHP

I'm working on a simple, small project for a class - a mock-database (built using phpMyAdmin) with an accompanying web application for user input, to allow for searching/appending/updating said database. Searching and Appending is working perfectly, but updating is giving me grief.

I have error messages set to display to help in the debugging process, and although they've popped up for previous issues in this project, they haven't shown up for this particular problem.

My code looks like this:

$sth = "UPDATE adjunct_number
        SET FIRSTNAME = '.$updateInformation.'
        WHERE 700NUMBER = '.$updateCriteria.';";

(I tried echoing $sth right here, nothing displayed!)

$sth = $db->prepare($sth);
$sth->execute();

$updateInformation and $updateCriteria are both being pulled from an HTML form's text boxes successfully. (I'll enter Drew and 700123456 into the form as test data.) I tested that $updateInformation and $updateCriteria ARE in fact being successfully captured using this statement:

echo $updateInformation . ", " . $updateCriteria

Which yields the following output:

Drew, 700123456

I'll enter the following query into phpMyAdmin to test it first.

UPDATE adjunct_number
SET FIRSTNAME = 'DREW'
WHERE 700NUMBER = 700482306

It always works in phpMyAdmin, as expected, 100% of the time... however, the exact same update command never works when I send it through via PHP, which confusions me greatly. The only thing I can think of is an overlooked PHP typo in my SQl statement, but at this point in time I've been staring at it for so long that I could use a fresh set of eyes! Why is it that when I tried to echo $sth, no sql statement was displayed? Any insight from this wonderful community would be greatly appreciated.

Upvotes: 0

Views: 3016

Answers (4)

Luc M
Luc M

Reputation: 17314

  • No need of ; in your query. You are not in interactive session
  • You are subject to SQL injection.
  • No need of quote with parameterized query.

Here's a suggestion how you you could do it.

$query = "UPDATE adjunct_number SET FIRSTNAME = ? WHERE 700NUMBER = ?";
$sth = $db->prepare($query);
$sth->execute( array( $updateInformation, $updateCriteria ) );

Upvotes: 1

Vrushab Maitri
Vrushab Maitri

Reputation: 350

May be you can try using the symbol (" ` ")backtick for column names and tables

UPDATE `adjunct_number`
SET `FIRSTNAME` = 'DREW'
WHERE `700NUMBER` = 700482306

Upvotes: 1

Manikiran
Manikiran

Reputation: 1

You have mixed single and double quotes. Update it to the following:

$sth = "UPDATE adjunct_number
    SET FIRSTNAME = ".$updateInformation."
    WHERE 700NUMBER = ".$updateCriteria.";

Upvotes: 1

Marc B
Marc B

Reputation: 360592

You need to learn basic PHP strings:

$sth = "UPDATE adjunct_number
       ^---start of PHP string
        SET FIRSTNAME = '.$updateInformation.'
        WHERE 700NUMBER = '.$updateCriteria.';";
                                              ^---end of PHP string

That means your query is doing ... SET FIRSTNAME = '.foo.' ..., with the . embedded literally within the query. That means your where clause will never match any records.

Since you're using " for the quotes, you don't NEED to use the . at all:

$sth = "UPDATE adjunct_number
        SET FIRSTNAME = '$updateInformation'
        WHERE 700NUMBER = '$updateCriteria';";

or you should properly exit the string first:

$sth = "UPDATE adjunct_number
        SET FIRSTNAME = '" . $updateInformation . "'
        WHERE 700NUMBER = '" . $updateCriteria . "';";

Upvotes: 3

Related Questions