Using Aggregate String Functions in MySQL and PHP

Question

I am currently having an issue where I have an enum named 'Privilege' where 0 = normal and 1 = privileged. These are assigned to jobs e.g. worker has normal privilege but CEO has admin privileges. An issue I have run into is where a user might have two jobs, where one is normal privileged and the other is admin privileged. I have written a query, in order to start sessions depending on the privilege,however when I use group by StaffID, I'm unsure on how to group by the highest privilege (1 being the case).

E.g.

else if ($pwdCheck == True){
    session_start();
    $query = mysqli_query($conn, 'SELECT staff.StaffID, role.Privilege FROM jobs
         INNER JOIN staff ON staff.StaffID = jobs.StaffID
         INNER JOIN role ON role.RoleID = jobs.RoleID GROUP BY StaffID
    ');

    $gettier = mysqli_fetch_assoc($query);
    if($gettier["Privilege"] === 'normal'){
        $_SESSION['userID'] = $row['LoginID'];
        header("Location: staffindex.php?login=USERsuccess");
    }
    elseif($gettier["Privilege"] === 'privileged'){
        $_SESSION['AdminID'] = $row['LoginID'];
        header("Location: staffindex.php?login=ADMINsuccess");
    }
    exit();
}

This is where in all cases, wen I enter a privileged person, I am still redirected with the USERsuccess header. I assume I will need to use an aggregate string function (after some research) but I don't really understand how to apply it.

Please view the image to see query in action:
With Group By: https://i.stack.imgur.com/XIZ8L.jpg
Without Group By: https://i.stack.imgur.com/gOJxx.jpg

Any help is appreciated.

Possible duplicate of [How does GROUP BY work?](https://stackoverflow.com/questions/7434657/how-does-group-by-work) — Masoud Keshavarz, May 22 '19 at 06:14

Nick · Accepted Answer · 2019-05-24T12:57:37.173

1

Unfortunately MySQL doesn't sort enum values numerically, but instead by their string representation, otherwise you could just use MAX directly on the values. But you can sort by CASTing the values as integers e.g.

SELECT staff.StaffID, MAX(CAST(role.Privilege AS UNSIGNED)) AS Privilege FROM jobs
INNER JOIN staff ON staff.StaffID = jobs.StaffID
INNER JOIN role ON role.RoleID = jobs.RoleID GROUP BY StaffID

Assuming your ENUM has values ('normal', 'privileged'), this will return a value of 1 for a user with only normal privilege and 2 for a user with either only privileged privilege or both normal and privileged. You can then change the check in your PHP to:

if($gettier["Privilege"] == 1){
    $_SESSION['userID'] = $row['LoginID'];
    header("Location: staffindex.php?login=USERsuccess");
}
elseif($gettier["Privilege"] == 2){
    $_SESSION['AdminID'] = $row['LoginID'];
    header("Location: staffindex.php?login=ADMINsuccess");
}

Note you can't use === in the comparison with $gettier["Privilege"] because the values returned from the query will be strings, not integers.

edited May 24 '19 at 12:57

answered May 22 '19 at 06:24

Nick

138,499
22
57
95

This is super, but this returns an error where Privilege is an undefined index? – GoGoAllen May 22 '19 at 06:30
@GoGoAllen sorry, forgot to give the value an alias. see my edit – Nick May 22 '19 at 06:32
Hmm, this still doesn't seem to work - doing this creates a blank page with no redirect. I added ((int)$gettier["Privilege"]) to both statements, however it still redirects to USERsuccess. – GoGoAllen May 22 '19 at 06:41
@GoGoAllen the db code works fine - see this demo https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=39ecc51e702091e2a91b2ebbd4319f57. PHP is returning values as strings which is why the `===` fails, but casting to int should fix that. what do you get if you `echo $gettier["Privilege"];`? – Nick May 22 '19 at 07:11
The query seems to be all goods - I ran the if statements through with a error_log(print_r($gettier,true)); at the end and it returned: "Array\n(\n [StaffID] => 1\n [Privilege] => 1\n)\n, referer: {localhost location}" – GoGoAllen May 22 '19 at 07:19
Is that the expected privilege level for that staff member? – Nick May 22 '19 at 07:21
Not sure, that was all that was returned in error.log – GoGoAllen May 22 '19 at 07:25
You will need a `WHERE` clause on your query to only select the privilege for a specific staff member – Nick May 22 '19 at 07:29
'SELECT staff.StaffID, MAX(CAST(role.Privilege AS UNSIGNED)) AS Privilege FROM jobs INNER JOIN staff ON staff.StaffID = jobs.StaffID INNER JOIN role ON role.RoleID = jobs.RoleID WHERE staff.StaffID = 4 GROUP BY StaffID' - Upon entering this and entering the login details of this admin user, the login process still leaves a blank page – GoGoAllen May 22 '19 at 07:38
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193752/discussion-between-nick-and-gogoallen). – Nick May 22 '19 at 07:40
Have you still got the log on $gettier? What does that say? – Nick May 22 '19 at 07:50
@GoGoAllen did you get anywhere with this? – Nick May 24 '19 at 12:56
Hey, the query was good! I realised that since the page I was redirecting too had an if statement saying that if the user did NOT have USERID it would deny access to the page. Upon changing this, everything worked as expected. Thanks so much for your help nick :) – GoGoAllen May 25 '19 at 04:22
@GoGoAllen that's great news! That sort of thing can be so hard to debug... – Nick May 25 '19 at 04:23

Using Aggregate String Functions in MySQL and PHP

1 Answers1