Problem with Between and AND Queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with Between and AND Queries

Author

Topic

TSiqueira
Starting Member

3 Posts

Posted - 2011-04-06 : 12:21:40

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:

$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:

$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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-06 : 12:25:52

Can you verify that the date string php is substituting is the same as the value in the database? It's possible that the string is formatting incorrectly, but hard to say if that is the issue. If you are using the correct ISO string format, then things should work correctly.

Can you post some DDL, DML and expected output?

TSiqueira
Starting Member

3 Posts

Posted - 2011-04-06 : 14:37:29

The dates are coming in through POST to the php script. I double checked the POST values coming in after the form is submitted and they are correct. $from_date is correctly storing "From" and $to_date is correctly storing "To".

Here is some 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[/CODE]
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 and match the ones on the DB. The dates on the DB are in the format mm/dd/yyyy.

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[/CODE]
As you can see entry 165 is missing from the output.

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. 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[/CODE]
The results were the same. The first entry dated 03/24/2011 was omitted from the results when a query was submitted with dates 03/24/2011 to 03/31/2011.

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

TSiqueira
Starting Member

3 Posts

Posted - 2011-04-06 : 16:43:11

Problem solved - Will post solution for anyone else that finds themselves in the same situation.

The problem in this case was not a SQL problem, the query was fine. The problem was with the PHP application.

mysqli_fetch_array was being used in an IF statement to check if the search came back empty, and then mysqli_fetch_array was being called again in the WHILE loop to display the search results.

Problem was that when mysqli_fetch_array was used in the IF statement, it was fetching the first row. Then when mysqli_fetch_array was called in the WHILE loop, it skipped over the first row that had already been fetched, and went straight to the second row.

I should've used mysqli_num_rows instead to check number of rows returned.

Thanks.

Tsiqueira

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources