{"id":183,"date":"2014-08-09T15:10:08","date_gmt":"2014-08-09T13:10:08","guid":{"rendered":"http:\/\/www.ygo.ro\/?p=183"},"modified":"2017-06-19T12:32:06","modified_gmt":"2017-06-19T10:32:06","slug":"mysql-trick-for-updateinsert","status":"publish","type":"post","link":"https:\/\/ygo.ro\/?p=183","title":{"rendered":"mySQL trick for update\/insert"},"content":{"rendered":"

I found myself often in the position of updating or inserting things in a database table. Writing the queries for such operations is a really tedious job, especially when the table have a lot of columns. Being a lazy creature I've decided to let the computer do half of my job and extract the columns from the table, ready to be copied into my code:
\n
\nSELECT <\/span><\/strong>GROUP_CONCAT<\/span>(CONCAT<\/span>('\\`', `COLUMN_NAME<\/span>`, '\\`')) as <\/strong><\/span>str_columns
\nFROM <\/strong><\/span>`INFORMATION_SCHEMA<\/span>`.`COLUMNS<\/span>`
\nWHERE <\/span><\/strong>`TABLE_SCHEMA<\/span>`='databasename<\/span>'
\nAND <\/strong><\/span>`TABLE_NAME`='tablename<\/span>';<\/code><\/p>\n

The result should look like this
\n`column1`, `column2`, `column3`...,`columnN`<\/em><\/p>\n

which can be copied an pasted into your code editor for a query looking like this
\nINSERT INTO `tablename` (`column1`, `column2`, `column3`...,`columnN`<\/em>) VALUES ('value1','value2','value3'...,'valueN'). <\/strong>
\nThough this is the default form of SQL encountered, I prefer to use the update like syntax because matching the values with columns is much more easier and less error prone. Here is the code to extract columns from a table:
\n
\nSELECT <\/strong><\/span>GROUP_CONCAT<\/span>(CONCAT<\/span>('\\`', `COLUMN_NAME<\/span>`, '\\`=\\'.$data<\/span><\/strong>[\"',COLUMN_NAME<\/span>,'\"].\\'','\\n')) as <\/strong><\/span>str_columns
\nFROM <\/strong><\/span>`INFORMATION_SCHEMA<\/span>`.`COLUMNS<\/span>`
\nWHERE <\/strong><\/span>`TABLE_SCHEMA<\/span>`='databasename<\/span>'
\nAND <\/strong><\/span>`TABLE_NAME<\/span>`='tablename<\/span>';
\n<\/code><\/p>\n

This is going to output a string like this:<\/p>\n

`column1`='.$data[\"column1\"].',
\n`column2`='.$data[\"column2\"].',
\n`column3`='.$data[\"column3\"].',
\n`column4`='.$data[\"column4\"].',
\n`column5`='.$data[\"column5\"].',<\/p>\n

...<\/p>\n

`columnN`='.$data[\"columnN\"].'<\/p>\n

This goes in a query of this form<\/p>\n

INSERT INTO `tablename` SET
\n`column1`='.$data[\"column1\"].',
\n`column2`='.$data[\"column2\"].',
\n`column3`='.$data[\"column3\"].',
\n`column4`='.$data[\"column4\"].',
\n`column5`='.$data[\"column5\"].',<\/strong><\/p>\n

<\/strong><\/p>\n

...<\/strong><\/p>\n

<\/strong><\/p>\n

`columnN`='.$data[\"columnN\"].'<\/strong><\/p>\n

You have already noticed the '\\n'<\/strong> character concatenated at the end of each assignment. Is the way I am keeping my queries in the code to easy spot all the variables. Some people might not like the wast of space and feel better with a more confined style. Just remove the '\\n'<\/strong> from CONCAT function and you will get the string without the carriage return characters. The reason:\u00a0 is very easy to debug a query in this way and my editor can collapse portions of code based on quote, parenthesis and many more entities.<\/p>\n

You will also notice the fact that I have used the a parameter for values $data[\"column1...N\"]. This is my convention: I pass data in a variable called $data and my fields have the name of column and thus making it very easy to automate the writing of a query. Not to mention that, memorizing the name of the columns once and using them everywhere is much easier than translating the name of the columns every time to a new variable name for each script where you access the database.<\/p>\n","protected":false},"excerpt":{"rendered":"

I found myself often in the position of updating or inserting things in a database table. Writing the queries for such operations is a really tedious job, especially when the table have a lot of columns. Being a lazy creature I’ve decided to let the computer do half of my job and extract the columns […]<\/p>\n","protected":false},"author":1,"featured_media":253,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46,63,49,64],"tags":[],"_links":{"self":[{"href":"https:\/\/ygo.ro\/index.php?rest_route=\/wp\/v2\/posts\/183"}],"collection":[{"href":"https:\/\/ygo.ro\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ygo.ro\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ygo.ro\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ygo.ro\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=183"}],"version-history":[{"count":13,"href":"https:\/\/ygo.ro\/index.php?rest_route=\/wp\/v2\/posts\/183\/revisions"}],"predecessor-version":[{"id":254,"href":"https:\/\/ygo.ro\/index.php?rest_route=\/wp\/v2\/posts\/183\/revisions\/254"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ygo.ro\/index.php?rest_route=\/wp\/v2\/media\/253"}],"wp:attachment":[{"href":"https:\/\/ygo.ro\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ygo.ro\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ygo.ro\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}