我是这个网站的新手,这是我的第一篇文章。
我有MySQL PDO准备的查询:
try {
$sql = "INSERT INTO `site_users_wishlist` (`user_id`, `uin`, `active`)
VALUES (?, ?, 'Y')";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($userId,$uin));
}
catch (PDOException $e) {
if ($e->errorInfo[1] == 1062) {
echo "__item_exists_in_wishlist__";
exit;
}
else {
echo "Error adding item to wishlist!<br/>".$e;
exit;
}
}
echo "__item_added_to_wishlist__";
exit;
'活动'列的值 y / n // B ( y <<strong> y <</strong> es, n o, b 应该)。
当用户将项目添加到他的愿望清单中时,它会使用'活动'=' y ',将项目插入数据库中当用户从愿望清单中删除项目时,它将变为'活动'=' n '。
此时问题继续:
如果用户尝试多次添加项目,我会捕获 1062错误重复输入并向用户显示消息。
如果用户以前删除了该项目,并且想将其重新添加到他/她的愿望清单中,我想显示的消息,它已成功地将其添加到愿望清单中,而不是在数据库中存在。
我需要一些干净的代码,例如
$sql = "INSERT INTO `site_users_wishlist` (`user_id`, `uin`, `active`)
VALUES (?, ?, 'Y')
ON DUPLICATE KEY UPDATE IF(`active`<>'Y', `active`='Y', TRIGGER DUPLICATE ERROR 1062";
预先感谢
edit1:
表创建代码:
CREATE TABLE `site_users_wishlist` (
`user_id` int(11) NOT NULL COMMENT 'user id',
`uin` int(5) NOT NULL COMMENT 'item id',
`date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'date of addition by user',
`added_price` int(10) DEFAULT NULL COMMENT 'price when added',
`updated_price` int(10) DEFAULT NULL COMMENT 'current price',
`active` tinytext NOT NULL COMMENT 'N - not, Y - yes, B - bought by user',
PRIMARY KEY (`user_id`,`uin`),
KEY `user_id` (`user_id`),
CONSTRAINT `userId` FOREIGN KEY (`user_id`) REFERENCES `site_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
好吧,所以,我没有找到优雅的单个查询解决方案,但这是使用 3查询的解决方案,就像我的特定需求一样,连接3次没有问题。
try {
$sql = "SELECT * FROM `site_users_wishlist` WHERE `user_id` = ? AND `uin` = ? AND `active` <> 'Y'";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($userId, $uin));
$exists = $stmt->rowCount();
if ($exists == 1) {
try {
$sql2 = "UPDATE `site_users_wishlist` SET `active` = 'Y' WHERE `user_id` = ? AND `uin` = ?";
$stmt2 = $pdo->prepare($sql2);
$stmt2->execute(array($userId, $uin));
}
catch (PDOException $e) {
echo "Error adding item to wishlist!<br/>".$e;
exit;
}
}
else {
try {
$sql2 = "INSERT INTO `site_users_wishlist` (`user_id`, `uin`, `uinsql`, `added_price`, `last_updated_price`, `active`) VALUES (?, ?, ?, ?, ?, 'Y')";
$stmt2 = $pdo->prepare($sql2);
$stmt2->execute(array($userId, $uin, $uinsql, $price, $price));
}
catch (PDOException $e) {
if ($e->errorInfo[1] == 1062) { // 1062 error code for duplicate entry
echo "__item_exists_in_wishlist__";
exit;
}
else {
echo "Error adding item to wishlist!<br/>".$e;
exit;
}
}
}
}
catch (PDOException $e) {
echo "Error adding item to wishlist!<br/>".$e;
exit;
}
echo "__item_added_to_wishlist__";
exit;
也许有一天对别人有帮助;)