Built one MySQL INSERT using two different arrays using for each

Question

I hope someone is able to help me with the problem I'm facing for hours already. I'm trying to built a MySQL INSERT from two arrays. One array ($bms) holds 6 values and the second one holds 3 values (I want to use the array with 3 values inside the INSERT query where I'm using the placeholder group right now). Each value of the (group array) needs to be used 2 times. I hope you understand with my example below:

The 2 arrays – always include 6 respectively 3 values:

Array ( [0] => 8 [1] => 3 [2] => 4 [3] => 121 [4] => 13 [5] => 154 ) // $bms

Array ( [0] => 266 [1] => 267 [2] => 268 ) // group

The query with foreach loops:

$query = "INSERT INTO userlinks (linkpool_id, group, userid) VALUES ";
foreach($bms as $bm) {
    $query .= "('".$bm."', group, '".$userid."'),";
}
echo $query;

The output so far:

INSERT INTO userlinks (linkpool_id, group, userid) VALUES ('8', group, '19'),('3', group, '19'),('4', group, '19'),('121', group, '19'),('13', group, '19'),('154', group, '19')

WHAT I'M TRYING TO ACHIEVE:

INSERT INTO userlinks (linkpool_id, group, userid) VALUES ('8', '266', '19'),('3', '266', '19'),('4', '267', '19'),('121', '267', '19'),('13', '268', '19'),('154', '268', '19')

I really appreciate your help and invested time – awesome community!

The two first values of $bms needs to be associated with the first value of $group, the 3rd and 4th of $bms with the second of $group etc ... So each value of group will be used twice. — Treast
– Treast, Commented Aug 6, 2017 at 13:28
@Treast Ah, now it's clear. Perhaps I need some more coffee. :-) — Rajdeep Paul
– Rajdeep Paul, Commented Aug 6, 2017 at 13:32

Treast · Accepted Answer · 2017-08-06 13:37:29Z

4

You can use a for loop instead :

$query = "INSERT INTO userlinks (linkpool_id, group, userid) VALUES ";

for($i = 0; $i < count($bms); $i++) {
  $j = (int) $i/2; //[0,1] will return 0, [2,3] return 1 etc... 
  $query .= "('".$bms[$i]."', '".$groups[$j]."', '".$userid."'),";
}

rtrim($query, ",");
echo $query;

Edit: Thanks @RajdeepPaul for more precision.

edited Aug 6, 2017 at 13:37

answered Aug 6, 2017 at 13:26

Treast

1,1151 gold badge9 silver badges22 bronze badges

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

3 Comments

Rajdeep Paul Over a year ago

group is a MySQL reserved word, encapsulate the table and column names using backticks. Also, remove the trailing , from the final query, do echo rtrim($query, ",");

Fab Over a year ago

@Treast, ok so I've tried the code you suggested above, but the output looks like this ->

INSERT INTO userlinks (linkpool_id, group, userid) VALUES ('8', '', '19'),('3', '', '19'),('4', '', '19'),('121', '', '19'),('13', '', '19'),('154', '', '19')

How do I assign the value of the groups array to it? I don't get it, sorry.

Fab Over a year ago

My mistake, sorry! Yes the code you provided above works just fine! Thanks a lot!

Collectives™ on Stack Overflow

Built one MySQL INSERT using two different arrays using for each

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related