MySQL Update from PHP form

Question

As a novice MySQL user I tried to insert, but I just read on the MySQL documentation that you can only insert on blank rows. My UPDATE statement needs work though, and I'm not sure that I have the syntax correct.

$query3 = "UPDATE `offices` SET `scash`="$total" WHERE `officename`="$office"";

offices is the table name. scash is the row to be updated. $total is a variable pulled from a post. $office is a variable pulled from the same database. I only want to set scash to total where the officename is $office.

Parse error: syntax error, unexpected T_VARIABLE is the error I'm getting.

Please learn how to use proper SQL escaping before you hurt yourself. This query is extremely dangerous. — tadman
– tadman, Commented Oct 22, 2012 at 17:42

Community · Accepted Answer · 2017-05-23 10:24:55Z

2

$query3 = "UPDATE `offices` SET `scash`='$total' WHERE `officename`='$office'";

Replace the double quotes with normal quotes in the string since double quotes are string delimiters and can't be used in the string.

And as Marc B mentioned your code might be vurnerable for SQL injections. See this post how you can avoid that.

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Oct 22, 2012 at 17:26

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marc B Over a year ago

and for the average php novice, better mention something about SQL injection

smada Over a year ago

I really appreciate your efforts. I don't know what I'd do without you guys.

tadman Over a year ago

The best way to say thanks is to learn PDO or mysqli so you don't fall into this trap again in the future.

smada Over a year ago

I'm on my way to learning mysqli, unfortunately, I'm under a time crunch right now, so this is the route I have to take. I can update my code as of next week.

Mr. Alien · Accepted Answer · 2012-10-22 17:27:51Z

1

You are going wrong at quotes

$query3 = "UPDATE `offices` SET `scash`="$total" WHERE `officename`='$office'";

Also always use LIMIT 1 if you want to update just a single row...

And sanitize your inputs before updating your row, atleast use mysqli_real_escape_string()

answered Oct 22, 2012 at 17:27

Mr. Alien

158k36 gold badges307 silver badges287 bronze badges

5 Comments

tadman Over a year ago

Using LIMIT 1 on an UPDATE is bad advice. Your WHERE clause should be more specific if you're having limit issues.

tadman Over a year ago

Yes, there is harm. It makes no sense, for one, and secondly leads to a false sense of security. Randomly updating one row is crazy.

tadman Over a year ago

The problem with LIMIT 1 is you don't get to say what row. MySQL will just pick one randomly for you, which leads to unpredictable behavior.

tadman Over a year ago

Additionally, if you're using mysqli and somehow involve mysqli_real_escape_string in your code you're doing it wrong. Please use placeholders for all data escaping.

tadman Over a year ago

The WHERE clause doesn't have a limit, the UPDATE does. It's generally wrong to do this. You really don't have a leg to stand on here.

Sony Mathew · Accepted Answer · 2012-10-22 17:48:22Z

1

if you still want to use double quotes inside double quotes escape it..

your query can be modified as follows..

$query3 = "UPDATE `offices` SET `scash`=\"$total\" WHERE `officename`=\"$office\"";

answered Oct 22, 2012 at 17:48

Sony Mathew

2,9912 gold badges25 silver badges30 bronze badges

Collectives™ on Stack Overflow

MySQL Update from PHP form

3 Answers 3

4 Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related