Limiting how many rows can be created by the same user

Is there a way to limit how many rows can be created by the same user? Let's say I want to limit a user to 20 entries and unless he/she deletes some of their old entries they can't submit a new entry. Is it possible to do by adding something to:

"INSERT INTO ajaxfavourites (user, favid, exptime) VALUES ('$user', '$favid', CURRENT_TIMESTAMP)"

Code (markup):

Or just tell me how it can be done.

 

You can either do a count, as Sarah suggests, or update a counter in the user-table by one for each entry, and check that. Either way, you'll need to pull a value from the database.

 

How do I update the counter?

Someone suggested on stackoverflow I can do this with triggers and referred me to: https://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html

Can anyone tell me if it's a viable solution?

 

Can you give me an example? I understand it should go something like:

SELECT count(*) FROM ajaxfavourites WHERE user='$user'

Code (markup):

What I don't know is where do I set or how do I run the "less than the max" code?

 

You could also use triggers in the database, yes - running either an internal count() or just use a trigger to update a given field in a table when a user inputs a new row - and if that total exceeds, just fail the query - however, this would involve some more coding, and depending on what database-engine you're using, you might have to create the information to the user (that the insert didn't work because s/he has exceeded the allowed entries count) manually.
I wouldn't recommend using triggers at this point, as you don't really have the knowledge to do so. Triggers can be tricky, and they're often an undocumented thing running on the database-layer, which isn't good if you ever need to hire another coder. Document such things.
As for how to update a counter, you could just do another update-query on the counter when adding. But as for the work, it's not that different doing a count(*) and having a count-column somewhere. You can easily also leverage the current count (if checked before the actual insert) to tell the user if s/he tries to inject more than one, for instance, whether or not that will be allowed.
Say a user have 17 entries currently, and wants to add 4 more. That won't be possible, given a limit of 20, and you could tell the user that - "sorry, with 4 entries, that will bring your total number of entries to 21, your limit is 20. Either remove one of the old ones before inserting, or remove one of the new ones to keep within limits", or something similar. Of course that means you should have an easy way to remove old entries, and so forth and so on.

 

Let me post the entire add / delete favorites file here. The issue I have right away is when I change SELECT * to SELECT count(*) the script stops working altogether. What do I edit to do this: 1. count 2. if count is >= 20 a $user can't add more favorites

By the way I don't need a warning message or anything like that. There will be a message on the "your favorites" page that will tell users about the limit.

// Include needed files

include 'mysql.php';

// Connect to MySQL

connectMySQL();

//****** SECURITY CHECK *********

session_start();
if(isset($_SESSION['userid'])){
$user = mysql_real_escape_string($_SESSION['userid']);

//*******************************

// Retrieves variables through AJAX

$favid = mysql_real_escape_string($_GET['favid']);
// $favid = mysql_real_escape_string($_GET['favid']);

// Firstly, check if article is favourite or not

$query = mysql_query("SELECT * FROM ajaxfavourites WHERE user='$user' AND favid='$favid'");
$matches = mysql_num_rows($query);

// If it is not favourited, add as favourite

if($matches == '0'){
mysql_query("INSERT INTO ajaxfavourites (user, favid, exptime) VALUES ('$user', '$favid', CURRENT_TIMESTAMP)");

echo "";
}


// Instead, if it is favourited, then remove from favourites

if($matches != '0'){
mysql_query("DELETE FROM ajaxfavourites WHERE user='$user' AND favid='$favid'");

echo "";
}

} else {

// Someone tries to directly access the file!

echo "Invalid session!";

}

Code (markup):

 

You're using mysql_, which you really shouldn't, but you can amend your query like this (should work):

$query = mysql_query("SELECT *,COUNT(*) AS count FROM ajaxfavourites WHERE user='$user' AND favid='$favid'");
$result = mysql_fetch_row($query);
$count = $result['count']; // or something, I haven't used mysql_ for forever, so I'm a bit rusty when it comes to mysql_ coding.

Code (markup):

And then you can access count as you would any other result from the query.
Then you can do a

if ($matches == 0 && $count < 20) {
//do stuff

Code (markup):

 

Oh, shit - I just saw that your query won't work for the purpose you're trying to use it for - you're checking both userID and favID - which will of course always return 1 or 0 (since it has to match against the favID) - this means you'll have to do a separate query for the count. "SELECT COUNT(*) FROM table WHERE id = $userid". Then use that for the match.

 

I got it.

Added

$query = mysql_query("SELECT * FROM ajaxfavourites WHERE user='$user'");
$userows = mysql_num_rows($query);

Code (markup):

then

if($matches == 0 && $userows < 20)

Code (markup):

 

Yup, that'll work.