php - How to insert multiple rows and columns in database using array

admin管理员组

文章数量:1335877

Please i want to know if it is posible to use one sql query to insert into multiple columns and rows using array in php
$sql = "INSERT INTO MyGuests (firstname, lastname, email) <br> VALUES ('John', 'Doe', '[email protected]');";
but i want to represent column names in array as arrays and then values as array values, something that will look like this in php
$sql = "INSERT INTO myguest (array(firstname => 'john'));

Please i want to know if it is posible to use one sql query to insert into multiple columns and rows using array in php
$sql = "INSERT INTO MyGuests (firstname, lastname, email) <br> VALUES ('John', 'Doe', '[email protected]');";
but i want to represent column names in array as arrays and then values as array values, something that will look like this in php
$sql = "INSERT INTO myguest (array(firstname => 'john'));

• php
• mysql
• array

Share Improve this question asked May 27, 2020 at 8:28 pandglobalpandglobal 4311 silver badge99 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

That is not an accepted SQL syntax per the MySQL manual. You must

INSERT INTO MyGuests
        (firstname, lastname,   email)
VALUES  ('John',    'Doe',      '[email protected]')

But, and this is an expansion well beyond the scope of your question, if you have an array of this information, you could iterate over the whole thing to build your SQL string.

// The array of information
$guests = array(
    array(
        'first_name'    => 'John',
        'last_name'     => 'Doe',
        'email'         => '[email protected]',
    ),
    array(
        'first_name'    => 'Jane',
        'last_name'     => 'Doe',
        'email'         => '[email protected]',
    ),
);
// Build the SQL string
$sql = '';
foreach($guests as $g) {
    if($sql != '') $sql.= ',';
    $sql .= '("'. $g['first_name'] .'", "'. $g['last_name'] .'", "'. $g['email'] .'")';
}
if($sql != '') {
    $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ". $sql;
}
// $sql now is either empty or full of lots of records to insert.

I used the description above to figure out a more better and simpler way of doing this without using arrays, even though you may want to use array, it still looks thesame way.

Below is the Code

$column = '(firstname, lastname, email)';  // here i can choose any column i wish
$value = "
    ('John', 'Doe', '[email protected]'),
    ('Jane', 'Doke', '[email protected]'),
    ('John3', 'Doe3', '[email protected]'),
    ('Jane4', 'Doke4', '[email protected]')
     ";


$sql = "INSERT INTO a_boy ". $column . " VALUES ". $value;  

This inserts a four row data into the database table called a_boy, so i can only keep extending the values to as many number of rows and details i wish to insert, and this makes my code simple and easy to use, since the goal was to insert many columns and many rows using one single query.

本文标签： phpHow to insert multiple rows and columns in database using array