Multi insert issue
bzgithub opened this issue · 5 comments
Hi, i have just started using mysqly.php library. I have an issue when calling:
mysqly::multi_insert('facts_union', $insert);
Below I am sending var_dump for $insert array and also var_dump for $params in your public static function exec() function. As you can see the column binding is getting mixed up. Only for first insert statement this order seems ok, but for the second and third the binding starts with "r:10" instead of "r:00". And the next array member starts with "r:20" etc. Am I doing something wrong? Is there a limit to columns number or something? I am not very good in php :)
`C:\inetpub\wwwroot...\test.php:224:
array (size=3)
0 =>
array (size=22)
'GENDER' => string 'male' (length=4)
'ID_P_F' => string '19' (length=2)
'YEAR_F' => string '2004' (length=4)
'TMW_F' => string '74' (length=2)
'TML_F' => string '6' (length=1)
'TBW_F' => string '21' (length=2)
'TBL_F' => string '5' (length=1)
'MF_F' => string '74' (length=2)
'ACES_F' => string '557' (length=3)
'DF_F' => string '152' (length=3)
'SRV1_F' => string '62' (length=2)
'WON1_F' => string '78' (length=2)
'WON2_F' => string '58' (length=2)
'SGW_F' => string '92' (length=2)
'BPS_F' => string '73' (length=2)
'PWR1S_F' => string '35' (length=2)
'PWR2S_F' => string '52' (length=2)
'BPW_F' => string '41' (length=2)
'RGW_F' => string '30' (length=2)
'PRIZE_F' => string '6357547' (length=7)
'JobID' => int -1
'JobTimestamp' => string '2023-01-12 12:24:09' (length=19)
1 =>
array (size=22)
'GENDER' => string 'male' (length=4)
'ID_P_F' => string '89' (length=2)
'YEAR_F' => string '2004' (length=4)
'TMW_F' => string '74' (length=2)
'TML_F' => string '16' (length=2)
'TBW_F' => string '36' (length=2)
'TBL_F' => string '11' (length=2)
'MF_F' => string '81' (length=2)
'ACES_F' => string '1017' (length=4)
'DF_F' => string '186' (length=3)
'SRV1_F' => string '63' (length=2)
'WON1_F' => string '81' (length=2)
'WON2_F' => string '56' (length=2)
'SGW_F' => string '91' (length=2)
'BPS_F' => string '69' (length=2)
'PWR1S_F' => string '29' (length=2)
'PWR2S_F' => string '50' (length=2)
'BPW_F' => string '40' (length=2)
'RGW_F' => string '22' (length=2)
'PRIZE_F' => string '2604590' (length=7)
'JobID' => int -1
'JobTimestamp' => string '2023-01-12 12:24:09' (length=19)
2 =>
array (size=22)
'GENDER' => string 'male' (length=4)
'ID_P_F' => string '7' (length=1)
'YEAR_F' => string '2004' (length=4)
'TMW_F' => string '68' (length=2)
'TML_F' => string '18' (length=2)
'TBW_F' => string '18' (length=2)
'TBL_F' => string '9' (length=1)
'MF_F' => string '84' (length=2)
'ACES_F' => string '523' (length=3)
'DF_F' => string '303' (length=3)
'SRV1_F' => string '54' (length=2)
'WON1_F' => string '75' (length=2)
'WON2_F' => string '53' (length=2)
'SGW_F' => string '82' (length=2)
'BPS_F' => string '64' (length=2)
'PWR1S_F' => string '34' (length=2)
'PWR2S_F' => string '53' (length=2)
'BPW_F' => string '43' (length=2)
'RGW_F' => string '32' (length=2)
'PRIZE_F' => string '2766051' (length=7)
'JobID' => int -1
'JobTimestamp' => string '2023-01-12 12:24:09' (length=19)
C:\inetpub\wwwroot...\mysqly.php:103:
array (size=66)
':r00' => string 'male' (length=4)
':r01' => string '19' (length=2)
':r02' => string '2004' (length=4)
':r03' => string '74' (length=2)
':r04' => string '6' (length=1)
':r05' => string '21' (length=2)
':r06' => string '5' (length=1)
':r07' => string '74' (length=2)
':r08' => string '557' (length=3)
':r09' => string '152' (length=3)
':r010' => string '62' (length=2)
':r011' => string '78' (length=2)
':r012' => string '58' (length=2)
':r013' => string '92' (length=2)
':r014' => string '73' (length=2)
':r015' => string '35' (length=2)
':r016' => string '52' (length=2)
':r017' => string '41' (length=2)
':r018' => string '30' (length=2)
':r019' => string '6357547' (length=7)
':r020' => int -1
':r021' => string '2023-01-12 12:24:09' (length=19)
':r10' => string 'male' (length=4)
':r11' => string '89' (length=2)
':r12' => string '2004' (length=4)
':r13' => string '74' (length=2)
':r14' => string '16' (length=2)
':r15' => string '36' (length=2)
':r16' => string '11' (length=2)
':r17' => string '81' (length=2)
':r18' => string '1017' (length=4)
':r19' => string '186' (length=3)
':r110' => string '63' (length=2)
':r111' => string '81' (length=2)
':r112' => string '56' (length=2)
':r113' => string '91' (length=2)
':r114' => string '69' (length=2)
':r115' => string '29' (length=2)
':r116' => string '50' (length=2)
':r117' => string '40' (length=2)
':r118' => string '22' (length=2)
':r119' => string '2604590' (length=7)
':r120' => int -1
':r121' => string '2023-01-12 12:24:09' (length=19)
':r20' => string 'male' (length=4)
':r21' => string '7' (length=1)
':r22' => string '2004' (length=4)
':r23' => string '68' (length=2)
':r24' => string '18' (length=2)
':r25' => string '18' (length=2)
':r26' => string '9' (length=1)
':r27' => string '84' (length=2)
':r28' => string '523' (length=3)
':r29' => string '303' (length=3)
':r210' => string '54' (length=2)
':r211' => string '75' (length=2)
':r212' => string '53' (length=2)
':r213' => string '82' (length=2)
':r214' => string '64' (length=2)
':r215' => string '34' (length=2)
':r216' => string '53' (length=2)
':r217' => string '43' (length=2)
':r218' => string '32' (length=2)
':r219' => string '2766051' (length=7)
':r220' => int -1
':r221' => string '2023-01-12 12:24:09' (length=19)`
I am also posting var_dump($sql) that you have in public static function exec() function. As you can see param numbering is off.
INSERT INTO facts_union(GENDER,ID_P_F,YEAR_F,TMW_F,TML_F,TBW_F,TBL_F,MF_F,ACES_F,DF_F,SRV1_F,WON1_F,WON2_F,SGW_F,BPS_F,PWR1S_F,PWR2S_F,BPW_F,RGW_F,PRIZE_F,JobID,JobTimestamp)
VALUES(:r00,:r01,:r02,:r03,:r04,:r05,:r06,:r07,:r08,:r09,:r010,:r011,:r012,:r013,:r014,:r015,:r016,:r017,:r018,:r019,:r020,:r021),
(:r10,:r11,:r12,:r13,:r14,:r15,:r16,:r17,:r18,:r19,:r110,:r111,:r112,:r113,:r114,:r115,:r116,:r117,:r118,:r119,:r120,:r121),
(:r20,:r21,:r22,:r23,:r24,:r25,:r26,:r27,:r28,:r29,:r210,:r211,:r212,:r213,:r214,',:r215,:r216,:r217,:r218,:r219,:r220,:r221)
After debugging a little I found the issue within your multi_insert function. If more than 10 columns/keys were in the $rows then binding was not done correctly, since first row had the binding ":r00" for 1st column and the last column had binding ":r10". In the second row the 1st column again had the binding ":r10" which was duplicate for the first row.
I changed 2 lines in your mysqly.php script:
original: $values[] = '(' . implode(',', array_map(function($c) use($r) { return ":r{$r}{$c}"; }, range(0, count($row)-1))) . ')';
change: $values[] = '(' . implode(',', array_map(function($c) use($r) { return ":r{$r}_{$c}"; }, range(0, count($row)-1))) . ')'; //added underscore in return
original: $bind[":r{$r}{$c}"] = $v;
change: $bind[":r{$r}_{$c}"] = $v; //added underscore
Hey man!
Sorry for long reply, my country is being bombed systematically, so have hard time getting here.
I'll fix that shortly, thanks for your report.
You actually had detected a severe bug and an efficient fix for it. Thank you!
I've commited into master since it's critical:
440c4ab
Glad I could help :) Stay safe!