CS 601 – MySQL INSERT … SET syntax

While working on my project, I came across the following. The standard syntax for inserting items into a database is something akin to:

INSERT INTO tblMyTable (FirstName, LastName, Email, Phone)
     VALUES ('John', 'Doe', 'johndoe@gmail.com', '1234567890')

This is standard SQL. However, MySQL has an alternative syntax using SET which is similar to the UPDATE syntax:

INSERT INTO tblMyTable SET
       FirstName = 'John',
       LastName  = 'Doe',
       Email = 'johndoe@gmail.com',
       Phone = '1234567890'

I ended up using the alternative syntax throughout my project. Why? I felt it is much cleaner and easier to read than the standard syntax, especially when removing a column to insert. (When adding a column, it’s easy enough to add them to the end of the lists.) It should also greatly reduce errors from incorrect ordering between column names and values since they’re right next to each other. I realize that using MySQL specific syntax would break the site if it was moved to another database, but I don’t feel that was a concern for this project. In the real world, especially dealing with projects that are commercial and may/will grow larger, it might not be a good idea.

Here’s an example from my actual code:

$query = <<<EOT
    INSERT INTO tblReservations SET
        dtReservationDateTime = FROM_UNIXTIME($datetimeunix),
        intPartySize = {$_POST['numparty']},
        intCustomerID = "{$_POST['userid']}",
        vcReservationNameFirst = "{$_POST['firstname']}",
        vcReservationNameLast = "{$_POST['lastname']}",
        vcReservationEmail = "{$_POST['email']}",
        chReservationPhone = $phone,
        vcComments = "{$_POST['comment']}",
        dtReservationDateTimeMade = NOW()
EOT;

Leave a Reply

Your email address will not be published.