0

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', 'john@example.com');";
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'));

0

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

INSERT INTO MyGuests
        (firstname, lastname,   email)
VALUES  ('John',    'Doe',      'john@example.com')

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'         => 'john@example.com',
    ),
    array(
        'first_name'    => 'Jane',
        'last_name'     => 'Doe',
        'email'         => 'jane@example.com',
    ),
);
// 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.
2
  • 1
    Thanks alot @Mort-1305 your code worked perfectly without flaws but i copied your idea and then recreated a solution that is more simplier for me to understand and use, instead of typing and repeating arrays, so i have included my solution as the answer to the question, but thanks alot i wouldn't have gotten the idea if you hadn't given me a clue. – pandglobal May 27 '20 at 12:16
  • You betcha, @pandglobal ... an upvote and setting my answer as the accepted one would be most helpful. – Mort 1305 May 27 '20 at 19:38
0

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', 'john@example.com'),
    ('Jane', 'Doke', 'jane@example.com'),
    ('John3', 'Doe3', 'john3@example.com'),
    ('Jane4', 'Doke4', 'jane4@example.com')
     ";


$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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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