CODEWITHSUNDEEP
phpmysqlsqlinsert-update
TNK
TNK

Reputation: 4323

how I make a single query for both insert and update?

Suppose we have a mysql table something like this

id, userid, days, start, and close

and we have data for each columns like this - 

1, 3, mon, 01.00, 02.00,
2, 3, tue, 03.00, 05.00,
3, 3, wed, 04.00, 06.00,
4, 3, thu, 05.00, 07.00,
5, 3, fri, 06.00, 08.00,
6, 3, sat, 07.00, 10.00,
7, 3, sun, 08.00, 12.00,

with this data, I need to update or insert my table. (if userid is not exist in table it should be insert or if userid exist in db it should be update.)

Can I know, is there a way to make a mysql single query for this? I tried it with INSERT ... ON DUPLICATE KEY UPDATE there I can only edit single row that mean I can not insert or update table with multiple rows using INSERT ... ON DUPLICATE KEY UPDATE.

At the moment I have used 2 different queries for inset and update

This is my insert query -

$q = "INSERT INTO availability (userid, days, opentime, closetime) 
        VALUES (?, 'Monday', ?, ?),
                 (?, 'Tuesday', ?, ?),
                 (?, 'Wednesday', ?, ?),
                 (?, 'Thursday', ?, ?),
                 (?, 'Friday', ?, ?),       
                 (?, 'Saturday', ?, ?),                         
                 (?, 'Sunday', ?, ?)";                          
            $stmt = mysqli_prepare($dbc, $q);
            mysqli_stmt_bind_param($stmt, 'issississississississ', 
                                            $userId, $monOpen, $monClose, 
                                            $userId, $tueOpen, $tueClose,
                                            $userId, $wedOpen, $wedClose,
                                            $userId, $thuOpen, $thuClose,
                                            $userId, $friOpen, $friClose,
                                            $userId, $satOpen, $satClose,
                                            $userId, $sunOpen, $sunClose);                          
            // Execute the query:
            mysqli_stmt_execute($stmt);

Upvotes: 0

Views: 3640

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

If you add a unique index on (userid, days):

-- run this only once
ALTER TABLE  availability 
  ADD UNIQUE INDEX userid_days_UQ         -- just a name for the index
    (userid, days) ;

then you can use the ON DUPLICATE KEY UPDATE syntax:

$q = "

      INSERT INTO availability 
          (userid, days, opentime, closetime) 
      VALUES 
          (?, 'Monday', ?, ?),
          (?, 'Tuesday', ?, ?),
          (?, 'Wednesday', ?, ?),
          (?, 'Thursday', ?, ?),
          (?, 'Friday', ?, ?),       
          (?, 'Saturday', ?, ?),                         
          (?, 'Sunday', ?, ?)
      ON DUPLICATE KEY UPDATE
          opentime = VALUES(opentime),
          closetime = VALUES(closetime) ;

     ";

Upvotes: 1

DesertEagle
DesertEagle

Reputation: 599

Having separate queries is a very common practice, there is nothing wrong with it. However, at least mySQL offers a replace into command:

REPLACE INTO availability
    (userid, days, opentime, closetime) VALUES
    (?,    'Monday', ?,         ?);

The only drawback is, that you can not specify a where clause.

see https://stackoverflow.com/a/3046980/1596455 as well.

Upvotes: 1

Related Questions