cooking, programming and everyday life xrado

Wednesday, December 19, 2007

Generate INSERT and UPDATE mysql queries

Tired of writing long insert and update queries? I wrote a query that can generate it for you. Paste the query to phpmyadmin sql tab, edit the table and database and run it. The result is full insert/update for the row, just erase/edit unneeded parts and the query is ready for use, of course don't forget about escaping and filtering the data before they end in a query.
## generate INSERT query
SELECT CONCAT('INSERT INTO ',table_name,' (',GROUP_CONCAT( column_name SEPARATOR ',' ),') VALUES (',GROUP_CONCAT( "'$POST_",column_name,"'" SEPARATOR ',' ),')') AS insert_query
FROM information_schema.COLUMNS
WHERE table_name = 'table' AND TABLE_SCHEMA = 'database';
 
## generate UPDATE query
SELECT CONCAT('UPDATE ',table_name,' SET ',GROUP_CONCAT( column_name,"='$POST_",column_name,"'" SEPARATOR ',' )) AS update_query
FROM information_schema.COLUMNS
WHERE table_name = 'table' AND TABLE_SCHEMA = 'database';
I save a lot of time using this, not even saying about the typing mistakes I usually do by writing it manually.

Results: 
INSERT INTO simpletable (id,date,rating) VALUES ('$POST_id','$POST_date','$POST_rating')
UPDATE simpletable SET id='$POST_id',date='$POST_date',rating='$POST_rating'