How can i play with these role in my database?

[mahenda]

How can i play with these role in my database table as you can see here

//I have a database table

CREATE TABLE users ( id int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, urole varchar(10) );

in the role column, I have 2 roles first role is only used once but the sound one can be assigned to more than one user, now I want to check my table if the first role is already registered, then we can't register user with that role, also the second role can exist two or more times

//check role, a variable user role has been defined as 
$urole = $_POST['urole'];
$check = $connect -> prepare('SELECT * FROM users WHERE urole = ?');
$check -> execute([$urole]); $checkfetch = $check -> fetch();

//I'm stacking here, I want to put $checkfetch['urole'] in rowCount() to be counted but I think this is not a correct way of using rowCount() 

if(($checkfetch['urole'] == 'MainAdmin') && ($checkfetch ->rowCount() == 1)) { 
   echo 'This role can be used by only once!';
} 
//another role 
if(($checkfetch['urole'] == 'NormalAdmin') && ($checkfetch ->rowCount() < 4)) { 
   echo 'This role can be used 4 times only!';
}

 

[Barand]

Don't retrieve all the columns in all the records just to count them. Get a record count.

$urole = 'MainAdmin';
$res = $pdo->prepare("SELECT count(*) as total
                      FROM testuser
                      WHERE role = ?;
                     ");
$res->execute([$urole]);
$total = $res->fetchColumn();

 

[Barand]

Alternatively, get all the totals in one go

$res = $pdo->query("SELECT role
                         , count(*) as total
                      FROM testuser
                      GROUP BY role;
                     ");
$totals = array_column($res->fetchAll(), 'total', 'role');

echo $totals['MainAdmin'];
echo $totals['normaAdmin'];