Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
1  
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

8 Answers 8

up vote 6 down vote accepted

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.

share|improve this answer
    
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
2  
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
    
@TNM - Thanks that's useful to know. –  Joe Sep 12 '11 at 18:32
    
@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
1  
-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

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.

share|improve this answer
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

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.

share|improve this answer

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) 
VALUES
(val11, val12, val13, ........, val189, val190),
(val21, val22, val23, ........, val289, val290),
.......
......
(val101, val102, val103, ........, va1089, val1090);
share|improve this answer

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.

share|improve this answer

With MySQL you can use alternate syntax for insert statements:

insert into table
        set column1 = value1
          , column2 = value2
          , column3 = value3
share|improve this answer

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
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
share|improve this answer

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:

SELECT MAX(id) FROM .....

add 1 to id and insert it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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