UPDATE in mySQL Query not working?

Question

Okay so I'm doing my website again and I want to edit or update the values of the rows so I went to phpmyadmin and check what is the code for updating values so I have this:

UPDATE `database`.`table` SET `title` = 'My Title', `message` = 'My Message' WHERE  `table`.`id` =2 LIMIT 1 ;

So I tried rephrasing it like this on PHP:

mysql_query("UPDATE posts SET title = '$title', message = '$message' WHERE id='$id' LIMIT 1");

But when I tried checking my table, it doesn't change.

By the way, there are no errors showing.

What is the content of $title and $contents? Also you need to use mysql_real_escape_string() — flowfree
– flowfree, Commented May 26, 2012 at 8:58
@bsdnoobz No, using mysql_real_escape_string() is no longer best practice. The whole mysql_ family of functions are no longer recommended. You should use the mysqli_ functions or the PDO library instead. These along with parameterised queries will provide good SQL injection protection. — liquorvicar
– liquorvicar, Commented May 26, 2012 at 9:07
Have you tried echoing out the constructed query? Have you checked the id you're looking for does exist? btw if id is a PK you don't need the LIMIT clause. — liquorvicar
– liquorvicar, Commented May 26, 2012 at 9:08
Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — eggyal
– eggyal, Commented May 26, 2012 at 9:16
Are you correctly connected to your database, does SELECT work ? — PhilippeVienne
– PhilippeVienne, Commented May 26, 2012 at 10:39

James Wright · Accepted Answer · 2012-05-26 09:01:41Z

2

Try:

mysql_query("UPDATE posts SET title = '" . $title . "' message = '" . $message . "' WHERE id =2 LIMIT 1");

What version of PHP are you using?

answered May 26, 2012 at 9:01

James Wright

3,0201 gold badge20 silver badges27 bronze badges

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

1 Comment

James Wright Over a year ago

@JuvarAbrera OK, perhaps it could be a character encoding problem. Convert HTML entities to character codes (e.g. ') | Convert character codes to HTML elements:

Uttara · Accepted Answer · 2012-05-26 09:21:57Z

2

Hope this will do

mysql_query("UPDATE posts SET title = '{$title}', message = '{$message}' WHERE id={$id} LIMIT 1");

answered May 26, 2012 at 9:21

Uttara

2,5403 gold badges25 silver badges35 bronze badges

Comments

Edesa · Accepted Answer · 2012-05-26 09:25:53Z

1

I used this:

mysql_query('UPDATE posts SET title = "' . $title .'", message = "' . $message .'" WHERE id=' . $id . ' LIMIT 1');

answered May 26, 2012 at 9:25

Edesa

5918 silver badges16 bronze badges

Comments

Maxim Krizhanovsky · Accepted Answer · 2012-05-26 09:30:04Z

1

You have SQL injection. Which means that a single quote in your message will break the query. Any variable you pass to your query, no matter if it comes from user input, was retrieved from the database, or has hard-coded value, has to be properly escaped. In your case, since you are using mysql* family of function, mysql_real_escape_string is needed. But you can do you a favor by migrating to mysqli (which is really simple process) or PDO, and use Prepared statements. When you are using prepared statements, you can forget about escaping, and bind directly the variables to the placeholders in the query.

answered May 26, 2012 at 9:30

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

Comments

Aliweb · Accepted Answer · 2012-05-26 09:54:11Z

1

probably you have quotations in your variables so you get a error.

you have two soloutions:

use this syntax : mysql_query("UPDATE posts SET title = '".$title."', message = '".$message."' WHERE id='$id' LIMIT 1");
or better to use the standard php function 'mysql_real_scape_string()' for each variable going to a sql command so each disallow character will be prefixed by a backslash and then there's no problem form you.

answered May 26, 2012 at 9:54

Aliweb

1,9513 gold badges23 silver badges45 bronze badges

Comments

Yanki Twizzy · Accepted Answer · 2012-05-26 10:01:54Z

1

Try

mysql_query("UPDATE posts SET title = '$title', message = '$message' WHERE id=$id");

Or to prevent SQL injection

mysql_query(sprintf("UPDATE posts SET title = '%s', message = '%s' WHERE id=%s LIMIT 1", mysql_real_escape_string($title), mysql_real_escape_string($message), mysql_real_escape_string($id)));

edited May 26, 2012 at 10:01

answered May 26, 2012 at 9:21

Yanki Twizzy

8,0918 gold badges44 silver badges72 bronze badges

Comments

marc_s · Accepted Answer · 2017-02-07 21:47:54Z

0

You are not using the correct text box name for referring in jQuery or script

Please correct it

edited Feb 7, 2017 at 21:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 24, 2012 at 4:10

Jordy S Chemparathy

53918 silver badges43 bronze badges

Collectives™ on Stack Overflow

UPDATE in mySQL Query not working?

7 Answers 7

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related