Solved

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

Posted on 2009-06-28
6
576 Views
Last Modified: 2013-12-13
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

0
Comment
Question by:tonypearce
  • 4
6 Comments
 

Author Comment

by:tonypearce
ID: 24730710
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
 
LVL 17

Accepted Solution

by:
pssandhu earned 500 total points
ID: 24730716
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
 

Author Comment

by:tonypearce
ID: 24730721
Hi,
I don't have an existing date to work from, so I need to create it from scratch..

Thanks for your response
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:tonypearce
ID: 24730734
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
 
LVL 5

Expert Comment

by:solutionDriver
ID: 24730737
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
 

Author Closing Comment

by:tonypearce
ID: 31597666
Thanks for your input, I actually did it myself.. WooHoo
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now