How to count all result where date = payday every month

I am not clear on what you are wanting.

Do you want to count how many employees are paid on a specific day in a particular month so something like

SELECT count(*), YEARpayday), MONTH(payday) 
   FROM employees 
   GROUP BY YEAR(payday), MONTH(payday)

Select allOpen in new window

I know you need a CI solution - but first trying to understand what the result set should look like.

Something similar.......
I want to count all employees due for payment on a specific day EVERY MONTH......

E.g
Payrolls every month:

Salaries  to pay on Nov 25 are:
Name                  Salary.       Payday.    
John,                   $2,500.      Nov 25
Ken,                     $3,400.     Nov 25
Ben ,                    $2,100.     Nov 25
Bamidele,             $2,500      Nov 25

Salaries  to pay on Nov 28 are:

AliJohn,                 $2,200.      Nov 28
Magego Ken,         $4,500.     Nov 28
Bendalina,              $2,500.     Nov 28
Bamidele Salem,     $3,200.    Nov 28

The count is just for <span>notification icon </span>

Actual query will list employees by name in datatables.

Any kind assistance will be highly appreciated

So are you asking for a query where you give the day of the month and you want the resulting employees that get paid on that month
Or
Do you want a query that gets all the employees and groups them by the day they get paid?

Not the latter.......get paid on that month.

@chris

In codeigniter view,

I am using datatables to list query results in table formatted style.
Can you help to modify the view code in that direction. I really appreciate your kind help.

Thanks.

So you want something like this - find all employees who are paid on the 25th?

SELECT * FROM employees WHERE DAY(payday) = 25

Select allOpen in new window

Excellent solution  with clear  explanation and  detail demonstration.

@chris, Please dont mind my novicity

this is how am trying to use it with the datatables.
but the table is not fetching any data. can you in your kindness help make this code work

  function salary() {
		$this->load->library('datatables');
			$payDays = $this->datatables->distinct()->select('payday')->get('employees');

			foreach ($payDays->result() as $date):
			$pay = new stdClass();
			$pay->pay_day = $date->pay_day;
			endforeach;
        $this->datatables->select("emp_id, emp_fname, emp_lname, hire_date, salary," .$date->pay_day, FALSE);
		$this->datatables->from('employees');
        $this->datatables->where(array('pay_day' => $date->pay_day));
        $this->datatables->group_by('employees.emp_id');
		$this->datatables->add_column("Actions", "<div class='text-center'>
		
		<a href='" . site_url('salary/view/$1') . "' title='" . lang("view") . "' class='tip btn btn-primary btn-xs' data-toggle='ajax'><i class='fa fa-file-text-o'></i></a>  </div>", "emp_id");
		
        $this->datatables->unset_column('emp_id');
        echo $this->datatables->generate();
	
	}

Select allOpen in new window

 my view looks like this

 $(document).ready(function () {
   $('#CuData').dataTable({
            "aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, '<?= lang('all'); ?>']],
            "aaSorting": [[ 0, "desc" ]], "iDisplayLength": <?= $Settings->rows_per_page ?>,
            'bProcessing': true, 'bServerSide': true,
            'sAjaxSource': '<?= site_url('salary/salary') ?>',
            'fnServerData': function (sSource, aoData, fnCallback) {
                aoData.push({
                    "name": "<?= $this->security->get_csrf_token_name() ?>",
                    "value": "<?= $this->security->get_csrf_hash() ?>"
                });
                $.ajax({'dataType': 'json', 'type': 'POST', 'url': sSource, 'data': aoData, 'success': fnCallback});
            },
            "aoColumns": [null, null, {"mRender":hrld}, {"mRender":currencyFormat}, {"mRender":hrld}, null ]
        });
		
		  $('#CuData').on('click', '.image', function() {
            var a_href = $(this).attr('href');
            var code = $(this).attr('id');
            $('#myModalLabel').text(code);
            $('#image').attr('src',a_href);
            $('#picModal').modal();
            return false;
        });

        $('#CuData').on('click', '.open-image', function() {
            var a_href = $(this).attr('href');
            var code = $(this).closest('tr').find('.image').attr('id');
            $('#myModalLabel').text(code);
            $('#image').attr('src',a_href);
            $('#picModal').modal();
            return false;
        });
		
    });
</script>

<section class="content">
    <div class="row">
        <div class="col-xs-12">
            <div class="box box-primary">
                <div class="box-header">
                    <h3 class="box-title"><?= lang('list_results'); ?></h3>
                </div>
                <div class="box-body">
                    <div class="table-responsive">
                    <table id="CuData" class="table table-bordered table-hover table-striped">
                        <thead>
                        <tr>
						    <th><?php echo $this->lang->line("emp_fname"); ?></th>
                            <th><?php echo $this->lang->line("emp_lname"); ?></th>
                            <th><?php echo $this->lang->line("HireDate"); ?></th>
                       		<th><?php echo $this->lang->line("salary"); ?></th>
							<th><?php echo $this->lang->line("SalaryDay"); ?></th>
                            <th style="width:65px;"><?php echo $this->lang->line("actions"); ?></th>
                        </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td colspan="6" class="dataTables_empty"><?= lang('loading_data_from_server') ?></td>
                            </tr>
                        </tbody>
                    </table>
                </div>
                    <div class="clearfix"></div>
                </div>
            </div>
        </div>
    </div>
</section>

Select allOpen in new window

Please help me and fix this. your assistant is highly appreciated.

Hi @lovelamp4all,

I'm not overly familiar with dataTables for either jQuery or CodeIgniter, but having a quick look at your PHP code, it doesn't really make logical sense. In your original question you talked about getting the employees for each date, but if you're going to be using dataTables, I don't see how that would be feasible.

Also, In your salary() method above, you're selecting all the unique paydays and looping through them, but you're not doing anything with them. Once the foreach block has finished the $date variable is no longer available so you can't use it later on in your script.

Your dataTable select() method seems to be trying to select a column called $date->pay_day - firstly, as I said before, that variable doesn't exist anymore (because you're outside of the foreach block), and secondly, it assumes you want to select a column called something like 2015-11-25 - I'm sure that's not what you need.

Maybe I've misread the question, and if so can you explain in more detail exactly what you want to achieve. Using dataTables in the manner doesn't really match with your opening question.

My bad. Sorry if my code is not so clear.
The simple thing I need is how to encode the data in json as you can see from the datatables

DataType = json
AJaxSource = my_controller/salary
Where salary is a function inside the controller that supplies the json encoded datatype.
Datatables was use to generates the data in json array.

Everything is still ok. Just trying to take the data from controller to the view, use datatables instead of normal html table.

My ordeal appears to be how to use datatables to SECLECT THE TABLE FIELDS or encode the data in JSON ARRAY.

OK. The latter part of your code should do what you want. Basically you need to build the data using the datatables object. Calling the generate() method will run the query and output the data as JSON

$this->load->library('datatables');
$this->datatables->select("emp_id, emp_fname, emp_lname, hire_date, salary, pay_day";
$this->datatables->from('employees');
$this->datatables->group_by('employees.emp_id, employees.pay_day');
$action = sprintf("<div class='text-center'><a href='%s' title='%s' class='tip btn btn-primary btn-xs' data-toggle='ajax'><i class='fa fa-file-text-o'></i></a></div>", site_url(array("salary/view/", $emp_id)), lang("view"))
$this->datatables->add_column("Actions", $action);
$this->datatables->unset_column('emp_id');
echo $this->datatables->generate();

Select allOpen in new window

You will probably need to group your data by pay_day as well as by emp_id.

Nowhere in your code are you passing in a pay_day so I can't see how that would be used to filter the query. In my original code, we grabbed all the different pay_days and then looped through and ran a query for each. That's not going to work if you're using dataTables.

That was quite clear. Alright then. Thank you. Guess I will have to get the list in plain html table ....then style the table somehow. Will let you on anything further


Meanwhile I tried this and get only 1 as notifications count.

Function payday_alert() {
$payDays = $this->db->distinct()->select('payday')->get('employees');

foreach ($payDays->result() as $date):
	$pay = new stdClass();
	$pay->payday = $date->payday;

	$pay->employees = $this->db
        ->where(array ('payday' => $date->payday))
        ->get('employees')
        ->result();

       [b]Return $this->db->count_all_results();[/b]

	$payInfo[] = $pay;
endforeach;
}

Select allOpen in new window

Why am I getting only 1 as notifications even without any 'payday' date in employees table?

Sorry lovelamp4all,

I'm not sure and I'm just finishing work and won't be back for a week, so I've ran out of time to help on this one.

Good luck with it

Thanks al the way. You are a life saver.
Thanks again. I appreciate.