MySQL / PHP - Cannot update database entries

Question

At the moment I'm trying to make a mini blog/cms type of thing for myself to test my skills and hopefully learn a thing or two with PHP.

So I've got a form that has a text field inside it. When it's submitted it should run the following query, however I get the following error...

Resource id #4 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 'cms, description = hello world, maintenance = off, regsi' at line 1

Here is the code around that area...

mysql_query("UPDATE settings SET name = " . $siteName . ", description = " . $siteDesc . ", maintenance = " . $siteMode . " [...] ") or die($settings . "<br/>" . mysql_error());

I've shortened it using "[...]" as it follows the same style (ie. "test1 = $test1, test2 = $test2" etc...).

Any help please? Thanks!

I agree with @Fred-ii- . You can see from the error message that it tries to do description = hello world. The string value has to be in quotes like description = 'hello world'. — Alex Szabo
– Alex Szabo, Commented Sep 16, 2014 at 20:01
you should ask a new question rather than edit your existing one — andrew
– andrew, Commented Sep 16, 2014 at 20:16
Its probably because you are a new user and need to earn the rep, I gave you +5 points for this question to help you on your way but unfortunately you need to stick to protocol, one topic per post. take a look here in the meantime stackoverflow.com/questions/12020227/… — andrew
– andrew, Commented Sep 16, 2014 at 20:32

Community · Accepted Answer · 2017-05-23 12:29:30Z

2

You don't actually need to be closing and reopening the string with the . (concatenation) operator

The php string parser will interpolate variables into the string.

So you can do it like this:

mysqli_query("UPDATE settings SET name = '$siteName', description = ...";

The single quotes tell mysql to treat the variables as string literals instead of column names.

What you should also be doing (if not already) is escaping your user input variables see How can I prevent SQL injection in PHP?

And what you should not be doing is using mysql*_ functions as they're depreciated. see the big red box here use mysqli*_ instead

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Sep 16, 2014 at 20:02

andrew

9,6039 gold badges36 silver badges62 bronze badges

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

3 Comments

Dan Over a year ago

Thanks andrew and Fred -ii-. Yeah that works now. I put security on the bench for now as I'm still learning how to do stuff but if I replace mysql with mysqli, is that better/more secure? This is only for a personal project of mine anyway so nothing is going onto a live environment.

Funk Forty Niner Over a year ago

@Dan You're welcome. Use mysqli with prepared statements, or PDO with prepared statements.

andrew Over a year ago

@Dan its not so much a security issue but one of functionality see stackoverflow.com/questions/548986/mysql-vs-mysqli-in-php, also bear in mind that many websites out there are all gonna break when people try to upgrade to future versions of php :)

Collectives™ on Stack Overflow

MySQL / PHP - Cannot update database entries

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related