Error when trying to update a MySQL table

Question

I am trying to update my MySQL table but I keep getting this error:

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 'INTO kh_comments (id, author, comment, 'timestamp', abstract, date_of_abstract) ' at line 1

This is how I am trying to update the table kh_comments:

public function prepare($author, $arr) {
    $conn = new mysqli($this->servername, $this->username, $this->password, $this->db_name);

    foreach ($arr as $value) {
        if ($stmt = $conn->prepare("UPDATE INTO kh_comments (id, author, comment, timestamp, abstract, date_of_abstract) VALUES (?, ?, ?, ?, ?, ?)")) {
            $stmt->bind_param('dssdss', '1', $author, '', 0, $value['id'], $value['date']);
        }
        else {
            echo $conn->error;
        }
    }
}

How can I fix this?

Why don't you just read a simple documentation? This is not a logical error — Steven Dropper, Jul 20 '16 at 14:25
@Philipp I would like to insert a new row, if there is no row existing with the same values that I am giving. If a row exists with the same values, it should only be updated. — Reza, Jul 20 '16 at 14:26
Look at the manual for [INSERT .. ON DUPLICATE](http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html) — RiggsFolly, Jul 20 '16 at 14:36

score 1 · Answer 1 · edited Jul 21 '16 at 09:57

1

TIMESTAMP is a restricted keyword in MySQL.

Use backticks for your table- and fieldnames, ie

"UPDATE `kh_comments` (`id`, `author`, `comment`, `timestamp`, ..."

https://dev.mysql.com/doc/refman/5.7/en/keywords.html

edited Jul 21 '16 at 09:57

RiggsFolly

93,638
21
103
149

answered Jul 20 '16 at 14:46

Honk der Hase

2,459
1
14
26

Timestamp is a keyword, not a reserved word. Plus, when is that even the syntax of an update stmt ! – Drew Jul 22 '16 at 17:10

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

Use this way.

$stmt = $conn->prepare("UPDATE kh_comments SET id=?, author=?, comment=?, timestamp=?, abstract=?, date_of_abstract=?";
if ($stmt) {             
    $stmt->bind_param('dssdss', '1', $author, '', 0, $value['id'], $value['date']);

}

For more info, click Prepare Statement For Update Query

edited May 23 '17 at 12:32

Community

1
1

answered Jul 20 '16 at 14:23

Nana Partykar

10,556
10
48
77

Update is in a foreach. So you only need to prepare the query ONCE and bind then execute in the loop – RiggsFolly Jul 20 '16 at 14:25
Ok OK @RiggsFolly. Didn't saw that. *~Thanks* – Nana Partykar Jul 20 '16 at 14:26
You might want to add a `->execute()` in there somewhere – RiggsFolly Jul 20 '16 at 14:30

score 0 · Answer 3 · edited Jul 20 '16 at 14:26

0

I think your update syntax is incorrect try using below function :

public function prepare($author, $arr) {
    $conn = new mysqli($this->servername, $this->username, $this->password, $this->db_name);

    foreach ($arr as $value) {
        if ($stmt = $conn->prepare("UPDATE kh_comments (id, author, comment, timestamp, abstract, date_of_abstract) VALUES (?, ?, ?, ?, ?, ?)")) {             
            $stmt->bind_param('dssdss', '1', $author, '', 0, $value['id'], $value['date']);
        }
        else {
            echo $conn->error;
        }       
    }
}

edited Jul 20 '16 at 14:26

RiggsFolly

93,638
21
103
149

answered Jul 20 '16 at 14:25

Ankush Gautam

139
3

You have no `->execute()` you might want to add one of those! Also move `->prepare()` outside the loop. It only needs running once – RiggsFolly Jul 20 '16 at 14:29

score 0 · Answer 4 · answered Jul 21 '16 at 10:11

As per your comment

I would like to insert a new row, if there is no row existing with the same values that I am giving. If a row exists with the same values, it should only be updated

You need to use the INSERT.... ON DUPLICATE UPDATE syntax to do that

You also can prepare the statement once outside the loop and then execute it many times with new parameters.

Also as one of your column names is timestamp which is a MYSQL reserved word, that column needs to be wrapped in backticks, or better still renamed

public function prepare($author, $arr) {
    $conn = new mysqli($this->servername, $this->username, $this->password, $this->db_name);


    $sql = "INSERT INTO `kh_comments` 
                   (`id`, `author`, `comment`, `timestamp`, `abstract`, `date_of_abstract`) 
            VALUES (?, ?, ?, ?, ?, ?) 
            ON DUPLICATE 
              UPDATE `author` = ?, `comment`=?, `timestamp`=?, 
                     `abstract`=?, `date_of_abstract`=?
              WHERE `id` = ?";
    $stmt = $conn->prepare($sql);

    foreach ($arr as $value) {
        $stmt->bind_param('dssdssssdssd', 
                           '1', $author, '', NOW(), $value['id'], $value['date'],
                           $author, '', NOW(), $value['id'], $value['date'], '1' );
        $stmt->execute()
        if ( $stmt === false )
            echo $conn->error;
            exit;
        }
    }
}

Of course this code will only work once as you are using the '1' as the id in all the rows you are trying to store.

score -1 · Answer 5 · answered Jul 20 '16 at 14:26

-1

Your UPDATE statement syntax wrong, for your reference I have tried to correct your code.

public function prepare($author, $arr) {
    $conn = new mysqli($this->servername, $this->username, $this->password, $this->db_name);
    $sql="UPDATE kh_comments SET author= ?, comment = ?, timestamp= ?, abstract = ?, date_of_abstract= ? WHERE id= ?";
    foreach ($arr as $value) {    
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('1', $author, '', 0, $value['id'], $value['date'],'dssdss');
        $stmt->execute();
        if ($stmt->errno) {
          echo "FAILURE!!! " . $stmt->error;
        }
        else echo "Updated {$stmt->affected_rows} rows";
    }
}

Please check order of parameters which I have defined. and make change as you need

answered Jul 20 '16 at 14:26

Haresh Vidja

8,340
3
25
42

Move the prepare outside the foreach loop – RiggsFolly Jul 20 '16 at 14:27
Your `bind_param()` is nonsense – RiggsFolly Jul 20 '16 at 14:34
@RiggisFolly, can you explain how it nonsense? – Haresh Vidja Jul 21 '16 at 06:09
Look at your `bind_param()` statement and compare it with [the manual](http://php.net/manual/en/mysqli-stmt.bind-param.php) – RiggsFolly Jul 21 '16 at 09:54
Also see that OP wants to do a INSERT if no row exists and an UPDATE if a row does exist – RiggsFolly Jul 21 '16 at 09:55

Error when trying to update a MySQL table

5 Answers5