CODEWITHSUNDEEP
phpmysql
angelcool.net
angelcool.net

Reputation: 2546

INSERT ...ON DUPLICATE KEY UPDATE when key is not always known?

I'm trying to update a record if the key is known else I want to insert it and get the inserted id, currently I have:

    if(isset($data['applicationId']))
    {
        //update
        $sql="
         UPDATE myTable SET data='jsonstring' WHERE id = {$data['applicationId']}
        ";
    }
    else
    {
        //insert and get id
        $sql="
         INSERT INTO myTable SET data='jsonstring'
        ";
    }

Is it possible to simplify the above to one query using INSERT ...ON DUPLICATE KEY UPDATE even when the key is not always known ?

I've tried this:

        INSERT INTO myTable
        (
            id,
            data

        )
        VALUES
        (
            ?, # <- I may not know this!!
            'jsonstring'
        )
        ON DUPLICATE KEY UPDATE 
            data = 'jsonstring'

Thanks for any suggestions.

Upvotes: 1

Views: 362

Answers (3)

Nana Partykar
Nana Partykar

Reputation: 10548

First, Simple update query will run. If it runs successfully, it will not go to if condition and your ID will be the one which was used in updating.

And, if that ID is not available (means update query fails, $Query will be false), so pointer jumps to if condition and insert the query. Now, new Inserted ID we can get.

$ID=$data['applicationId'];
    $Query=mysql_query("UPDATE myTable SET data='jsonstring' WHERE id='$ID' ");
    if(!$Query)
    {
       $InsertQuery=mysql_query("INSERT INTO myTable SET data='jsonstring'");
       $ID=mysql_insert_id();
    }

    So, $ID will be your ID.(either updated or currently inserted)

Upvotes: 0

skroczek
skroczek

Reputation: 2309

Yes, you can do that, assumed id is your primary key and auto_increment. You will have two different queries, one if you know the applicationId and one when you not knowing it.

The first, when you know it:

INSERT INTO myTable
(
    id,
    data
)
VALUES
(
    1337, # <- insert id
    'jsonstring'
)
ON DUPLICATE KEY UPDATE 
        data = 'jsonstring';

And the one if the applicationId is unknown:

INSERT INTO myTable
(
    id,
    data
)
VALUES
(
    NULL, # <- This will cause mysql to use a auto_increment value
    'jsonstring'
)
ON DUPLICATE KEY UPDATE 
        data = 'jsonstring';

So you can conclude this to:

    $sql="INSERT INTO myTable
        (
            id,
            data
        )
        VALUES
        (" .
            isset($data['applicationId']) ? $data['applicationId'] : 'NULL'
            .",
            'jsonstring'
        )
        ON DUPLICATE KEY UPDATE 
                data = 'jsonstring';
    ";

But be aware of How can I prevent SQL-injection in PHP?

Happy coding

Upvotes: 1

kayleighsdaddy
kayleighsdaddy

Reputation: 670

Please forgive because your question is not 100% clear. However, the concept I can tell is that you want to be able to ask more than 1 query on 1 sql statement. That can be done with a multi-query command. However, if you want some of your data from a query placed in your next query I do not think it will work. Link provided for multi_query

http://php.net/manual/en/mysqli.quickstart.multiple-statement.php

Upvotes: 0

Related Questions