PHP MYSQL Update query

Question

I currently have the following update statement but is there anyway that I can make it retain the current values but insert and new values that are not in the db?

If not what would be the best way to achieve this?

UPDATE INTO {refocus_candidate_category} SET canid=?, categoryid=? WHERE canid=? AND categoryid=?",array($emailCheck['id'], $id, $emailCheck['id'], $id));

Function:

$catParams = array_merge(array($emailCheck['id']), $fields['Occupation']);
$catPlaceholders = '?'.str_repeat(',?',count($fields['Occupation'])-1);

$catCheck = CMS::selectQuery("SELECT * FROM {table} WHERE canid=? AND categoryid IN (".$catPlaceholders.")", $catParams);

if($catCheck != FALSE)
{
    for($i=0; $i<count($fields['Occupation']); $i++) {
        $id = $fields['Occupation'][$i];

        CMS::updateQuery("UPDATE INTO {table} SET canid=?, categoryid=? WHERE canid=? AND categoryid=?",array($emailCheck['id'], $id, $emailCheck['id'], $id));
    }
        echo 'found update';
}

ID Print

    $fields['Occupation'][$i] = 1678

That's a fragment (it has two closing brackets but no opening bracket). Can you post the whole line? — msanford
– msanford, Commented Sep 21, 2012 at 22:16
@msanford I have now updated with the whole function that is relevant to my question. I am just unsure what code like in the answer below is good in my situation — Jess McKenzie
– Jess McKenzie, Commented Sep 21, 2012 at 22:20

Jon Marnock · Accepted Answer · 2012-09-21 22:16:53Z

2

It's not clear to me from your question precisely what you mean, but there are a number of alternatives for inserts/updates that deal with missing or already present values.

Firstly, if you just want to insert into mysql and have it either create a new row or replace an existing row (where existing is determined by the primary key matching) use REPLACE INTO instead of INSERT INTO. REPLACE INTO tries an insert, but if the primary key already exists, it turns the query into a DELETE and then retries the INSERT.

If you want to insert a new row but leave an existing row alone if you've already got one, you can either use INSERT IGNORE INTO (which may also fail to insert if you've got your data types or column info wrong...) or INSERT INTO ... ON DUPLICATE KEY UPDATE which allows you to do much finer grained control of how you handle inserts of items that already exist.

There's other options as well, but those are probably the most relevant.

answered Sep 21, 2012 at 22:16

Jon Marnock

3,2451 gold badge22 silver badges15 bronze badges

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

8 Comments

Jess McKenzie Over a year ago

Thanks Jon, I have now updated my question with the if statement I have tried to use INSERT IGNORE INTO but I cannot seem to get it going

msanford Over a year ago

Jon, Jess is asking how to partially update a row that already contains some data.

Alain Collins Over a year ago

In that case, ON DUPLICATE KEY will let you update only the columns you want when the unique key is violated.

Jess McKenzie Over a year ago

@AlainCollins I have tried using the IGNORE but get the following - I have changed the above to an insert d.pr/i/1vWK

Alain Collins Over a year ago

I think your catgeoryid is " 1 ' " - with an extra quote.

|

Collectives™ on Stack Overflow

PHP MYSQL Update query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related