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) 
('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?

  • 2
    I'm more concerned that the table has 90 columns than the trivial amount of time spent typing in column names. (BTW I drag and drop all columns at once in SQL Server, is there not aplace to do the same in mySQL or PHP? I'd look to see if you can find that it makes life easier as there are no typos.) – HLGEM Sep 6 '11 at 15:22
  • 1
    I know 90 columns is a lot, but each column relates to a single field for a pdf document that I need to populate and I don't see the point in breaking it up, or how I would do so. Thanks for the info about SQL Server. I'm not sure quite what you mean about dragging and dropping the columns buut I'll have a look. – Joe Sep 6 '11 at 15:35
  • 1
    Write a select statement that lists all the columns in a given table and go from there. – JeffO Sep 6 '11 at 19:32
  • Jeff O: I've used that too, it can be a very powerful technique if done right. You should post that as an answer if you can give a code example! – FrustratedWithFormsDesigner Sep 6 '11 at 19:47

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.

  • Thanks for your reply. All of fields (apart from the index) are VARCHARS, so that would work for me (and I could convert other values anyway). I might be wasting a lot of space though because I'd have to have the size of the field_value column set to the largest value (about 256 chars long) whereas some fields only require a length of 3. It would certainly be easier to use and I can understand how it would be more future proof as you described. – Joe Sep 6 '11 at 16:46
  • 4
    FWIW, most database systems only use as much space as required to store data. So if you store only 3 characters in a VARCHAR(256) field, it will only take 3 bytes, not 256. I don't know much about MySQL internals, but I'd be surprised if they padded their fields out to the full declared size. – TMN Sep 9 '11 at 20:08
  • @TMN That's what the VAR in VARCHAR means! Variable Length Char. This is a function (or the definition) of the Data type not the DB system. Also not that a because a VARCHAR is Variable Length, the DB needs to know the length for each value, so it stores the length as metadata. That means overhead storage! So a VARCHAR(1) actually uses 3 bytes of data because of the overhead, 3x as much as a Char(1)! – Morons Sep 27 '12 at 6:52
  • 2
    -1, I disagree with this answer, In this case you are better off with 90 columns. If the entity has 90 data points, then so be it, keep your data rational. – Morons Sep 27 '12 at 6:56
  • @TMN just to clarify my point, said "So if you store only 3 characters in a VARCHAR(256) field, it will only take 3 bytes" The truth is it will take up 5 bytes not 3. – Morons Sep 27 '12 at 7:02

In general, the fastest way to load a large dataset into a SQL database is to use the native bulk loading interface. As far as I know, every SQL dbms has at least one.

MySQL docs: Using the Bulk Loader

If I have to turn a tab- or comma-delimited file into SQL INSERT statements, I use awk to read the input file and write the output file. There's nothing really special about awk; it just happens to be the text-processing language I know best. You could get the same results by writing code in Perl, Python, Ruby, Rexx, Lisp, and so on.

  • 2
    Bulk loading is indeed the way to go if you need to insert a large number of rows, but in this case he's just inserting a single row with lots of columns. Bulk loading won't help, and will probably require writing more code than the straightforward approach. – TMN Sep 6 '11 at 18:37
  • -1, this answer is completely missing the point of the question – Doc Brown Jan 22 '17 at 8:04

If you can easily get the column names into an Excel spreadsheet, you could write Excel macros to produce code for various queries and DML statements, then just paste values into another column and your insert/update statement is created automatically for you. Manually typing is a very slow way to do it, so see if you can find tricks using your existing tools. Many developer-oriented text editors also have the ability to record and store macros to make repetitive jobs like this much faster and easier.


If you have a csv file, you can use the LOAD DATA INFILE... to import the data.

If you have to use 'INSERT' queries, then doing bulk inserts will speed up the process. Instead of running an 'INSERT' query for every single row, group the rows, say 100 and run the query. Something like this:

INSERT INTO theTable (col1, col2, col3,....., col89, col90) 
(val11, val12, val13, ........, val189, val190),
(val21, val22, val23, ........, val289, val290),
(val101, val102, val103, ........, va1089, val1090);

An efficient way to write a multi-columns query data into MySQL DB is to convert these data into JSON or YAML format and insert them as a single unit. It changes "write an insert for a table with 90 columns" into "write an insert into a table with one column".

In this approach, not everything needs to be broken down into it's base components, and the single datum is stored just into 1 column.

  • @gnat: it offers an alternative solution. It changes "write an insert for a table with 90 columns" into "write an insert into a table with one column". Given the problem as described, it is a valid solution. Not everything needs to be broken down into it's base components. The only other similar answer, suggested going full NoSQL, eliminating the SQL database entirely, which is overkill. This answer says that you can use a mixed approach. Make just 1 column for this single datum. Consider that the alternative might be to have a binary column and store the whole pdf. – jmoreno Jan 22 '17 at 15:06
  • @gnat: I'll give Noviff the chance to put it in his own words... – jmoreno Jan 22 '17 at 15:11
  • @ gnat and jmoreno - thank you for your comments. I like gnat's clarification of my answer, and I edited the answer based on his clarification. – Noviff Jan 22 '17 at 16:54

With MySQL you can use alternate syntax for insert statements:

insert into table
        set column1 = value1
          , column2 = value2
          , column3 = value3
  • 1
    Is this actually faster? – Pacerier Apr 8 '15 at 15:44
  • @Pacerier No, this is not faster. Just another syntax. – Kaspars Foigts Aug 3 '15 at 13:41

Your scenario looks like a very good fit for a NoSQL solution, since the attribute list can change anytime the format changes. Have you evaluated other options than MySQL? Dig around DynamoDB/MongoDB/Cassandra - that might be a better fit.


There is more efficient way to insert data into database using php and mysql. We can use LOAD COMMAND to insert the data. It inserts data remarkably fast.

For this create a flat file (for example I used .csv file) with your data using fputcsv() function. Then insert data using LOAD command. Syntax some what similar as below:

LOAD DATA LOCAL INFILE "C:/downloads/local/my_data_file.csv"
INTO TABLE  my_data

Try the following. Worked for me.

Form names must equal to database column names

Get the values as below:

foreach ($_GET as $formName => $value) {
    $sql = mysql_query("UPDATE table_name SET $formName = '$value' WHERE ID= $id");

You will first need to insert an ID before the foreach loop. you can get next id by doing:


add 1 to id and insert it.

Not the answer you're looking for? Browse other questions tagged or ask your own question.