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
– Jonnix, Commented Jun 10, 2016 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: stackoverflow.com/a/13508045/870729 — random_user_name
– random_user_name, Commented Jun 10, 2016 at 12:48

Kenny Grage · Accepted Answer · 2016-06-16 20:38:59Z

1

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.

edited Jun 16, 2016 at 20:38

answered Jun 16, 2016 at 18:44

Kenny Grage

1,1249 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding multiple values to multiple columns to SQL via PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related