Tricky SQL

Reformed

I know this is not a MySQL board, but I think this is more PHP realted than MySQL related. I'm trying to perform a query on a timesheets database that I have created. I want to limit the query to all records within a 2 week period.

I'm trying to figure out the best way to do this. My dates are stored in the database in seperate columns for day, month and year. What I'm really looking for is a way to get a date range based on 14 days from a specific day (I think).

This is what I have so far:

SELECT month, day, year, hours_worked, 
short_desc, client_name, project_name, category_name, billable, rush, 
invoice_num, timesheet_id AccountName 
FROM timesheet JOIN lu_category_codes, lu_project, lu_client, user_login WHERE
 timesheet.employee = user_login.user_id AND
 timesheet.client = lu_client.client_id AND
 timesheet.category = lu_category_codes.category_id AND
 timesheet.project = lu_project.project_id ORDER BY year, month, day

Now I want to limit this to display only the records for the currnet pay period (14 days). Would it be best to concantonate my date, set up some kind of php variable that contains a date range (based on what, I'm not sure yet), and compare the dates in the database with the range variable to get results? Man that sounds terrible. Is there an easier way?

bastien

seems to me like this is a good place for the date_add function

and i think you need to goto a straight date type column to have a real value (not separate like now, which will only confuse crap out of you when trying a query like this)

SELECT the_date, hours_worked,

short_desc, client_name, project_name, category_name, billable, rush,

invoice_num, timesheet_id AccountName

FROM timesheet JOIN lu_category_codes, lu_project, lu_client, user_login WHERE
timesheet.employee = user_login.user_id AND
timesheet.client = lu_client.client_id AND
timesheet.category = lu_category_codes.category_id AND
timesheet.project = lu_project.project_id
and
the_date between $someDateFromForm and date_add($someDateFromForm, Interval -14 DAY)

ORDER BY year, month, day

TheIceman5

question i have to ask is why on earth did you put the day month and year in different fields??

dbchip2000

Here are some MySQL functions so that you can deal with them as if separate fields. I too think that it should be in one column for queries

Numerical Day of Month => DAYOFMONTH(date_column)
Text Day of Week => DAYNAME(date_column)
Numerical Month => MONTH(date_column)
Text Month => MONTHNAME(date_column)
Numerical Year => YEAR(date_column)

Also, a question of my own. Why not use DATE_SUB instead of DATE_ADD? Is there an advantage?

Reformed

Thanks fore the advice. To answer the question of why I put the date in different columns, I didn't, I inhereted this. I think the idea is that the table can be searched by day, month and year.:p

bastien

so alter the table, and run a query to concatenate the dates into a 'good' date format (this assumes that time is not an important part of the equation of course)...

then you can either drop or maintain the other columns

Reformed

Well, thanks to the help from this forum I have fixed up my timesheets table and created a proper date column. I was able to ditch that 3 column hack.

I am using the advice offered by bastien and it is sort of working, but my problem is that I'm not using any input from a form in this query. This query merely displays values. I need to display the values for a 14 day period, which your advice will do, I'm just trying to figure out how to set a date value to compare the ones in the database to in order to show the information relevant to the 14 period. I am almost there. 🙂

What I think I need to do is replace $someDateFromForm with a value that represents the beginning of the 14 day period. I just don't know how to do that (if that even makes sense). Can anyone suggest how I can create a value in php that represents a 14 day period (once again, if that makes any sense). This is way too confusing.

BuzzLY

Create a field called Start14Day and set it to a date. That date always represents the first day of the 14 day period.

Reformed

Okay, I see where your going but I'm not quite sure I follow you. Can you be a bit more specific (I'm a bit dense). 🙂 Thanks.

BuzzLY

You haven't really stated what you are trying to do beyond:

Now I want to limit this to display only the records for the currnet pay period (14 days).

Where are those periods/dates stored? Are you using PHP to run the query? Try to be a little more specific about what you are trying to do, and perhaps we can be more specific in our answers for you.

Reformed

Sorry, this is really confusing a tough to explain. All I'm trying to do is retrieve a dataset from my database in a PHP script. I need to retrieve all of the data within a 14 day period.

I know how to do that much, but where I'm having a problem is I'm not sure how to set up a beginning and end date to determine when the 14 day period begins and ends. What I want to do is something like this:

select * from timesheets where '$date' between begin_period and end_period;

I'm not sure if that's way to do it or not. So I need to determine how to create a beginning and end date for a 14 day period and create my statement to only retrive the data between those dates. 😕

bastien

if you are not using a form, you need some value for either the startdate or the enddate...what time frame do you have in mind?

the last 14 days...now()-14days??
the last 14 days from last friday?
some other scheduled time frame....

you need to determine one of the values to be able to calc the other and retreive the data from the table...

what is that value?

Reformed

you need some value for either the startdate or the enddate...what time frame do you have in mind?

That is my entire problem. I need my 14 day period to begin and/or end every friday at say, 7:00pm. If I can figure out how to create a variable to do that I'll be in good shape. Is there a way to do that in PHP. BTW, thanks for your patience.

bastien

aha...now we are getting somewhere

I need my 14 day period to begin and/or end every friday at say, 7:00pm

so here we assume that any time this is run it will look for the last friday as the start/end date (call it enddate since we want to get the previous 14 days to that date)


$day= date("w");   //gets todays date as a numeric day of the week

//we know that day of the week uses 0 for SUN and 6 for SAT
//so we take the day of the week and add 2 to get the previous friday

$prevFriday=$day+2;

$endDate= date("D F j, Y, g:i a", mktime (19,0,0,date("m")  ,date("d")-$prevFriday ,date("Y")));

echo $endDate

//now create the sql
sql="select * from table where datefield between $endDate and date_add($endDate, INTERVAL -14 DAY)";

$result=mysql_query($sql) or die ("Can't connect because ".mysql_error());

hth

Reformed

That was awsome. It almost works now. I'm sure I'm doing something funky here. This is where I'm at now:

SELECT date, hours_worked, short_desc, client_name, project_name, category_name, billable, rush, invoice_num, timesheet_id AccountName FROM 
timesheet JOIN 
lu_category_codes, lu_project, lu_client, user_login WHERE 
timesheet.employee = user_login.user_id AND timesheet.client = lu_client.client_id AND timesheet.category = lu_category_codes.category_id AND
 timesheet.project = lu_project.project_id AND
 date BETWEEN '$endDate' AND DATE_ADD('$endDate', INTERVAL - 14 DAY) ORDER BY date

The query currently displays nothing so I have something wrong. The entrys I'm testing with have been entered today, so it should display them since the $endDate is July 18. So close. This is the most complex sql query I have ever dealt with (as it that wasn't obvious). :rolleyes:

bastien

couple of things:

try setting up the sql to look like something below...just makes it easier to read
include the table aliases...makes for less typing and keeps things clearer (see bold below)
include the table aliases in the selected field portion of the sql

like:

select ti.date, ti.hours_worked, pr.short_desc, cl.client_name, pr.project_name....

this can prevent problems and confusion for the db engine because it then knows exactly which table to get the value from when tables have fields with the same name (like DATE or NAME)

SELECT date, hours_worked, short_desc, client_name, project_name, category_name,
billable, rush, invoice_num, timesheet_id AccountName
FROM
timesheet as ti,
lu_category_codes as cd,
lu_project as pr,
lu_client as cl,
user_login as ul
WHERE
ti.employee = ul.user_id
AND ti.client = cl.client_id
AND ti.category = cd.category_id
AND ti.project = pr.project_id
AND date BETWEEN '$endDate' AND DATE_ADD('$endDate', INTERVAL - 14 DAY)
ORDER BY date

Reformed

Alright, I did a little reading about aliases so I'll start using them. I think I have this thing functioning properly now. I changed something and it seems to have worked, but I'm not sure if it will have un-desirable effects.

What I did was I replaced

ANDdate 
BETWEEN '$endDate' 
AND DATE_ADD('$endDate', INTERVAL -14 DAY)

with

AND date 
BETWEEN '$endDate'
AND CURRENT_DATE()

That seems to have done the trick and I changed the date on my machine to see if it does what I want and it seems to be Okay. So it selects based on the date between the '$endDate' and the current date.

I don't fully understand where the value of '$endDate' comes from as you have set it up, so I plan to do some reading toinght so I can better understand it. I must say that handling dates has been the most diffucult thing I have had to do with PHP and MySQL.

I really appriceate all your help, you have really gone out of your way. 🆒

HalfaBee

It sure was a nice peice of logic to work out the last friday.

Friday is 2 days before sun ( 0 value ) so when you subtract that 2+(0 to 6 ) from the current day of the month you get a friday.

e.g
Today is the 23rd and Wed. ( value 4 ) 23 - (4+2) = 18 last Friday.

It took me a minute to workout what he had done.
Even mktime works when you give it a negative day of the month.

The only day that really stuffs up is Sat because last Friday is only 1 day before.
If you try Sat 26 last Friday is still the 18th.
Where I would have said it should have been the 25th.
You probably need to add this line if this is what you expect.
( or just not work on Sat. 🙂 )

$prevFriday=$day+2;
if( $day==6 )
$prevFriday = 1;

HalfaBee

Weedpacket

Shall I be annoying now and point out that strtotime('last Friday') gives an appropriate timestamp?

bastien

damn damn damn....oh well, it was a nice piece of logic though