Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-06-28
6
Medium Priority
?
614 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

604 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