[eluser]afro[/eluser]
Hi people, can anyone help me out with this, am trying to restrict the no
of people from the same department to go on leave at the same time.
Thanks.
Code: function check_staff_on_leave()
{
$query = "SELECT Department, COUNT(*) as 'On Leave'
FROM checkoff
WHERE Department = '".$this->input->post('department')."'
GROUP BY Department
HAVING COUNT(*) > 3";
$this->db->query($query);
$q = $this->db->get();
return $q->num_rows();
if($query->num_rows() > 3)
{
return false;
}
else
{
$query->free_result();
return true;
}
}
Am getting this error
Error Number: 1096
No tables used
SELECT *
[eluser]kreamik[/eluser]
can post the error message?
[eluser]WanWizard[/eluser]
I hope this is an example, since you're asking for a SQL injection if you code your queries this way. Use CI's active record!
And you are doing a $this->db->get() without selecting anything.
[eluser]afro[/eluser]
Wan
I have tried using the active records and I am no getting the desired results
Code: function check_staff_on_leave()
{
$this->db->select('Department, COUNT(*)');
$this->db->from('checkoff');
$where = "Department = '".$this->input->post('department')."'";
$this->db->where($where);
$q = $this->db->get();
$query = $this->db->count_all_results();
$this->db->having(array('Department >' => 3));
if($query > 3)
{
return true;
}
else
{
return false;
}
}
There is no error but the results is not what i expected
[eluser]davidbehler[/eluser]
Your query doesn't make sense at all. So you want to get a list of all deparments with the number of people on leave, right?
Code: $this->db->select('Department, COUNT(*) AS on_leave', FALSE);
$this->db->from('checkoff');
$this->db->where('Department', $this->input->post('department'));
$this->db->group_by('Department');
$result = $this->db->get();
if($result->num_rows() > 0) {
$rows = $result->result_array();
return TRUE;
} else {
return FALSE;
}
Explanation: The 2nd parameter for $this->db->select() tells CI not to escape your select parameter. If you leave the parameter out, CI would try to escape the COUNT(*) part and you would end up with selecting a string instead of getting the number of rows.
If your query returns atleast one row (meaning atleast one person is on leave), then you can use all returned rows (in $rows) to run whatever check you want.
About your second post: You can't use HAVING with a non-group-function in MySQL, that's what WHERE is there for. If however you want to get a list of all Departments with more than 3 persons on leave, then that's something you can do quite easily. Add this line right after the $this->db->group_by() call:
Code: $this->db->having('on_leave >', 3);
or if that doesn't work try this:
Code: $this->db->having('COUNT(*) >', 3);
[eluser]afro[/eluser]
I have tried what Waldmeister advised me to do, but am not getting what I really want,
what i want is that in an event that a staff form a particular department is trying to apply for a leave and already 3
staffs from the same department are on leave the system should inform the staff that they are 3 staff already on leave thus
he/she does not qualify to apply for the leave. Here what i have being trying, I will really appreciate if u guys will help me out.
the controller.
Code: if($this->form_validation->run() == FALSE){
$this->load->view('leave');
}else
{
$this->load->model('Leave_model');
$query = $this->Leave_model->appliedleave();
//successfull applicant//
if($query)
{
$data['message'] = $this->input->get_post('staffname').' your application has been successfully sent.'. '<br>'. 'You will receive an email as soon as it has been approved.';
$this->load->view('leavesuccess',$data);
}
//in case where more than three staff from one department apply for a leave//
else
{
$data['message'] = $this->input->get_post('staffname'). " three staffs from your department are already on leave";
$this->load->view('leavesuccess',$data);
}
}
}
the model
Code: function check_staff_on_leave()
{
$this->db->select('Department, COUNT(*) AS on_leave', FALSE);
$this->db->from('checkoff');
$this->db->where('Department', $this->input->post('department'));
$this->db->group_by('Department');
$this->db->having('COUNT(*) >', 3);
$result = $this->db->get();
if($result->num_rows() > 0)
{
$rows = $result->result_array();
return FALSE;
}
else
{
return TRUE;
}
}
function appliedleave()
{
if($this->check_staff_on_leave($this->input->get_post('department')))
{
$applied = array(
'StaffName' =>$this->input->post('staffname'),
'StaffId' =>$this->input->post('staffid'),
'Date' =>$this->input->post('currentdate'),
'LeaveBalance' =>$this->input->post('leavebalance'),
'NoofDaysRequested' =>$this->input->post('noofdays'),
'Department' =>$this->input->post('department'),
'LeaveType' =>$this->input->post('leavetype'),
'CommencementDate' =>$this->input->post('commencedate'),
'Dateforreportingback' =>$this->input->post('reportdate'),
'CurrentProject' =>$this->input->post('currentproject'),
'HandedTo' =>$this->input->post('handoverto'),
'comment' =>$this->input->post('comment') );
$checkoff = array(
'StaffName' =>$this->input->post('staffname'),
'StaffId' =>$this->input->post('staffid'),
'Date' =>$this->input->post('currentdate'),
'DaysRequested' =>$this->input->post('noofdays'),
'Department' =>$this->input->post('department'),
'CommencementDate' =>$this->input->post('commencedate'),
'ReportingBack' =>$this->input->post('reportdate') );
$this->db->insert('checkoff', $checkoff);
$this->db->insert('appliedleaves', $applied);
}
}
[eluser]davidbehler[/eluser]
your appliedleave() function doesn't return anything, that's propably why your check doesn't work:
Code: $query = $this->Leave_model->appliedleave();
if($query) {
}
just add return TRUE; at the end of your appliedleave() function and you should be good.
In case this doesn't fix it you might want to check what query is generated by the check_staff_on_leave() function. Add
Code: echo $this->db->last_query();
after $result = $this->db->get(); and it will display the query that was executed.
[eluser]afro[/eluser]
the query that is being executed is the right one, but the results are not what i needed, can you still have a look at it,
could it been the controller?
the query is here.
Code: SELECT Department, COUNT(*) AS on_leave FROM (checkoff) WHERE `Department` = 'Administration' GROUP BY Department HAVING COUNT(*) > 3
[eluser]afro[/eluser]
waldmeister
Thanks alot men, its now working,I had place the return TRUE in the wrong place
I like how you handled the situation, can i get your personal email address so that next time
I get assistance from you directly.
once again thank you.
[eluser]davidbehler[/eluser]
Glad I could help. But I don't think I'm gonna give you my personal email address just yet. I'm not that kind of man 
Seriously though, I'm an active CI user and I like to give something back to the community by helping out where ever I can in this forum. So when ever you run into a new problem, it's propably best if you post here and I'm pretty sure that you'll get some help by either me or someone else (there are plenty of other competent users around here!).
In case you insist on me being your first (only?) source of help and being able to contact me by email, you might wanna consider hiring me 
|
