好的,我有这个最近的访问表,下面的代码我用来将记录输入到用户表中
if($user->is_logged_in() ){
$postid = $row['postid'];
$uid = $_SESSION['memberid'];
$stmt = "SELECT * FROM recent WHERE postid = :postid AND memberid = :memberid";
$stmt = $db->prepare($stmt);
$stmt->bindParam(':postid', $postid, PDO::PARAM_STR);
$stmt->bindParam(':memberid', $uid, PDO::PARAM_STR);
$stmt->execute();
$recentCount = $stmt->rowCount();
if(!$recentCount)){
$stmt = $db->prepare('INSERT INTO recent (postid,memberid) VALUES ( :postid,:memberid)');
$stmt->execute(array(
':postid' => $postid,
':memberid' => $uid
));
}
}
但问题是我希望限制记录,因为在每个用户中,数据库中应该只有50条记录。假设用户访问了一个新的主题,那么如果用户的最近表中已经有50条记录,那么数字50被删除,49条记录变为50。我希望你明白我的意思?
我的意思是每个用户的记录不应该超过50条。
根据问题和评论,我认为你可以这样做(你没有提到日期字段的名称,但在这个例子中,我假设它被称为createddate):
if($user->is_logged_in() ){
$postid = $row['postid'];
$uid = $_SESSION['memberid'];
$stmt = "SELECT COUNT(*) FROM recent WHERE postid = :postid AND memberid = :memberid"; //let mysql count the rows
$stmt = $db->prepare($stmt);
$stmt->bindParam(':postid', $postid, PDO::PARAM_STR);
$stmt->bindParam(':memberid', $uid, PDO::PARAM_STR);
$stmt->execute();
$recentCount = $stmt->fetchColumn(); //fetch first column in first row, this will be the count result
if($recentCount >= 50)
{
$stmt2 = $db->prepare('DELETE FROM recent WHERE createddate = (select min(createddate) where memberid = :memberid)');
$stmt2->bindParam(':memberid', $uid, PDO::PARAM_STR);
$stmt2->execute();
}
$stmt = $db->prepare('INSERT INTO recent (postid,memberid) VALUES ( :postid,:memberid)');
$stmt->execute(array(
':postid' => $postid,
':memberid' => $uid
));
如果PDO语法错误,我很抱歉,我已经有一段时间没有使用它了。我相信你自己也能做到。但重要的是PHP"if"语句和"delete"SQL的结构。