limit entry to db while added new records

Question

ok i have this recent visits table and the following code i use to enter records into the table user wise

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                 
         ));
     }  
}

but the thing is i wish to limit records, as in per user only 50 records should be in db. supposing user visits a new topic then if there already 50 records in recent table for the user then the number 50 gets deleted and 49 record becomes 50. i hope you get my point?

its just that records per user should not exceed above 50 is what i mean.

so check that $recentCount is <50, and if not, delete the oldest row. Then carry on and do the insert — ADyson, Jul 26 '16 at 09:17
yes what would the delete query look like ? to delete the oldest row — lalthung, Jul 26 '16 at 09:19
I'm assuming postid is an autoincrement value, meaning that the lowest value would be the oldest. Therefore probably something like `DELETE FROM recent where postid = (SELECT min(postid) from recent where memberid = :memberid)` If postid is not automatically generated, then maybe you've got a date field you can use (created date or something)? — ADyson, Jul 26 '16 at 09:27
yes i have date and no postid iin recent table it not autoincrement there is this id which is autoincrement postid is from the posts table but yes i have field as date which is timestamp — lalthung, Jul 26 '16 at 09:40
Instead of limiting the amount of records in the database, simply insert and limit the amount while you pull the data out. Using event scheduler, you can perform cleanup every several hours automatically and prune records that don't have to be there. Limiting the amount of records at insertion is not how databases are meant to be used. You can also get false positives that way and end up with more/less records at any point. — Mjh, Jul 26 '16 at 09:55

ADyson · Answer 1 · 2016-07-26T10:32:50.967

0

Based on the question and comments, I think you can do it like this (you didn't mention the name of your date field but for this example I'll assume it's called 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                 
     ));

Apologies if the PDO syntax is wrong, I haven't used it in a while. I'm sure you can make that right yourself. But the important thing is the structure of the PHP "if" statement and the "delete" SQL.

edited Jul 26 '16 at 10:32

answered Jul 26 '16 at 09:46

ADyson

57,178
14
51
63

well, did you change it to use the correct name of your date field? Did you check that date field has the right data in it? I did notice that your insert doesn't contain anything that would set the contents of that field, so unless the DBMS is setting it automatically to a default value then you need to make sure it has the current date/time in it – ADyson Jul 26 '16 at 10:08
`if($recentCount >= 2){ $stmt = "DELETE FROM recent WHERE date = (SELECT MIN(date) WHERE memberid = :memberid)"; $stmt = $db->prepare($stmt);` $stmt->bindParam(':memberid', $uid, PDO::PARAM_STR); $stmt->execute(); }elseif(!$recentCount){ $stmt = $db->prepare('INSERT INTO recent (postid,memberid) VALUES ( :postid,:memberid)'); $stmt->execute(array( ':postid' => $postid, ':memberid' => $uid )); } – lalthung Jul 26 '16 at 10:10
yes i did that but it keeps adding more to recent table this is how dates are stored 2016-07-26 15:32:24 – lalthung Jul 26 '16 at 10:10
is it in a "date" field or "varchar"? it should be date. also is "$recentCount" producing the correct value? – ADyson Jul 26 '16 at 10:18
its in current timestamp let me change to date and see brb – lalthung Jul 26 '16 at 10:19
but inserted rocerds now has no value to date its all 00.00.00:00 – lalthung Jul 26 '16 at 10:21
timestamp will initialise the values automatically when the row is inserted. for date it won't, you would have to do it manually. tbh though I was more concerned to ensure you weren't storing the dates as text (because that would mess up the min comparison). timestamp type should be fine for this. – ADyson Jul 26 '16 at 10:25
http://php.net/manual/en/pdostatement.rowcount.php - this is could be more of an issue. the ->rowCount may not return the number of rows selected. Have edited the answer to count the rows more reliably – ADyson Jul 26 '16 at 10:29
i see but if i try fetchColumn i get error for somereason – lalthung Jul 26 '16 at 10:35

limit entry to db while added new records

1 Answers1