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
Solved

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

Posted on 2009-06-28
6
584 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Very Large data in MYSQL 7 73
How to set the Tinymce Editor image path 4 23
Link failure 16 34
Inserting results of While loop to sql server 5 19
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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 …

829 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