How do you calculate today's date - minus a db Date Entry?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do you calculate today's date - minus a db Date Entry?

Post by simonmlewis »

pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Code: Select all

 $today = (date('Y-m-d'));
  $regdate = strtotime("$row->datecustreturned");
  $regdatecomplete = date( "Y-m-d", ($regdate) );
  $subDateMicrotime = strtotime($regdatecomplete);
  $fortnight = date( "Y-m-d", ($subDateMicrotime + (86400*14)) );
 
  $result = ($today - $regdatecomplete);
We are trying to show how many days there is between the date of something in the database, and today's date.

ie. if the date returned was "01-09-2009" and today is 14-09-2009, it would result in "13".

Using this code, just produces errors about the database query, when in fact the db query is fine.


pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: How do you calculate today's date - minus a db Date Entry?

Post by pickle »

These don't hurt anything, but:
Line 2: Don't quote strtotime()'s argument
Line 3: Don't put parentheses around $regdate.

Your (as shown) code doesn't touch a database.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you calculate today's date - minus a db Date Entry?

Post by simonmlewis »

God knows why it's been edited.

This code WORKS on another site, but not here.

I have only taken the snippet that is wrong, hence the lack of DB connection.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: How do you calculate today's date - minus a db Date Entry?

Post by pickle »

The point is - if the error you're getting is about the query, then this code can't be causing the problem, as there's no query involved.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you calculate today's date - minus a db Date Entry?

Post by simonmlewis »

The query is fine. If I remove this code it works.

Something in this code is wrong.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: How do you calculate today's date - minus a db Date Entry?

Post by pickle »

Well then the error reporting must be wrong.

Sorry, but I just don't see how this code - which doesn't even touch the database - can be generating a legitimate error about a database query being wrong.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you calculate today's date - minus a db Date Entry?

Post by simonmlewis »

Ok - this IS touching a database, I just haven't put that in the code.

This is a SNIPPET.

Without this code, the query works fine. With the code, it fails.

Please stop saying it's not touching a database - IT IS.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: How do you calculate today's date - minus a db Date Entry?

Post by Mirge »

simonmlewis wrote:Ok - this IS touching a database, I just haven't put that in the code.

This is a SNIPPET.

Without this code, the query works fine. With the code, it fails.

Please stop saying it's not touching a database - IT IS.
Calm down, and post more then... including the query. And also, print out the actual query (including all variables' values in that query) and post that too.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you calculate today's date - minus a db Date Entry?

Post by simonmlewis »

You are asking me to calm down, when you are telling me there is no db connection. There is, I just don't see the need to put it in here, when the connection is fine, and the query is fine.

This is a new attempt at the query within the connection:

Code: Select all

echo "<td valign='top'>";
if ($row->received == NULL) { echo "$row->id";}
elseif ($row->received != NULL) {echo "<a href='index.php?page=returnid&id=$row->id&head=$row->website item returned'>$row->id [view]</a>";}
 
$datesubmitted = strtotime($row->received);
$addedDays = strtotime('+14 days', $datesubmitted);
 
$today = (date("Y-m-d"));
$thedate = strtotime($today);
$result = ($thedate - $addedDays);
 
if ($result > "0") { echo "Fail";}
elseif ($result <= "0") { echo "Success";}
 
echo "</td><td valign='top'>$row->itemname</td>
<td valign='top'>$row->firstname $row->lastname<br/>$row->orderid</td>
<td valign='top'>$row->website</td>
<td valign='top'>$row->datecustreturned</td>
<td valign='top'>";
 
 
It's the area in the middle from $datesubmitted to echo "Success";} that is erroring.

If I take it out, it works. If I leave it in, I get this:
Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /usr/local/psa/home/vhosts/shop.co.uk/httpdocs/support/includes/returnstst.inc on line 145

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /usr/local/psa/home/vhosts/lshop.co.uk/httpdocs/support/includes/returnstst.inc on line 200
Line 145 is this:

Code: Select all

while ($row = mysql_fetch_object($result))
 
Running this query to calculate days works on it's own. Just not here for some reason.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: How do you calculate today's date - minus a db Date Entry?

Post by Mirge »

And it'd be helpful to see all of the code to determine WHY. If you're not going to help us help you, then I'm not going to help period.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you calculate today's date - minus a db Date Entry?

Post by simonmlewis »

If you insist - here's the lot! I didn't sent it all before because it is very length and works perfectly.... without the erroneous code, so thought it irrelevant.

Hopefully a more polite reply will come.

Code: Select all

<img src='../images/icon_email.gif' /> = Email has been sent to customer.
<?php 
$received = $_POST['received'];
$update = $_POST['update'];
$id = $_POST['id'];
$cookieemail = $_COOKIE['email'];
$cookietype = $_COOKIE['type'];
if ($cookietype == "admin" || $cookietype == "user") {
include "dbconn.php";
 
if(isset($_REQUEST['websitesort']))
{
    $websitesort = $_REQUEST['websitesort'];
    $_SESSION['websitesort']=$websitesort;
}
else
{
    $websitesort = $_SESSION['websitesort'];
}
 
if ($websitesort == "clear") { $websitesort = NULL;}
 
if ($update == "delete") {
$query  = mysql_query ("DELETE from returns WHERE id = '$id'");
}
 
if ($received != NULL) {
$today = (date('Y-m-d'));
$query  = mysql_query ("UPDATE returns SET received = '$received', user = '$cookieemail', datereceived = '$today' WHERE id = '$id'");
 
$result = mysql_query ("SELECT * FROM returns WHERE id = '$id'");
while ($row = mysql_fetch_object($result))
      {      
        $to = "$row->email";
        $subject =  "$row->website Return: Your item has been received";
        $headers = "From: $row->supportemail";
        $body = "
Hello.  We have received your item in the post.
 
Your Returns Number is $row->id.
The product you have returned is the $row->itemname.
 
We will inspect the item and your reason for returning it back to us within the next few working days.
As soon as we have made a decision we will be in contact with you via email.
 
Best wishes,
Returns at $row->website";
        mail ($to, $subject, $body, $headers);
 }
    mysql_free_result($result);
  echo "<div class='admincompletedbox'>
        <b>Item marked as received.  Customer has been informed.</b>
        </div>";
        }
 
if ($cookietype == 'admin' ){
echo "
<table width='795' cellpadding='2' cellspacing='0'><tr><td>
<ul id='sddm'>
    <li><a href='#' 
        onmouseover=\"mopen('m1')\" 
        onmouseout=\"mclosetime()\">Select Web Site<img src='../images/arrow_down.gif' border='0' style='margin-left: 4px'/></a>
        <div id='m1' 
            onmouseover=\"mcancelclosetime()\" 
            onmouseout=\"mclosetime()\">";
            
            $result = mysql_query ("SELECT DISTINCT website FROM returns ORDER BY website ASC");
while ($row = mysql_fetch_object($result))
      {
            echo "
        <a href='index.php?page=returnsall&websitesort=$row->website&head=$row->website Returns'>$row->website</a>";
        }
    mysql_free_result($result);
            
echo "</div>
    </li>
</ul><a href='index.php?page=returnsall&websitesort=clear&head=All Returns'>Show all Returns</a>
<div style='clear:both'></div></td><td align='right'>
<form method='post' action='index.php?page=searchr&head=search result'><input type='text' name='search'><input type='submit' value='Search'></form></td></tr></table>";}
 
elseif ($cookietype == 'user' ){
echo "
<table width='795' cellpadding='2' cellspacing='0'><tr><td>
<ul id='sddm'>
    <li><a href='#' 
        onmouseover=\"mopen('m1')\" 
        onmouseout=\"mclosetime()\">Select Web Site<img src='../images/arrow_down.gif' border='0' style='margin-left: 4px'/></a>
        <div id='m1' 
            onmouseover=\"mcancelclosetime()\" 
            onmouseout=\"mclosetime()\">";
            
            $result = mysql_query ("SELECT DISTINCT website FROM returns WHERE website <> 'LILYBEADS' ORDER BY website ASC");
while ($row = mysql_fetch_object($result))
      {
            echo "
        <a href='index.php?page=returnsall&websitesort=$row->website&head=$row->website Returns'>$row->website</a>";
        }
    mysql_free_result($result);
            
echo "</div>
    </li>
</ul><a href='index.php?page=returnsall&websitesort=clear&head=All Returns'>Show all Returns</a>
<div style='clear:both'></div></td><td align='right'>
<form method='post' action='index.php?page=searchr&head=search result'><input type='text' name='search'><input type='submit' value='Search'></form></td></tr></table>";}
 
 
      // how many rows to show per page
$rowsPerPage = 20;
 
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;        
 
if ($websitesort == NULL && $cookietype == 'admin') {
$result = mysql_query ("SELECT * FROM returns WHERE dateactioned IS NULL ORDER BY id ASC LIMIT $offset, $rowsPerPage");}
elseif ($websitesort != NULL && $cookietype == 'admin') {
$result = mysql_query ("SELECT * FROM returns WHERE website = '$websitesort' AND dateactioned IS NULL ORDER BY id ASC LIMIT $offset, $rowsPerPage");}
 
if ($websitesort == NULL && $cookietype == 'user') {
$result = mysql_query ("SELECT * FROM returns WHERE dateactioned IS NULL AND website <> 'site1' AND website <> 'site2' ORDER BY id ASC LIMIT $offset, $rowsPerPage");}
elseif ($websitesort != NULL && $cookietype == 'user') {
$result = mysql_query ("SELECT * FROM returns WHERE website = '$websitesort' AND dateactioned IS NULL AND website <> 'site1'  AND website <> 'site2' ORDER BY id ASC LIMIT $offset, $rowsPerPage");}
 
echo "<table width='790' cellspacing='0' cellpadding='0' class='table'><tr bgcolor='#cccccc'>";
 
if ($cookietype == 'admin') { echo "<td valign='top'></td>";}
 
echo "
<td valign='top'></td>
<td valign='top'>Rtn ID</td>
<td valign='top'>Item Returned</td>
<td valign='top'>Name/OrderID</td>
<td valign='top'>Website</td>
<td valign='top' width='90'>Date submitted</td>
<td valign='top' width='90'>Date actioned</td>
<td valign='top' width='90'>Date Rcvd</td></tr>";
while ($row = mysql_fetch_object($result))
      {
            echo "<tr><td valign='top'>";
if ($row->emailsent == 'yes') { echo "<img src='../images/icon_email.gif' style='padding-right: 5px; padding-top: 3px' alt='An email has been sent to this customer' />";}
echo "</td>";
            
if ($cookietype == 'admin') { echo "<td valign='top'><a href='index.php?page=returnid&id=$row->id&head=$row->website item returned'>Open</a></td>";}
 
echo "<td valign='top'>";
if ($row->received == NULL) { echo "$row->id";}
elseif ($row->received != NULL) {echo "<a href='index.php?page=returnid&id=$row->id&head=$row->website item returned'>$row->id [view]</a>";}
 
$datesubmitted = strtotime($row->received);
$today = (date("Y-m-d");
$thedate = strtotime($today);
$resulta = ($thedate - $datesubmitted);
$resultfinal = (date($resultfinal));
echo "$resultfinal";
 
echo "</td><td valign='top'>$row->itemname</td>
<td valign='top'>$row->firstname $row->lastname<br/>$row->orderid</td>
<td valign='top'>$row->website</td>
<td valign='top'>$row->datecustreturned</td>
<td valign='top'>";
 
if ($row->dateactioned == NULL) { echo "Not actioned";}
elseif ($row->dateactioned != NULL) { echo "$row->dateactioned<br/>$row->result";}
echo "</td>
<td valign='top'>";
 
if ($row->received == NULL) { 
echo "<form method='post' action='index.php?page=returnsall&head=all returns'>
<input type='hidden' name='id' value='$row->id'>
<input type='hidden' name='received' value='yes'>
<input type='submit' value='Received' style='width: 80px'>
</form>
<form method='post' action='index.php?page=returnsall&head=all returns'>
<input type='hidden' name='id' value='$row->id'>
<input type='hidden' name='update' value='delete'>
<input type='submit' value='Junk/Delete' style='width: 80px'  onclick=\"javascript&#058;return confirm('Are you sure you wish to delete this item before opening it?');\">
</form>";
}
elseif ($row->received == "yes") { 
echo "$row->datereceived";
}
 
echo "</td></tr>";
 
if ($cookietype == 'admin') { echo "<tr><td colspan='9'><hr noshade size='1' color='#cccccc' /></td></tr>";}
elseif ($cookietype == 'user') { echo "<tr><td colspan='8'><hr noshade size='1' color='#cccccc' /></td></tr>";}
   }
 
    mysql_free_result($result);
 
    echo "</table>";
 
if ($websitesort == NULL && $cookietype == 'admin') {
$query   = "SELECT COUNT(id) AS numrows FROM returns WHERE dateactioned IS NULL";
}
if ($websitesort != NULL && $cookietype == 'admin') {
$query   = "SELECT COUNT(id) AS numrows FROM returns WHERE website = '$websitesort' AND dateactioned IS NULL";
}
if ($websitesort == NULL && $cookietype == 'user') {
$query   = "SELECT COUNT(id) AS numrows FROM returns WHERE dateactioned IS NULL AND website <> 'site2' AND website <> 'site1'";
}
if ($websitesort != NULL && $cookietype == 'user') {
$query   = "SELECT COUNT(id) AS numrows FROM returns WHERE website = '$websitesort' AND dateactioned IS NULL AND website <> 'site1' AND website <> 'site2'";
}
$result  = mysql_query($query) or die(mysql_error());
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
    // how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"index.php?page=returnsall&head=all returns&pagenum=$page\" class='bodylink'>$page</a>";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"index.php?page=returnsall&head=all returns&pagenum=$page\" class='bodylink'>[Prev]</a> ";
 
   $first = " <a href=\"index.php?page=returnsall&head=all returns&pagenum=1\" class='bodylink'>[First Page]</a>";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"index.php?page=returnsall&head=all returns&pagenum=$page\" class='bodylink'>[Next]</a>";
 
   $last = " <a href=\"index.php?page=returnsall&head=all returns&pagenum=$maxPage\" class='bodylink'>[Last Page]</a>";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo "<div class='navpages'>" . $first . $prev . $nav . $next . $last . "</div>";
    mysql_free_result($result);
    mysql_close($sqlconn);
}
    else
    {
    echo "<meta http-equiv='Refresh' content='0 ;URL=index.php?page=login&head=Login'>";
    }
?>
 
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: How do you calculate today's date - minus a db Date Entry?

Post by Mirge »

After each of your mysql_query() calls, put or die(mysql_error()). See which is not working if that's the case... as you should have been doing from the get-go.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you calculate today's date - minus a db Date Entry?

Post by simonmlewis »

But they are ALL working.
It's only with that additional query I have posted, that it's failing.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: How do you calculate today's date - minus a db Date Entry?

Post by Mirge »

So they show nothing after you put "or die(mysql_error());" after them? After ALL of your mysql_query() calls?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you calculate today's date - minus a db Date Entry?

Post by simonmlewis »

I have not put that in, because I don't see it being needed since without the code it works fine - there are no errors.

All the queries are SELECT ALL, so it's not even going to be a lack of data producing the error.

I don't understand why you'd need to see errors when without the other code (which is questioning JUST ONE field that is being rendered) it is fine.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply