MySQL create new column in Select to show date plus 7 days

Hi,
I have a select as code view (not working!!)  to create a new result that is 7 days in advance of the current date, the format must be dd/mm/yyyy (UK style)

When using PHP to show the results it's easier :
<?php
$expiry = mktime(0, 0, 0, date("m"), date("d")+7, date("y"));
echo "".date("d/m/Y", $expiry);
?>

But I have to export directly from a page to XML so I have a choice:
!. create a SELECT to give me this date
or
2. Integrate the above in the the second code that produces the XML (see code view)
SELECT
DATE AS expiry_date
 
PHP:
 
    $expiration_date = $item->appendChild($dom->createElement('g:expiration_date')); 
 $expiration_date->appendChild($dom->createTextNode('CODE HERE???????')); 
}

Open in new window

Tony PearceAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pssandhuConnect With a Mentor Commented:
I am not sure if this going to help you and I do not know php, so this how you will do it MySQL in a Select statement:
SELECT ADDDATE(Date, 7) from expiry_date   <-- Added 7 days
P.
0
 
Tony PearceAuthor Commented:
The code below now works and shows the Epoch date? like:

expiration_date>1246748400</g:expiration_date>
$days7 = mktime(0, 0, 0, date("m"), date("d")+7, date("y"));
     $expiration_date = $item->appendChild($dom->createElement('g:expiration_date')); 
 $expiration_date->appendChild($dom->createTextNode($days7));

Open in new window

0
 
Tony PearceAuthor Commented:
Hi,
I don't have an existing date to work from, so I need to create it from scratch..

Thanks for your response
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Tony PearceAuthor Commented:
This works, thie first time I have worked something out for myself......................
 $tomorrow = mktime(0, 0, 0, date("m"), date("d")+7, date("y"));
     $expiration_date = $item->appendChild($dom->createElement('g:expiration_date')); 
 $expiration_date->appendChild($dom->createTextNode(date("d/m/Y", $tomorrow)));

Open in new window

0
 
solutionDriverCommented:
Hi tonypearce,

you can do the date advancing and formatting purely in mySQL, like this:

SELECT DATE_FORMAT( DATE_ADD( <datefield>, INTERVAL 1 WEEK ) , '%d/%m/%Y' )
FROM <database>
WHERE <condition>

It works with mySQL fieldtypes DATE, TIME, and TIMESTAMP for <datefield>.

Instead of 'INTERVAL 1 WEEK ' you could also use 'INTERVAL 7 DAY'.

Enjoy,

  sd
0
 
Tony PearceAuthor Commented:
Thanks for your input, I actually did it myself.. WooHoo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.