how to make a limit 20 users maximum to choose one package

user_15428500 0
Viewed 35

This is my code that is responsible for assigning every package name chosen to that specific userName my idea is to add another query such as query = select userName from users where userName = 'package_name'; and after the exestuation of this query if the rows affected are =>20 user shoudn't be able to choose this package otherwise userName will be assigned to that package name

<?php
include("session.php");
include("connection.php");

$Pname= filter_input(INPUT_GET,'Pname');
$user_select = $_SESSION['login_user'];

$query='update users set package_name=? where userName=?';
  
$stmt=mysqli_stmt_init($conn);

mysqli_stmt_prepare($stmt,$query) or exit('Query Error.'. mysqli_stmt_errno($stmt));

@mysqli_stmt_bind_param($stmt,'ss',$Pname,$user_select) or exit('Bind Param Error.'); 

mysqli_stmt_execute($stmt) or exit('Query Execution failed.'. mysqli_stmt_errno($stmt));


if (mysqli_stmt_affected_rows($stmt)>0) echo "package chose successfully.";

mysqli_stmt_close($stmt);


mysqli_close($conn);
?>
mysqli mysql html php
1 Answers

Use triggers:

CREATE TRIGGER tr_bi_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN 
    IF 20 <= (SELECT COUNT(*)
              FROM users
              WHERE package_name = NEW.package_name
                AND userName <> NEW.userName) THEN
        SIGNAL SQLSTATE = '45000'
            SET MESSAGE_TEXT = 'The limit for this package reached.';
    END IF;
END

and the same trigger for BEFORE UPDATE.

user_101387340
Related