我有2个表将需要4元组更新或插入,例如表将有每个球员的游戏和职业生涯(2)的条目,所以这是4个元组。在我的头顶上,我只能想到做一个选择为player1和游戏,如果不存在插入否则更新,这为player1和职业生涯,.......这似乎有很多DB查询,有没有更好的方法来处理这个问题?在PHP PDO MYSQL现有代码中:
CREATE TABLE `standings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`league_id` int(11) NOT NULL,
`season_id` int(11) NOT NULL,
`team_id` int(11) NOT NULL,
`statistic_type_id` int(11) NOT NULL,
`wlt` varchar(30) NOT NULL);
$sth = $dbh->prepare('Select * from standings
where league_id=? and season_id=? and team_id=? and statistic_type_id=$? ');
$data = $sth->execute(array($l, $s, $t, $st));
$data = $sth->fetchAll();
if ($sth->rowCount() == 0) {
$wlt = $w . '," . $lo . ',' . $di;
$sth = $dbh->prepare('INSERT INTO standings ( id, league_id, season_id,
team_id, statistic_type_id, wlt)
VALUES( 0, ?, ?, ?, ?, ?);
$data = $sth->execute(array( $l, $s, $t, $st, $wlt));
} else {
foreach ($data as $row) {
$wlt = explode(",", $row['wlt']);
$wlt[0] = $wlt[0] + $w;
$wlt[1] = $wlt[1] + $lo;
$wlt[2] = $wlt[2] + $di;
$nwlt = implode(",", $wlt);
$sth = $dbh->prepare('UPDATE standings SET wlt=?
where league_id=? and season_id=? and team_id=? and statistic_type_id=$? ');
$data = $sth->execute(array($nwlt, $l, $s, $t, $st));
}
}
发布于 2011-02-02 00:21:09
可以使用replace into http://dev.mysql.com/doc/refman/5.0/en/replace.html
https://stackoverflow.com/questions/4865057
复制相似问题