Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 6th, 2011, 12:06 PM
Registered User
 
Join Date: Apr 2011
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default Problem with Between and AND Queries

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
Reply With Quote
  #2 (permalink)  
Old April 6th, 2011, 01:14 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #3 (permalink)  
Old April 6th, 2011, 02:19 PM
Registered User
 
Join Date: Apr 2011
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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

Last edited by tsiqueira; April 6th, 2011 at 02:22 PM..
Reply With Quote
  #4 (permalink)  
Old April 6th, 2011, 02:57 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
The Following User Says Thank You to Imar For This Useful Post:
tsiqueira (April 6th, 2011)
  #5 (permalink)  
Old April 6th, 2011, 04:04 PM
Registered User
 
Join Date: Apr 2011
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Imar View Post
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!
Reply With Quote
  #6 (permalink)  
Old April 6th, 2011, 04:06 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #7 (permalink)  
Old April 6th, 2011, 04:34 PM
Registered User
 
Join Date: Apr 2011
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem while joining two queries yogeshyl SQL Language 0 July 21st, 2008 04:08 AM
problem with parameter queries ph0neman Classic ASP Basics 2 July 1st, 2008 08:57 PM
Combining Queries or results from 2 queries Ford SQL Server 2000 24 November 7th, 2005 08:54 PM
Select with multiple queries problem djbst84 SQL Language 0 May 21st, 2004 07:58 PM
MySQL queries problem hosefo81 PHP Databases 1 January 22nd, 2004 07:15 AM



All times are GMT -4. The time now is 03:02 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.