[RESOLVED] Help with date please

ali_p

Hi I have a database of form request which are shown on my website. There is a submit date that gets entered into the table called 'subdate'.

Now the requests are visible on my site for maximum 180 days from the 'subdate'
So in order to show only the requests that are not out of date I need to say show only the requests that are less than 180 days added to the subdate.

But I'm not too sure how to write this without going round in circles.

Installer

$before = strtotime($subdate);
$today = strtotime('today');
$days = 0;
while (($today = strtotime('-1 day', $today)) > $before) {
    $days++;
}
echo $days . '<br />';

scrupul0us

Installer;10904157 wrote:

$before = strtotime($subdate);
$today = strtotime('today');
$days = 0;
while (($today = strtotime('-1 day', $today)) > $before) {
    $days++;
}
echo $days . '<br />';

wouldnt it be prudent to do the date stuff on the mysql server during the query? no sense in returning results you arent going to use

Installer

Yes, it would. I guess that was likely the real question, and that I glassed over the database part.

ali - This can be done in the query using DATEDIFF.

scrupul0us

Installer;10904163 wrote:
Yes, it would. I guess that was likely the real question, and that I glassed over the database part.

ali - This can be done in the query using DATEDIFF.

yea i was googling to find an example of this for him but its rare I mess with DB functions that my googling for such was rusty 😉

ali_p

Thank you both, I was hoping to so this in the query so that datediff looks like it might do the trick. I'll give it a go and see how I get on.

Thanks again

Installer

Something like this, I reckon:

SELECT * FROM mytable WHERE DATEDIFF(CURDATE, subdate) < 180

Edit: ali - I didn't see your last post before posting this. Good luck.

ali_p

Okay this is the query that I have now

$getcountries="
SELECT  country, subdate, approved
FROM youth
WHERE approved = 'yes'
AND DATEDIFF(subdate,subdate+180) < $todaydate
GROUP BY country
";

But its not right. I only want results that are not not over 180 days from the subdate....
oh I'm getting quite confused

scrupul0us

$getcountries="
SELECT  country, subdate, approved
FROM youth
WHERE approved = 'yes'
AND DATEDIFF(CURDATE, subdate) < 180
GROUP BY country
";

the combined query you had used the code pre-query based date computation... try what ive posted here

ali_p

Hi, The CURDATE is throwing me an error

Unknown column 'CURDATE' in 'where clause'

Installer

It also wouldn't hurt to read the manual page I linked to earlier (or the sample query I posted after that).

Edit: Our error. Use CURDATE().

scrupul0us

try putting () after curdate..

$getcountries="
SELECT  country, subdate, approved
FROM youth
WHERE approved = 'yes'
AND DATEDIFF(CURDATE(), subdate) < 180
GROUP BY country
";

ali_p

My mistake I forgot the () after CURDATE

Looks like its working now;

$getcountries="
SELECT  country, subdate, approved
FROM youth
WHERE approved = 'yes'
AND DATEDIFF(CURDATE, subdate) < 180
GROUP BY country
";

That's a good one thank you all