How to count all result where date = payday every month

I have a table "employees"
in MySQL database  inside which I have 'payday' for employees salary day as date in field in 'employees' table.

Let's say:

payday is
Nov 28 2015 for some employees
Nov 25 2015 for some others


What I can't rap my head around is how to :
$this->db
->select('*')
->from(employees)
->where (Curdate() = 'payday');
->get()
count_all_result();
Return true;

While this works for the first month , how then can I repeat this every 28th, 25th of every month loop through the years......

I am using framework, Codeigniter
lovelamp4allAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
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)

Open in new window

I know you need a CI solution - but first trying to understand what the result set should look like.
1
lovelamp4allAuthor Commented:
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
0
Chris StanyonWebDevCommented:
You're probably going to need to run a couple of queries here. Firstly, run a query to grab the unique pay dates. Then for each of those, run a query to get the employees that are to be paid on that date. Build an array from that and pass it to your view:

// CodeIgniter Controller Code:
$payDays = $this->db->distinct()->select('payday')->get('yourTable');

foreach ($payDays->result() as $date):
	$pay = new stdClass();
	$pay->payday = $date->payday;
	$pay->employees = $this->db->where(array('payday' => $date->payday))->get('yourTable')->result();

	$payInfo[] = $pay;
endforeach;

$this->load->view('yourView', array('payInfo' => $payInfo));

Open in new window


// View Code:
foreach ($payInfo as $pay):
	printf("<p><strong>Salaries to pay on %s are:</strong></p>", $pay->payday);
	
	foreach ($pay->employees as $employee):
		printf("<p>%s | %s</p>", $employee->name, $employee->salary);
	endforeach;
endforeach;

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Julian HansenCommented:
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?
0
lovelamp4allAuthor Commented:
Not the latter.......get paid on that month.
0
lovelamp4allAuthor Commented:
@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.
0
Julian HansenCommented:
So you want something like this - find all employees who are paid on the 25th?

SELECT * FROM employees WHERE DAY(payday) = 25

Open in new window

0
Chris StanyonWebDevCommented:
The CodeIgniter view is basically just HTML, with some PHP included to loop through and output the data. To create a table layout, just use regular HTML. Something like this:

<table>
	<thead>
		<tr>
			<th>Name</th>
			<th>Salary</th>
			<th>Payday</th>
		</tr>
	</thead>
	<tbody>
	<?php foreach ($payInfo as $pay): ?>
		<tr>
			<td colspan="3"><strong>Salaries to pay on <?php echo $pay->payday ?> are:</strong></td>
		</tr>
	
		<?php foreach ($pay->employees as $employee): ?>
		<tr>
			<td><?php echo $employee->name ?></td>
			<td><?php echo $employee->salary ?></td>
			<td><?php echo date("M d", strtotime($employee->payday)) ?></td>
		</tr>
		<?php endforeach; ?>

	<?php endforeach; ?>
	</tbody>
</table>

Open in new window

0
lovelamp4allAuthor Commented:
Excellent solution  with clear  explanation and  detail demonstration.
0
lovelamp4allAuthor Commented:
@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();
	
	}

Open 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>

Open in new window


Please help me and fix this. your assistant is highly appreciated.
0
Chris StanyonWebDevCommented:
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.
0
lovelamp4allAuthor Commented:
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.
0
Chris StanyonWebDevCommented:
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();

Open 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.
1
lovelamp4allAuthor Commented:
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;
}

Open in new window


Why am I getting only 1 as notifications even without any 'payday' date in employees table?
0
Chris StanyonWebDevCommented:
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
0
lovelamp4allAuthor Commented:
Thanks al the way. You are a life saver.
Thanks again. I appreciate.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.