php - IF EXISTS it should UPDATE the row, ELSE INSERT INTO a new row. How to solve this error?

416

How to solve 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 'update user_options set value='1' where name='slider';INSERT INTO user_options (' at line 2

Code

public function update($slider, $welcomebox, $servicebox, $postbox, $testimonials, $welcomemessage, $welcomededcription, $welcomelink, $welcomelinktitle, $servicetitle, $totalservicedisplay, $ourservice, $blogtitle, $totalblogdisplay, $ourblog, $newstitle, $totalnewsdisplay, $ournews, $totaltestimonialdisplay, $ourtestimonial) {
    $db = connectionstart();
    $currentdatetime = date("Y-m-d H:i:s", time());

    $sql = "select * from user_options ";
    $result = mysql_evaluate($db, $sql, 0);

    $columns = array(
        array(
            'name' => 'slider'
            , 'value' => $slider
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'welcomebox'
            , 'value' => $welcomebox
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'servicebox'
            , 'value' => $servicebox
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'postbox'
            , 'value' => $postbox
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'testimonials'
            , 'value' => $testimonials
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'welcomemessage'
            , 'value' => $welcomemessage
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'welcomededcription'
            , 'value' => $welcomededcription
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'welcomelink'
            , 'value' => $welcomelink
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'welcomelinktitle'
            , 'value' => $welcomelinktitle
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'servicetitle'
            , 'value' => $servicetitle
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'totalservicedisplay'
            , 'value' => $totalservicedisplay
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'ourservice'
            , 'value' => $ourservice
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'blogtitle'
            , 'value' => $blogtitle
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'totalblogdisplay'
            , 'value' => $totalblogdisplay
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'ourblog'
            , 'value' => $ourblog
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'newstitle'
            , 'value' => $newstitle
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'totalnewsdisplay'
            , 'value' => $totalnewsdisplay
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'ournews'
            , 'value' => $ournews
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'totaltestimonialdisplay'
            , 'value' => $totaltestimonialdisplay
            , 'entrydate' => $currentdatetime
        ),
        array(
            'name' => 'ourtestimonial'
            , 'value' => $ourtestimonial
            , 'entrydate' => $currentdatetime
        )
    );




            $results = mysql_updateoptionfields($db, $columns);
            if ($results == true) {
                $results = 'SUCCESS';
            }


    connectionclose($db);
    return $result;
}

Function mysql_updateoptionfields

function mysql_updateoptionfields($db, $columns) {
    $sql = "";
    foreach ($columns as $obj) {
        $sql.="INSERT INTO user_options (name,value,entrydate) VALUES ('{$obj['name']}','{$obj['value']}','{$obj['entrydate']}') ON DUPLICATE KEY UPDATE name=values(name);
                update user_options set value='{$obj['value']}' where name='{$obj['name']}';";
    }
    if ($sql != '') {
        $result = mysql_query($sql, $db) or die(mysql_error());
    }
    return $result;
}

I want to insert data if exixts otherwise update based on name

476

Answer

Solution:

Try this

INSERT INTO subs
  (col1,col2,col3)
VALUES
  (?, ?, ?)
ON DUPLICATE KEY UPDATE
  Col1     = VALUES(value1),
  Col2     = VALUES(value2);

As per your question

INSERT INTO values
  (name,value,entrydate)
VALUES
('{$obj['name']}','{$obj['value']}','{$obj['entrydate']}')
ON DUPLICATE KEY UPDATE
  value     = VALUES(value),
  entrydate     = VALUES(entrydate)
953

Answer

Solution:

"INSERT … ON DUPLICATE KEY UPDATE” use this. It will help you to fix ur problem. http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

212

Answer

Solution:

There are 3 problems in your code:

  1. You should not use mysql_*() functions any longer, they are deprecated and removed from php7.
  2. mysql_query() cannot execute more than 1 sql statements in a call. This is the reason for your syntax error message. Remove the separate update statement.
  3. In theon duplicate key update part of the sql statement you should assign value to the value field, not to the name field.

    ... value='{$obj['value']}'

People are also looking for solutions to the problem: node.js - Compare Nodejs generated bcrypt hash in PHP

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.