Ygo's world About games, gaming and game development and coding in general

mySQL trick for update/insert

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:

SELECT GROUP_CONCAT(CONCAT('\`', `COLUMN_NAME`, '\`')) as str_columns
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='databasename'
AND `TABLE_NAME`='tablename';

The result should look like this
`column1`, `column2`, `column3`...,`columnN`

which can be copied an pasted into your code editor for a query looking like this
INSERT INTO `tablename` (`column1`, `column2`, `column3`...,`columnN`) VALUES ('value1','value2','value3'...,'valueN').
Though 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:

SELECT GROUP_CONCAT(CONCAT('\`', `COLUMN_NAME`, '\`=\'.$data["',COLUMN_NAME,'"].\'','\n')) as str_columns
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='databasename'
AND `TABLE_NAME`='tablename';


This is going to output a string like this:

`column1`='.$data["column1"].',
`column2`='.$data["column2"].',
`column3`='.$data["column3"].',
`column4`='.$data["column4"].',
`column5`='.$data["column5"].',

...

`columnN`='.$data["columnN"].'

This goes in a query of this form

INSERT INTO `tablename` SET
`column1`='.$data["column1"].',
`column2`='.$data["column2"].',
`column3`='.$data["column3"].',
`column4`='.$data["column4"].',
`column5`='.$data["column5"].',

...

`columnN`='.$data["columnN"].'

You have already noticed the '\n' 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' from CONCAT function and you will get the string without the carriage return characters. The reason:  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.

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.