troy
|
| Posted: 09/21/2005, 3:40 AM |
|
hi there
using CCS3.49 PHP MySQL
This must be easy for someone.
I need the SQL statement to show the records between the current date and a date 3 months earlyer.
I have built a grid witch is set to a cron in Cpanel witch sends a email to the users in the database whose email is shown on the grid. that all works fine and the emails are sent out as normal.
But i only need the records between the current date and 3 months earlyer to show up on the grid, so only them dates are emailed to the members.
can any one help or as any advice..
cheers TROY..
|
|
|
 |
TROY
|
| Posted: 09/21/2005, 3:45 AM |
|
i must also add that i dont want to do this manually though a search, becauce the dates wont change , they will be set and will be all automatic. if you no what i mean ..
cheers Troy...
|
|
|
|
Walter Kempees
|
| Posted: 09/21/2005, 6:07 AM |
|
first: identify the field to filter on, let's call it some_date
second: today in MySql is now() or curdate() with or without the timestamp
your field can be of several type, lets assume it's of type DATE
So:
select * from table_name where (some_date >= DATE_ADD(CURDATE(),
INTERVAL -90 DAY) );
or even better
select * from table_name where (some_date >= DATE_ADD(CURDATE(),
INTERVAL -3 MONTH) );
should give you all records with a some_date between today (is implicit) and
90 days ago.
In the VQB you can add this clause as an AND to the existing where clause.
Hope it helps.
Walter
"troy" <troy@forum.codecharge> schreef in bericht
news:5433138afbc201@news.codecharge.com...
> hi there
>
> using CCS3.49 PHP MySQL
>
> This must be easy for someone.
> I need the SQL statement to show the records between the current date and
> a
> date 3 months earlyer.
>
> I have built a grid witch is set to a cron in Cpanel witch sends a email
> to
> the users in the database whose email is shown on the grid. that all
> works
> fine and the emails are sent out as normal.
> But i only need the records between the current date and 3 months earlyer
> to
> show up on the grid, so only them dates are emailed to the members.
>
> can any one help or as any advice..
>
> cheers TROY..
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Walter Kempees
|
| Posted: 09/21/2005, 8:05 AM |
|
if your field is not type date, datestamp it can still be done, report back.
"Walter Kempees" <kempe819@planet.nl> schreef in bericht
news:dgrlup$adb$1@news.codecharge.com...
> first: identify the field to filter on, let's call it some_date
> second: today in MySql is now() or curdate() with or without the timestamp
> your field can be of several type, lets assume it's of type DATE
>
> So:
>
> select * from table_name where (some_date >= DATE_ADD(CURDATE(),
> INTERVAL -90 DAY) );
>
> or even better
>
> select * from table_name where (some_date >= DATE_ADD(CURDATE(),
> INTERVAL -3 MONTH) );
>
> should give you all records with a some_date between today (is implicit)
> and 90 days ago.
>
> In the VQB you can add this clause as an AND to the existing where clause.
> Hope it helps.
>
> Walter
>
> "troy" <troy@forum.codecharge> schreef in bericht
>news:5433138afbc201@news.codecharge.com...
>> hi there
>>
>> using CCS3.49 PHP MySQL
>>
>> This must be easy for someone.
>> I need the SQL statement to show the records between the current date and
>> a
>> date 3 months earlyer.
>>
>> I have built a grid witch is set to a cron in Cpanel witch sends a email
>> to
>> the users in the database whose email is shown on the grid. that all
>> works
>> fine and the emails are sent out as normal.
>> But i only need the records between the current date and 3 months earlyer
>> to
>> show up on the grid, so only them dates are emailed to the members.
>>
>> can any one help or as any advice..
>>
>> cheers TROY..
>> ---------------------------------------
>> Sent from YesSoftware forum
>> http://forums.codecharge.com/
>>
>
>
|
|
|
|
troy
|
| Posted: 09/21/2005, 9:27 PM |
|
hi Walter Kempees, thanks for your reply.
in the VQB is the condition type a "parameter "and the "type "a URL
I made a mistake before.
what i want to do is show the records from todays date and 3 months after..
So would that be +30 instead od -30 and
condition "greater than or equal(>=)
cheers Troy.....
|
|
|
|
Walter Kempees
|
| Posted: 09/22/2005, 1:07 AM |
|
type is expression, i think
from todays date and three months after ?
You want to look in te future?
Then you're right.
"troy" <troy@forum.codecharge> schreef in bericht
news:54332329b158b0@news.codecharge.com...
> hi Walter Kempees, thanks for your reply.
>
> in the VQB is the condition type a "parameter "and the "type "a URL
> I made a mistake before.
> what i want to do is show the records from todays date and 3 months
> after..
> So would that be +30 instead od -30 and
> condition "greater than or equal(>=)
>
> cheers Troy.....
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
troy
|
| Posted: 09/22/2005, 1:16 AM |
|
hi Walter Kempees
what it is ,
I have members wich store eg: passport details, and the grid i am trying to show is expiry dates.
so im wanting to show all expiry dates from the current date the page is run , to 3 months in the future, so then they will recive a email to let them no that there passport will expire in 3 months..
will let you no when i get it working
cheers troy
|
|
|
|
troy
|
| Posted: 09/22/2005, 1:34 AM |
|
hi there
when i use a "type" expression i get this error sent to me:
parse error, unexpected T_STRING
i will keep trying.
|
|
|
|
troy
|
| Posted: 09/22/2005, 5:03 AM |
|
hi there
in my VQB i am using this expression to select the current date and 90 days in the future :
'passport.passport_dateexpiry >= DATE_ADD(CURDATE(), INTERVAL +90 DAY));'
I no there is a date within these dates but it is not showing.
does this query look aright to you.
cheers Troy.
|
|
|
|
Walter Kempees
|
| Posted: 09/22/2005, 5:50 AM |
|
looks ok
select date_add(curdate(), interval +90 day) from "YOURTABLE"
in a MySql query tool should deliver 2005-12-21
that is if you do it today 
however : what type is your field pasport_dateexpiry ?
"troy" <troy@forum.codecharge> schreef in bericht
news:543329d9173b2c@news.codecharge.com...
> hi there
>
> in my VQB i am using this expression to select the current date and 90
> days in
> the future :
>
> 'passport.passport_dateexpiry >= DATE_ADD(CURDATE(), INTERVAL +90 DAY));'
>
> I no there is a date within these dates but it is not showing.
>
> does this query look aright to you.
>
> cheers Troy.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
troy
|
| Posted: 09/22/2005, 2:16 PM |
|
hi there
'passport.passport_dateexpiry is a date field.
if i remove this query then all the member dates come up .
i will keep trying
will let you no when it works.
cheers troy..
|
|
|
|
troy
|
| Posted: 09/23/2005, 1:42 AM |
|
nope, must be a bug in the VQB, because its not working for me.
have spent 12 hours on it , and still only comes up with all records.
i put the query in the expression and its doesnt do anything,
anyway thanks for your help Walter Kempees
|
|
|
|
Damian Hupfeld
|
| Posted: 09/24/2005, 1:10 AM |
|
Have you set the Date Format correctly?
"troy" <troy@forum.codecharge> wrote in message
news:54333c00bc925b@news.codecharge.com...
> nope, must be a bug in the VQB, because its not working for me.
> have spent 12 hours on it , and still only comes up with all records.
>
> i put the query in the expression and its doesnt do anything,
>
> anyway thanks for your help Walter Kempees
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
troy
|
| Posted: 09/24/2005, 2:58 AM |
|
hi there
field type=date
Condition Type=parameter
formatt=yyyy-mmdd HH:nn:ss
condition=greater than or equal (>=)
parameter= '{date_add(curdate(),interval +90));}'
type=Expression
formatt=LongDate
SELECT *
FROM passport INNER JOIN users ON
passport.users_id = users.users_id
WHERE users.users_id = {users_users_id}
AND passport.passport_dateexpiry >= '{date_add(curdate(),interval +90));}'
is there an error in this do you think, or should this be working.
cheers troy
|
|
|
|
Damian Hupfeld
|
| Posted: 09/24/2005, 4:49 AM |
|
I'll be *really* honest - I have to work really hard at getting this stuff
to work LOL!
I use the following code to display all members whose birthdays are in the
next Month. I used a slightly different approach - I looked at the DD and MM
fields only and displayed everything where the CURRENT DD is more than the
BDAY DD and the BDAY MM is the same OR the CURRENT DD is less than the BDAY
DD and the CURRENT MM + 1 is the same as the BDAY MM - I hope that made
sense!
<<my sample>>
SELECT user_fname, user_lname, user_email, user_bday, user_id
FROM user
WHERE month(user_bday) = month(now()) AND dayofmonth(user_bday) >=
dayofmonth(now())
OR month(user_bday) = month(DATE_ADD(NOW(), INTERVAL 1 MONTH)) AND
dayofmonth(user_bday) <= dayofmonth(now())
ORDER by month(user_bday), dayofmonth(user_bday)
<<end sample>>
Then again several people here have really helped me out in the past so here
goes....
SELECT *
FROM passport INNER JOIN users
ON passport.users_id = users.users_id
WHERE passport.passport_dateexpiry <= date_add(NOW(),interval 3 month)
I created a sample database and tested this code and it works... The
differences are:
1. Your greaterthan/lessthan was wrong way round.
2. I took out the WHERE users.users_id = {users_users_id} - I think you are
trying to display this info for the current logged in user - so you need to
somehow use the CCGetSession(UserID) - but I dont have time to try that part
out tonight
3. I changed + 90 to 3 month
4. I think there were too many {}''() in there!
Hope this helps you somewhat.
regards
Damian Hupfeld
http://www.nexthost.com.au/services.php
"troy" <troy@forum.codecharge> wrote in message
news:54335234f06299@news.codecharge.com...
> hi there
>
> field type=date
>
> Condition Type=parameter
> formatt=yyyy-mmdd HH:nn:ss
> condition=greater than or equal (>=)
> parameter= '{date_add(curdate(),interval +90));}'
> type=Expression
> formatt=LongDate
>
>
> SELECT *
> FROM passport INNER JOIN users ON
> passport.users_id = users.users_id
> WHERE users.users_id = {users_users_id}
> AND passport.passport_dateexpiry >= '{date_add(curdate(),interval 90
> DAY));}'
>
> is there an error in this do you think, or should this be working.
>
> cheers troy
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Walter Kempees
|
| Posted: 09/24/2005, 8:03 AM |
|
YES there is an error in your syntax!
See my previous mail, and check google on "MySql date_add"
The error is you are not talling date_add what the interval should be, the
correct syntax is
date_add(curdate(),interval +90 day)
"troy" <troy@forum.codecharge> schreef in bericht
news:54335234f06299@news.codecharge.com...
> hi there
>
> field type=date
>
> Condition Type=parameter
> formatt=yyyy-mmdd HH:nn:ss
> condition=greater than or equal (>=)
> parameter= '{date_add(curdate(),interval +90));}'
> type=Expression
> formatt=LongDate
>
>
> SELECT *
> FROM passport INNER JOIN users ON
> passport.users_id = users.users_id
> WHERE users.users_id = {users_users_id}
> AND passport.passport_dateexpiry >= '{date_add(curdate(),interval +90));}'
>
> is there an error in this do you think, or should this be working.
>
> cheers troy
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Walter Kempees
|
| Posted: 09/24/2005, 8:08 AM |
|
To test this use any tool you are using to fire SQL queries on your Db.
Do this:
select date_add(curdate(),interval +90) from any_table_you_have_in_your_db
and it will display the resulting date
By The Way, we have a table in all our MySQL databases called DUAL.
DUAL contains 1 field called DUMMY char(1).
This is an Oracle trick allowing us to do
select date_add(curdate(),interval +90) from dual;
Walter Kempees
In the MySql online manual there is a great example using date_add to select
peoples birthdays in the upcoming week
"troy" <troy@forum.codecharge> schreef in bericht
news:54335234f06299@news.codecharge.com...
> hi there
>
> field type=date
>
> Condition Type=parameter
> formatt=yyyy-mmdd HH:nn:ss
> condition=greater than or equal (>=)
> parameter= '{date_add(curdate(),interval +90));}'
> type=Expression
> formatt=LongDate
>
>
> SELECT *
> FROM passport INNER JOIN users ON
> passport.users_id = users.users_id
> WHERE users.users_id = {users_users_id}
> AND passport.passport_dateexpiry >= '{date_add(curdate(),interval +90));}'
>
> is there an error in this do you think, or should this be working.
>
> cheers troy
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Walter Kempees
|
| Posted: 09/24/2005, 8:15 AM |
|
Example:
Table persoon (dutch for person)
(translation of fields and identifiers)
psn.id = unique primary autonum
psn.voorletters = initials
psn.naam = christian name
psn.voorvoegsels = the "of" in Troy of Rome
psn.geboortedatum = day of birth (type = date, nulls alowed)
VERJAARDAG = bitrhday date
Leeftijd = age
dagen = days
SELECT psn.id , concat_ws('', '', concat_ws(' ', psn.voorletters,
concat_ws('-', psn.naam_egt,concat_ws('', psn.voorvoegsel,'
',psn.naam ) ) ), '') as Naam ,
date_format(CONCAT(((RIGHT(psn.geboortedatum,5) < RIGHT(CURRENT_DATE,5))+
YEAR(CURRENT_DATE)), RIGHT(psn.geboortedatum,6)),'%d-%m-%Y') AS 'Verjaardag'
, year(current_date) - year(psn.geboortedatum) + 1 as Leeftijd ,
TO_DAYS(CONCAT(((RIGHT(psn.geboortedatum,5) < RIGHT(CURRENT_DATE,5)) +
YEAR(CURRENT_DATE)), RIGHT(psn.geboortedatum,6))) - TO_DAYS(CURRENT_DATE) AS
'dagen'
FROM persoon as psn
WHERE (TO_DAYS(CONCAT(((RIGHT(psn.geboortedatum,5) < RIGHT(CURRENT_DATE,5))
+ YEAR(CURRENT_DATE)), RIGHT(psn.geboortedatum,6))) - TO_DAYS(CURRENT_DATE)
<= 7)
ORDER BY psn.geboortedatum, RIGHT(psn.geboortedatum,5)
Will give you an order list displaying full name details + birthday date +
age for all people having a birthday in the upcoming 7 days.
Walter
"Damian Hupfeld" <damian.hupfeld@itng.com.au> schreef in bericht
news:dh3efb$vh8$1@news.codecharge.com...
> I'll be *really* honest - I have to work really hard at getting this stuff
> to work LOL!
>
> I use the following code to display all members whose birthdays are in the
> next Month. I used a slightly different approach - I looked at the DD and
> MM fields only and displayed everything where the CURRENT DD is more than
> the BDAY DD and the BDAY MM is the same OR the CURRENT DD is less than the
> BDAY DD and the CURRENT MM + 1 is the same as the BDAY MM - I hope that
> made sense!
>
> <<my sample>>
> SELECT user_fname, user_lname, user_email, user_bday, user_id
> FROM user
> WHERE month(user_bday) = month(now()) AND dayofmonth(user_bday) >=
> dayofmonth(now())
> OR month(user_bday) = month(DATE_ADD(NOW(), INTERVAL 1 MONTH)) AND
> dayofmonth(user_bday) <= dayofmonth(now())
> ORDER by month(user_bday), dayofmonth(user_bday)
> <<end sample>>
>
> Then again several people here have really helped me out in the past so
> here goes....
>
> SELECT *
> FROM passport INNER JOIN users
> ON passport.users_id = users.users_id
> WHERE passport.passport_dateexpiry <= date_add(NOW(),interval 3 month)
>
> I created a sample database and tested this code and it works... The
> differences are:
> 1. Your greaterthan/lessthan was wrong way round.
> 2. I took out the WHERE users.users_id = {users_users_id} - I think you
> are trying to display this info for the current logged in user - so you
> need to somehow use the CCGetSession(UserID) - but I dont have time to try
> that part out tonight
> 3. I changed + 90 to 3 month
> 4. I think there were too many {}''() in there!
>
> Hope this helps you somewhat.
>
> regards
> Damian Hupfeld
> http://www.nexthost.com.au/services.php
>
>
>
>
> "troy" <troy@forum.codecharge> wrote in message
>news:54335234f06299@news.codecharge.com...
>> hi there
>>
>> field type=date
>>
>> Condition Type=parameter
>> formatt=yyyy-mmdd HH:nn:ss
>> condition=greater than or equal (>=)
>> parameter= '{date_add(curdate(),interval +90));}'
>> type=Expression
>> formatt=LongDate
>>
>>
>> SELECT *
>> FROM passport INNER JOIN users ON
>> passport.users_id = users.users_id
>> WHERE users.users_id = {users_users_id}
>> AND passport.passport_dateexpiry >= '{date_add(curdate(),interval 90
>> DAY));}'
>>
>> is there an error in this do you think, or should this be working.
>>
>> cheers troy
>> ---------------------------------------
>> Sent from YesSoftware forum
>> http://forums.codecharge.com/
>>
>
>
|
|
|
|
troy
|
| Posted: 09/24/2005, 11:24 PM |
|
hi there
i have got it working.
in the grid properties i selected SQL instead of Table and put this query in.
SELECT *
FROM passport INNER JOIN users
ON passport.users_id = users.users_id
WHERE
month(passport.passport_dateexpiry) = month(DATE_ADD(NOW(), INTERVAL 1 MONTH))
OR month(passport.passport_dateexpiry) = month(DATE_ADD(NOW(), INTERVAL 2 MONTH))
OR month(passport.passport_dateexpiry) = month(DATE_ADD(NOW(), INTERVAL 3 MONTH))
it seems to work , the only thing it doesnt do is bring up the records of the current month, but i can live with that, but it does bring up all the records (or seems to) for the next 3 months..
Anyway thanks for taking the time to show me what to do, I could not have done it with ut your help.
thanks again.
cheers Troy....
|
|
|
|
Damian Hupfeld
|
| Posted: 09/25/2005, 1:20 AM |
|
Hi Troy,
There is a problem in your code - not only is it not showing the current
month but is also not checking the expiry YEAR.
The code I posted earlier:
SELECT *
FROM passport INNER JOIN users
ON passport.users_id = users.users_id
WHERE passport.passport_dateexpiry <= date_add(NOW(),interval 3 month)
is doing both.
regards
Damian
"troy" <troy@forum.codecharge> wrote in message
news:5433642a1557bb@news.codecharge.com...
> hi there
>
> i have got it working.
> in the grid properties i selected SQL instead of Table and put this query
> in.
>
> SELECT *
> FROM passport INNER JOIN users
> ON passport.users_id = users.users_id
> WHERE
> month(passport.passport_dateexpiry) = month(DATE_ADD(NOW(), INTERVAL 1
> MONTH))
> OR month(passport.passport_dateexpiry) = month(DATE_ADD(NOW(), INTERVAL 2
> MONTH))
> OR month(passport.passport_dateexpiry) = month(DATE_ADD(NOW(), INTERVAL 3
> MONTH))
>
> it seems to work , the only thing it doesnt do is bring up the records of
> the
> current month, but i can live with that, but it does bring up all the
> records
> (or seems to) for the next 3 months..
>
> Anyway thanks for taking the time to show me what to do, I could not have
> done
> it with ut your help.
> thanks again.
> cheers Troy....
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
troy
|
| Posted: 09/25/2005, 2:23 AM |
|
thanks Damian Hupfeld, yes thanks for pointing that out.
i have put your query in and it works great.
thanks so much for your help.
regards Troy....
|
|
|
|
Walter Kempees
|
| Posted: 09/25/2005, 1:08 PM |
|
MySQL [retrieving the date but not time]
SELECT CURDATE();MySQL [retrieving date and time]
SELECT NOW();
|
|
|
|
|
