SELECT teamid FROM teams WHERE active = '1' AND nationalteam = '2'
SELECT trainingunit FROM teamtrainings WHERE team_id = 'x' AND year = 'x' AND month = 'x' AND day = 'x'
SELECT ability_id, effect FROM trainingeffects WHERE trainingunit_id = 'x'
SELECT players.playerid, players.talent, players.fitness, players.freshness, players.training_intensity FROM playerteams INNER JOIN players ON playerteams.player_id = players.playerid WHERE playerteams.team_id = 'x' AND (playerteams.begin IS NOT Null AND playerteams.end IS Null)
SELECT playerabilities.player_id, playerabilities.ability_id, playerabilities.value, playerabilities.training_level FROM playerabilities INNER JOIN playerteams ON playerabilities.player_id = playerteams.player_id WHERE playerteams.team_id = 'x' AND (playerteams.begin IS NOT Null AND playerteams.end IS Null)
SELECT t.teamid, tt.trainingunit, te.ability_id, te.effect, p.playerid, TIMESTAMPDIFF(YEAR, p.birthdate, CURDATE()) AS age, p.talent, p.fitness, p.freshness, p.training_intensity, pa.ability_id, pa.value, pa.training_level
FROM teams t
LEFT JOIN teamtrainings tt ON t.teamid = tt.team_id
LEFT JOIN trainingeffects te ON tt.trainingunit = te.trainingunit_id
LEFT JOIN playerteams pt ON t.teamid = pt.team_id
LEFT JOIN players p ON pt.player_id = p.playerid
LEFT JOIN playerabilities pa ON p.playerid = pa.player_id
WHERE t.active = '1'
AND t.nationalteam = '2'
AND tt.year = '2018'
AND tt.month = '07'
AND tt.day = '01'
AND (pt.begin IS NOT Null AND pt.end IS Null)
| update tab2 with for | 145 | for($i=1; $i<=1024; $i++){ $sql = "UPDATE tab2 SET val=".($i+1)." WHERE id=$i"; $result = $db->exec($sql); } $t->check($result, $result !== false); |
[7550.2 ms] 1 |
Ok |
| update with prepared statements | 152 | $sql = "UPDATE tab2 SET val=:val WHERE id=:id"; $stmt = $db->prepare($sql); for($i=1; $i<=1024; $i++){ $param = array( "val" => $i+2, "id" => $i, ); $result = $stmt->execute($param); } $t->check($result, $result !== false); |
[9115.1 ms] true |
Ok |
| create update-Table | 164 | $sql = "CREATE TABLE `updatetab2` ( `id` int(11) NOT NULL, `val` DOUBLE NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test'"; $result = $db->execSQL($sql); $t->check($result, $result !== false); |
[3.9 ms] 0 |
Ok |
| insert update data from array | 173 | $rowCount = 1024; $data = array(); for($i=1; $i<=$rowCount; $i++){ $data[] = array( $i, (float)($i+10.5) ); } $assign = array( 0 => 'id', 1 => 'val' ); $result = $db->insertArray('updatetab2',$assign,$data); $t->checkEqual($result, $rowCount); |
[24.1 ms] 1024 |
Ok |
| update tab2 from update-Table | 189 | $sql = "UPDATE tab2, updatetab2 SET tab2.val = updatetab2.val WHERE tab2.id = updatetab2.id"; $result = $db->execSQL($sql); $t->check($result, $result !== false); |
[14.2 ms] 1024 |
Ok |
| show first row | 196 | $sql = "SELECT id, val FROM tab2 WHERE id=1"; $row = $db->getSingleValue($sql); $expected = array('id' => '1', 'val' => '11.5'); $t->checkEqual($row, $expected); |
[0.7 ms] array ( 'id' => '1', 'val' => '11.5', ) |
Ok |
INSERT INTO `updatetab2` ( `id`,`val` ) VALUES (1,'11.5'),(2,'12.5') ...
mysqli_query($connection,"CREATE TABLE updateTable (id int(10) NOT NULL, playerid int(10) DEFAULT NULL, abilityid int(10) DEFAULT NULL, value int(10) DEFAULT NULL, training_level int(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8");
mysqli_query($connection,"ALTER TABLE updateTable ADD PRIMARY KEY (id)");
mysqli_query($connection,"ALTER TABLE updateTable MODIFY id int(10) NOT NULL AUTO_INCREMENT");
if(is_array($arUpdatePlayerabilities)):
$i = 0;
foreach($arUpdatePlayerabilities AS $playerid => $value):
foreach($value AS $abilityid => $key):
if($i == 0):
$string .= "(".$playerid.", ".$abilityid.", ".$key["value"].", ".$key["training_level"].")";
else:
$string .= ", (".$playerid.", ".$abilityid.", ".$key["value"].", ".$key["training_level"].")";
endif;
$i++;
endforeach;
endforeach;
endif;
mysqli_query($connection,"INSERT INTO updateTable (playerid, abilityid, value, training_level) VALUES ".$string);
mysqli_query($connection,"UPDATE playerabilities_test, updateTable SET playerabilities_test.value = updateTable.value, playerabilities_test.training_level = updateTable.training_level WHERE playerabilities_test.player_id = updateTable.playerid AND playerabilities_test.ability_id = updateTable.abilityid");
mysqli_query($connection,"DROP TABLE updateTable");
Kommentar