SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 47
Thread: Subtract Dates
-
Jul 7, 2008, 19:56 #1
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Subtract Dates
Hi
I have a database containing dates, is it possible to retrieve all dates within a specific year range, then subtract the retrieved dates from today's date?
-
Jul 7, 2008, 20:10 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
yes, and you can do this entirely with SQL, no php necessary
-
Jul 8, 2008, 22:57 #3
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So I don't need PHP for any of it?
I want to search the DOB (DDMMYYYY) field within a database, for any users that are between the ages specified in a form (between 25 and 50)
How would I do this?
-
Jul 9, 2008, 00:02 #4
- Join Date
- Jul 2008
- Posts
- 5,757
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Code:select user , dob from birthdays where dob between curdate() - interval 25 years and curdate() - interval 50 years
-
Jul 9, 2008, 03:28 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:SELECT somedate , DATEDIFF(CURRENT_DATE,somedate) AS days_diff FROM daTable WHERE somedate >= '2005-01-01' AND somedate < '2006-01-01'
this also works, but is inefficient:Code:WHERE YEAR(somedate) = 2005
-
Jul 12, 2008, 08:00 #6
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
How do I return any records contained within a database, if the value of drop down box 1 and drop down box 2 is between the difference of todays date, and the DOB stored in the database?
I hope this makes sense.
-
Jul 12, 2008, 08:11 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jul 12, 2008, 08:24 #8
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
What do you mean?
drop-down menu 1 contains values 25-99
drop-down menu 2 contains values 25-99
Subtract today's date from all DOB dates within the database, in order to return the total years difference. If the total years is between drop-down 1's and drop-down 2's values, then the whole record should be displayed!
Hope this makes more sense!
-
Jul 12, 2008, 08:33 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:SELECT something FROM daTable WHERE DATEDIFF(CURRENT_DATE,DOB) BETWEEN $dropdown1 AND $dropdown2
-
Jul 12, 2008, 08:41 #10
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Do I need to replace "CURRENT_DATE" with today's date, or can I use the date() function?
Code:SELECT something FROM daTable WHERE DATEDIFF(date(),DOB) BETWEEN $dropdown1 AND $dropdown2
-
Jul 12, 2008, 08:46 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jul 12, 2008, 10:33 #12
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
OK. I now have the following code, but the nothing gets returned from the database.
I have a record within the database, that contains the DOB 12-05-1984, and drop-down menu 1 contains value "18", and drop-down menu 2 contains value "27".
As a result of this, the records returned, should be 12-05-1984, bu nothing gets displayed. Why is this? (Do I need to set the format of the date?)
PHP Code:<?php
require_once "init.php";
$from = mysql_real_escape_string($_POST['from']);
$to = mysql_real_escape_string($_POST['to']);
echo $from;
echo $to;
mysql_select_db($data, $con) or die (mysql_error());
$result = mysql_query("SELECT *
FROM member
WHERE DATEDIFF(CURRENT_DATE,DOB)
BETWEEN $from AND $to");
while($row = mysql_fetch_array($result))
{
echo $row['username']."<br />";
}
mysql_close($con)
?>
-
Jul 12, 2008, 11:18 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jul 12, 2008, 11:33 #14
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
The DOB Data Type is: Date
Don't know if this will also help, but the dates are in the following format: YYYY-MM-DD
-
Jul 12, 2008, 12:43 #15
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
please run this query and show me what you get:
Code:SELECT DATEDIFF(CURRENT_DATE,DOB) AS d FROM member LIMIT 9
-
Jul 12, 2008, 13:00 #16
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Nothing except the contents of the two drop-down menus (from & to)
I ran the following code, including your query, but nothing was displayed.
PHP Code:<?php
require_once "init.php";
$from = mysql_real_escape_string($_POST['from']);
$to = mysql_real_escape_string($_POST['to']);
echo $from;
echo $to;
mysql_select_db($data, $con) or die (mysql_error());
$result = mysql_query("SELECT DATEDIFF(CURRENT_DATE,DOB) AS d FROM member LIMIT 9");
while($row = mysql_fetch_array($result))
{
echo $row['Username']."<br />";
}
mysql_close($con)
?>
-
Jul 12, 2008, 13:30 #17
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
no, that's not what i meant
the query i asked you to run in post #15 should return only 1 column of data
please run it outside of php in a front-end app or the command line
-
Jul 12, 2008, 13:42 #18
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I ran it in MySQL. The following was returned/displayed:
d
14764
11543
21596
8945
218
10232
11983
10454
9583
-
Jul 12, 2008, 14:06 #19
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
do you see what this means? DATEDIFF produces the difference in days
14764 means the guy is about 40½ years old
so what would you do to change the WHERE clause to be able to use the dropdown values?
-
Jul 12, 2008, 14:14 #20
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
40 years old? - How did you work that out? (14764 / 365?)
I haven't a clue on what to change so that the WHERE clause includes the drop-down menus - This I defiantly need help on
-
Jul 12, 2008, 15:37 #21
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jul 12, 2008, 15:45 #22
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So what would the final code look like? So it only displays records between the ages set by the two drop-down menu values?
-
Jul 12, 2008, 15:50 #23
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
yes, why don't you try it, again outside of php, substituting numeric literals like 9 and 37 and similar for the two dropdown values
this experimentation will familiarize you with testing queries
it should further show you that your php code will also have to ensure that the 2nd value is not less that the 1st (note: it can be equal)
-
Jul 12, 2008, 16:35 #24
- Join Date
- Jun 2008
- Posts
- 279
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So I would enter the following outside php?: - Correct?
PHP Code:SELECT *
FROM member
WHERE DATEDIFF(CURRENT_DATE,DOB)/365
BETWEEN 18 AND 27
-
Jul 12, 2008, 16:37 #25
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
what happened when you tested that?
Bookmarks