Solved

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

Posted on 2009-06-28
6
581 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

770 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