Jump to content

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


thara

Recommended Posts

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 useravailability (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);

Link to comment
Share on other sites

Why not doing a simple Lookup first to see if the userid exists and then do the Update or insert?

 

Yes Its ok. I have already done it. But reason to post here, I am looking for a solution with single query for both insert and update using INSERT ... ON DUPLICATE KEY UPDATE.

Link to comment
Share on other sites

@Barand, Thanks for response. 

 

I tried it, this is my table structure -

CREATE TABLE availability (
    availabilityid SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    userid SMALLINT UNSIGNED NOT NULL,
    days VARCHAR(10) NOT NULL,
    opentime VARCHAR( DEFAULT NULL,
    closetime VARCHAR( DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE (userid, days)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Link to comment
Share on other sites

yes I tried it like this and it works me. 

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) ;

is my way correct? or is there any to do this?

Link to comment
Share on other sites

Here is a solution which will surely help you...

This is the example which i tried..

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) ; "; 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.