PHP script not updating mysql table

Question

this script is simply not working... can anyone tell me what I'm doing wrong?

$id = $_POST['id'];
$name = $_POST['name'];
$date = $_POST['date'];
$shortdesc = $_POST['shortdesc'];
$link = $_POST['link'];
$target = $_POST['target'];
$sort = $_POST['sort'];
$html = $_POST['html'];

    include('appvars.php');

    $query = "UPDATE insight SET name='".$name."' AND SET date='". $date . "' AND SET html='" . $html . "' AND SET shortdesc='" . $shortdesc . "' AND SET link='" . $link . "' AND SET target='" . $target . "' AND SET sort='" . $sort . "' WHERE id='" . $id . "'";

    mysqli_query($dbc, $query);

put echo($query); after the SQL query to see what is being passed — codingbiz
– codingbiz, Commented Jun 6, 2012 at 5:56
@tunmisefasipe, most fundamental and the must thing to do :) +1 — Jashwant
– Jashwant, Commented Jun 6, 2012 at 5:58
call mysqli_error after the query to check if it returns any errors. This is a good practice in general — Anirudh
– Anirudh, Commented Jun 6, 2012 at 6:05

Mark Byers · Accepted Answer · 2012-06-06 06:01:42Z

5

You aren't escaping your values so you are vulnerable to SQL injection and also construction of invalid statements. For example, if any of your input strings contain an apostrophe then it could cause your code to fail.

Have a look at prepared statements that will make it much easier to construct your queries with parameters.

In your query you will also need to use commas instead of AND SET.

$query = "UPDATE insight SET name='foo', date='2012-12-10' WHERE id=42";

The syntax for UPDATE is described in the MySQL documentation:

UPDATE syntax

edited Jun 6, 2012 at 6:01

answered Jun 6, 2012 at 5:49

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

5 Comments

user1427274 Over a year ago

not worried about that right now. not a public form, only for my own use.

Corbin Over a year ago

@user1427274 Escaping values isn't about security; it's about correct interpretation. Security just happens to be a byproduct.

user1427274 Over a year ago

will i still be able to insert html if I escape values?

Mark Byers Over a year ago

@user1427274: Yes of course. It's unlikely to work if you don't.

Anirudh Over a year ago

PDO and Prepared Statements in case you're wondering what they are. You could also use an ORM

Stranger · Accepted Answer · 2012-06-06 05:50:51Z

0

Use it like this,

$query = "UPDATE insight SET name='".$name."' ,date='". $date . "' ,html='" . $html . "' ,shortdesc='" . $shortdesc . "' ,link='" . $link . "' ,target='" . $target . "' ,sort='" . $sort . "' WHERE id='" . $id . "'";

answered Jun 6, 2012 at 5:50

Stranger

10.7k18 gold badges84 silver badges121 bronze badges

2 Comments

user1427274 Over a year ago

used this, it set 'name' to 0, rather than the given "testing" and the rest of the values were unaffected.

Stranger Over a year ago

It may due to the data type of column you set. You should set that to text or varchar to accept non numeric values.

Wazan · Accepted Answer · 2012-06-06 05:55:21Z

0

Its working ... check now

 $id = $_POST['id'];
    $name = $_POST['name'];
    $date = $_POST['date'];
    $shortdesc = $_POST['shortdesc'];
    $link = $_POST['link'];
    $target = $_POST['target'];
    $sort = $_POST['sort'];
    $html = $_POST['html'];

        include('appvars.php');

       $query = "UPDATE insight SET name='".$name."' ,date='". $date . "' ,html='" . $html . "' ,shortdesc='" . $shortdesc . "' ,link='" . $link . "' ,target='" . $target . "' ,sort='" . $sort . "' WHERE id='" . $id . "'";



        mysqli_query($dbc, $query);

answered Jun 6, 2012 at 5:55

Wazan

5391 gold badge9 silver badges27 bronze badges

Comments

Jashwant · Accepted Answer · 2012-06-06 05:57:19Z

0

I aint a pro at mysql, but a try.

I guess id is an integer. So, dont quote it.

Try this,

$query = "UPDATE insight SET name='".$name."' , date='". $date . "' , html='" . $html . "' , shortdesc='" . $shortdesc . "' , link='" . $link . "' , target='" . $target . "' , sort='" . $sort . "' WHERE id=". $id ;

answered Jun 6, 2012 at 5:57

Jashwant

29.2k16 gold badges76 silver badges110 bronze badges

Comments

Jelly · Accepted Answer · 2012-06-06 06:15:01Z

0

I think the SQL syntax is not correct, you can use it like this: UPDATE tablename SET rowname = value , ....

answered Jun 6, 2012 at 6:15

Jelly

1115 bronze badges

Collectives™ on Stack Overflow

PHP script not updating mysql table

5 Answers 5

5 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related