I have users assigned to specific Departments. When they view the same page (/manage) I want them to get different tickets to show up based on the viewing user's department.
To try this, I set a variable to get the viewing user's ID (corresponds to the database), and then another to pass that value into the parameter for a new SQL function I made named get_department_by_user:
static function get_department_by_user($userid){
$dp = SupportCenterDB::table_department();
$dpu = SupportCenterDB::table_department_user();
$d=OW::getDbo();
return $d->query("
SELECT {$dpu}.department_id FROM {$dpu}
JOIN {$dp} ON {$dpu}.department_id = {$dp}.id
WHERE user_id ='".$d->escapeString($userid)."'
;");
}
And here's the variable from the script:
$id = SupportCenterDB::get_department_by_user(OW::getUser()->getId());
...
$ticket_q = SupportCenterDB::ticket_list_manage($id);
When I check the database queries for loading the page, the first SQL function works as it should. It is grabbing the department type to use based on the user's ID (when users are assigned to departments, they are assigned by their ID to the specific department_id).
The step I'm having trouble with is the next SQL function. I want to get the data from the ticket table, where the ticket's department_id = the department Id set by the earlier SQL function.
static function ticket_list_manage(){
$table_ticket = SupportCenterDB::table_ticket();
$d=OW::getDbo();
$dp = SupportCenterDB::table_department();
return $d->queryForList("SELECT {$table_ticket}.id, category_id, subject,
requested_deletion, {$dp}.text, department_id, updated, status FROM {$table_ticket}
JOIN {$dp} ON {$dp}.id = {$table_ticket}.department_id
WHERE {$table_ticket}.department_id='{$id}'
ORDER BY updated DESC;");
}
Right now, this returns nothing. When I change it up and add a parameter, it simply returns the tickets from Department 1 (id 1) but the user is supposed to be seeing only Department 2 (id 2). I've tried changing it around, but with no results. When I check the SQL processes, it the ticket_list_manage function shows that {$table_ticket}.department_id = '' meaning (at least I think) that there is no parameter, and one needs to be passed.
My question: how do I take the result from the first function (get_department_by_user) and pass it as the parameter for table_list_manage()?
EDIT: removed $userid from the second function in question, it doesn't do anything beyond function get_department_by_user
For example, when I change department_id = '2' in the ticket_list_manage() query, I get the ticket's for that department. I also get department_id = '2' when I run the query for get_department_by_user when I'm the appropriate user. I just need to know how to pass that result to my other query.
