As part of a PHP project, I have to insert a row into a MySQL database. I'm obviously used to doing this, but this required inserting into 90 columns in one query. The resulting query looks horrible and monolithic (especially inserting my PHP variables as the values):
INSERT INTO mytable (column1, colum2, ..., column90)
VALUES
('value1', 'value2', ..., 'value90')
and I'm concerned that I'm not going about this in the right way. It also took me a long (boring) time just to type everything in and testing writing the test code will be equally tedious I fear.
How do professionals go about quickly writing and testing these queries? Is there a way I can speed up the process?
Best Answer
Joe, your last comment explained a lot. I think the real problem is the data design. New columns may be needed when the document format changes, and in my experience document formats tend to change frequently. Instead of a 90-column table, with a single row per report, I would store the report data in a table with four columns: report_id, format_id, field_name, field_value. Each report would be represented by 90 rows, one for each field value in the report. This should simplify your code considerably.