Problem with Between and AND Queries

tsiqueira · April 6th, 2011, 12:06 PM

Hi,

I have an application that keeps track of clock in and clock out times for employees. The application is written in PHP/MySQL, and the client side is HTML/PHP/Javascript on a website. When an employee clocks in an entry is created on the "timesheet" table.

The entry includes an entry id, the employee id, date, time clocked in and then the entry is updated using the UPDATE command when an employee clocks out.

I am now trying to design a tool that allows the employer to search the database for a specific employee's clock in and clock out times between two dates ($from_date, $to_date).

The query i am using is:

PHP Code:


			
$sql = "SELECT employees.id, employees.firstname, employees.lastname, timesheet.date, timesheet.time_in, timesheet.time_out

            FROM employees

            INNER JOIN timesheet ON employees.id = timesheet.employeeid

            WHERE employeeid = '$employee_id' AND timesheet.date

            BETWEEN '$from_date' AND '$to_date'

            ORDER BY timesheet.date ASC";

I also tried:

PHP Code:


			
$sql = "SELECT employees.id, employees.firstname, employees.lastname, timesheet.date, timesheet.time_in, timesheet.time_out

            FROM employees

            INNER JOIN timesheet ON employees.id = timesheet.employeeid

            WHERE employeeid = '$employee_id' 

            AND timesheet.date >= '$from_date' AND timesheet.date <= '$to_date'

            ORDER BY timesheet.date ASC";

The results i get are always missing the first entry of the range. I understand that the BETWEEN operator is inclusive, which should be the reason the above is happening. I tested using the >= and <= operators and the results did not change.

There has to be a way to have the query include the first date in the range...or is there?

Any input is appreciated. Thanks in advance.

TSiqueira

Imar · April 6th, 2011, 01:14 PM

Hi there,

Could it be that your $from_date and $to_date variables contain a time part other than 00:00:00? That would filter out records that took place earlier during the day.

Cheers,

Imar

tsiqueira · April 6th, 2011, 02:19 PM

Not exactly, let me add some small amount of data from the DB to see if it helps illustrate the problem:

Data from DB, :

Code:

entry_id    date        time_in      time_out
163       03/07/2011   1:34:29 PM   2:20:33 PM 
164       03/07/2011   2:21:10 PM   0 
165       03/24/2011   3:10:16 PM   3:12:13 PM 
166       03/24/2011   3:12:22 PM   3:52:48 PM  
167       03/24/2011   3:52:51 PM   4:10:38 PM  
168       03/24/2011   4:11:13 PM   0  
169       03/25/2011   3:04:48 PM   3:04:54 PM  
170       03/31/2011   9:48:08 AM   9:50:50 AM

The application provides a simple form that takes in the employee name, a field for 'date from' and a field for 'date to'. The input for the date fields are filled out using a popup calendar which ensures that the format the dates are input are correct.

When i submit a query with a date range of: (date_from) 03/24/2011 to (date_to) 03/29/2011, this is the output i get:

Code:

03/24/2011    3:52:51 PM   4:10:38 PM
03/24/2011    3:12:22 PM   3:52:48 PM
03/24/2011    3:10:16 PM   3:12:13 PM
03/25/2011    3:04:48 PM   3:04:54 PM

As you can see entry 165 is missing from the output.

I hope it's not too much information, but i noticed something else as well..

I had about 5 entries with the same date but different clock in and clock out times (03/24/2011) that the query was pulling from the DB. After reading Imar's reply, i decided to change the entries so that the dates were consecutive, 03/24/2011, 03/25/2011, 03/26/2011, until 03/31/2011, to see if it would make a difference.

Code:

entry_id    date        time_in      time_out
163       03/07/2011   1:34:29 PM   2:20:33 PM 
164       03/07/2011   2:21:10 PM   0 
165       03/24/2011   3:10:16 PM   3:12:13 PM 
166       03/25/2011   3:12:22 PM   3:52:48 PM  
167       03/26/2011   3:52:51 PM   4:10:38 PM  
168       03/27/2011   4:11:13 PM   0  
169       03/28/2011   3:04:48 PM   3:04:54 PM  
170       03/31/2011   9:48:08 AM   9:50:50 AM

The results were the same. The first entry dated 03/24/2011 was omitted from the results.

Code:

03/25/2011   3:12:22 PM   3:52:48 PM
03/26/2011   3:52:51 PM   4:10:38 PM
03/27/2011   4:11:13 PM   0
03/28/2011   3:04:48 PM   3:04:54 PM
03/31/2011   9:48:08 AM   9:50:50 AM

Imar · April 6th, 2011, 02:57 PM

Can you post the exact query that is being executed against the database? Don't know much about MySql so not sure if it has a query profiler but it would be interesting to see what gets executed exactly.

Also, have you tried executing this against the database directly, outside your application? Can't this be an application issue that accidentally skips the first record?

Imar

tsiqueira · April 6th, 2011, 04:04 PM

Quote:

Originally Posted by Imar

Can you post the exact query that is being executed against the database? Don't know much about MySql so not sure if it has a query profiler but it would be interesting to see what gets executed exactly.

Also, have you tried executing this against the database directly, outside your application? Can't this be an application issue that accidentally skips the first record?

Imar

Ah ha! Don't know why i didn't think of that earlier. Thanks a lot for that.

I ran the query directly on the database and get the right results every time. The query that i'm using works, the problem is somewhere on the application. Very odd.. but at least it narrowed it down somewhat.

I'm going to chase this bug down. Thanks for insight!

Imar · April 6th, 2011, 04:06 PM

You're welcome.

Searching Google for PHP Mysql first record missing brings up some interesting stuff.... ;-)

http://www.google.com/#sclient=psy&h...record+missing

Cheers,

Imar

tsiqueira · April 6th, 2011, 04:34 PM

Yeah turns out the problem came from using mysqli_fetch_array twice, the first time was just to check if the row came back empty, and the second to actually display the information. However, when i fetched it the first time, it pulled the information, and then when i pulled it the second time it was omitting the information already fetched by the first mysqli_fetch_array.

Using mysqli_num_rows might be a better idea.

Thanks again for your attention!

Tsiqueira