Mysql循环遍历行以检查行，直到找到计数为零的行

Question

I have looked thru many solutions that are similar but can not find one that meets my requirements.

I want to limit my users to creating more than two shortURL per url in a 24 hour period

people are abusing this and creating 10+ urls and its clogging up my database. so i want to limit them to 2 per url and leave a message if they have exceeded that

right now i have this check to see if how many they have done per url

    $sql = "SELECT * FROM ShortUrls WHERE user_id='$user' AND vidID='$urlMe' AND time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))";
    $result = $conn->query($sql);

   echo "user created this many urls: " .$result->num_rows;
   if ($result->num_rows > 2) {
         // keep checking till find one less that 2
     }

Loop thru the database of urls and check if they used that same url more than twice in 24 hours. if they have not used that url more than twice in 24 hours they can create a new Shortened URL with that url.

EDIT the solution i used:

$sql2 = "SELECT tube.vidID FROM tube LEFT JOIN ( SELECT COUNT(*) c, vidID FROM ShortUrls WHERE user_id='$user' AND time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY)) GROUP BY vidID ) urls ON urls.vidID = tube.vidID WHERE urls.c < 2 LIMIT 1;";
$result = $conn->query($sql2);

if ($row = $result->fetch_assoc()) { 
  echo "You can add a URL to this video: " . htmlspecialchars($row['vidID']); 
} else { 
  echo "All videos used up!"; 
}

Hope this can be useful to others

Answer 1

Do you want to see which vidIDs have less than two rows created in the last 24 hours by the same user_id?

SELECT
    COUNT(*) c, vidID
FROM
    ShortUrls
WHERE
    user_id = ?
    AND time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
GROUP BY
    vidID
HAVING
    c < 2

But if you know the vidID in advance, and you're only concerned with that one vidID:

SELECT
    COUNT(*)
FROM
    ShortUrls
WHERE
    user_id = ?
    AND vidID = ?
    AND time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))

If you want to stop them if they've created more than two for any vidID, try this:

SELECT
    COUNT(*) c, vidID
FROM
    ShortUrls
WHERE
     user_id = ?
     AND time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
GROUP BY
    vidID
HAVING
    c > 2

If the query returns anything, the user has created more than 2 for a single vidID in the last 24 hours.

If you want to get fancy:

SELECT
    COUNT(*)
FROM (
    SELECT
        COUNT(*) c, vidID
    FROM
        ShortUrls
    WHERE
        user_id = ?
        AND time > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
    GROUP BY
        vidID
    HAVING
        c > 2
) i