Adding multiple values to multiple columns to SQL via PHP

Question

I have a table which contains 150 columns each row, so I need to insert 150 values.

I know the way to insert 3 columns would be like this:

$query="INSERT INTO table_name
(data1,data2,data3)
VALUES
('data_part_1', 'data_part_2', 'data_part_3')";

But if I had to insert 150 values it would be:

$query="INSERT INTO table_name
(data1,data2,data3,...,data150)
VALUES
('data_part_1', 'data_part_2', 'data_part_3',...,'data_part_150')";

And it takes a lot of time.

How can I do it more efficiently?

Thanks

Sounds like your database schemas need reworked to be normalised. That's a massive topic though, and imo the question is too vague to try and be specific about it. — Jonnix, Jun 10 '16 at 12:43
If you had an array that was in `field_name` => `value` pairs (which I hope you do!) AND you were using PDO (which you should be), then you could do something like this: http://stackoverflow.com/a/13508045/870729 — random_user_name, Jun 10 '16 at 12:48

Kenny Grage · Answer 1 · 2016-06-16T20:38:59.663

When you say it takes a lot of time, I am figuring that you are saying it takes too much time to write out 150 variables in a query rather than computing time (as 150 variables doesn't take that long). If this is the case, the best way to write this would be to put the column names in an array and the data (or as you call it, data_parts) in another array and use a for loop.

$database = "my_database";
$table = "my_table";
$data = "SELECT * FROM $database.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = $table";

$query = "";
for ($i = 1; $i <= 150; $i++) {
    $query .= "INSERT INTO table_name
        ($data[$i])
    VALUES
        ($data_part[$i]";
}

Side note: having 150 columns in a table is not the best way to design your database. You might want to think about recreating it into multiple tables.

Adding multiple values to multiple columns to SQL via PHP

1 Answers1