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'));
Add a comment
|
2 Answers
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.
-
1Thanks 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. Commented May 27, 2020 at 12:16
-
You betcha, @pandglobal ... an upvote and setting my answer as the accepted one would be most helpful. Commented May 27, 2020 at 19:38
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.