Countdown from Current date to Recordset Date

[drax007]

Hi there,

I was hoping for a little help please...

I want to calcuate the number of days between the current date and a date found in a field in my database table.

So pefectly illustrated is with this Mysql code:

SELECT DATEDIFF (CURDATE(), SowDate) AS intval FROM `vegeschedule` 

Using the above code works a charm in phpmyadmin but i realise the tricky bit is to do it with php.

So far i have found a basic coutdown script that counts down from the current date to a manually inputted date....I need to to use the current date and a date in a field in my table.

Heres the coutdown script:

<?php // Define date format
$dateFormat = "Y-m-d H:i:s";

$targetdateDisplay = date($dateFormat,$targetdate);
$actualdateDisplay = date($dateFormat,$actualdate);
$result =mysql_query($sql);
$row = mysql_fetch_array($result);
// Define your target date here

$targetYear = $row['year'];
$targetMonth = $row['month'];
$targetDay = $row['days'];
$targetHour = 12;
$targetMinute = 00;
$targetSecond = 00;
// End target date definition

$targetdate = mktime($targetHour,$targetMinute,$targetSecond,$targetMonth,$targetDay,$targetYear);
$actualdate = time();

$secondsDiff = $targetdate - $actualdate;

$remainingDay = floor($secondsDiff/60/60/24);
$remainingHour = floor(($secondsDiff-($remainingDay*60*60*24))/60/60);
$remainingMinutes = floor(($secondsDiff-($remainingDay*60*60*24)-($remainingHour*60*60))/60);
$remainingSeconds = floor(($secondsDiff-($remainingDay*60*60*24)-($remainingHour*60*60))-($remainingMinutes*60));

// Define date format
$dateFormat = "Y-m-d H:i:s";

$targetdateDisplay = date($dateFormat,$targetdate);
$actualdateDisplay = date($dateFormat,$actualdate);

?>
<br />
<font size="+1" color="#FF0000">Expires In: <?php echo "$remainingDay days";?> </font> </p> 

Please NOTE the target date hasn't been set yet.....

I tried to add my php query to get the date value from the database (below) but just dont know what the hell to do now....

mysql_select_db($database_connect, $connect);
$sql = "SELECT ''SowDate' FROM vegeschedule";

If anybody could please help me that would be great...I have spent nearly ALL day and night yesterday trying to get working....

Thank You 🙂

[bpat1434]

What's wrong with running the first query in your mysql_query() call? Then the result will be the difference and you can start subtracting time periods from it.

$sql = "SELECT DATEDIFF((CURDATE(), SowDate) AS intval FROM `vegeschedule`";
$result = mysql_query($sql) or die('MySQL Error: '.mysql_error());
$row = mysql_fetch_assoc($result);

echo 'Expires in: '.$row['intval'].' days';

[drax007]

Thanks for reply,

I tried for hours to somehow use the mqsql code to work it out... but concluded it o****ed be done...If it can it would be far easier to use your example....

when i try i get: MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM vegeschedule' at line 1.

What am i missing?

I am fairly new to php but am trying 🙂

Thanks

[drax007]

I found an answer....Wohoo! just incase anybody else wants to calculate the days between the current date and a database retrieved date then this code works fine.

#<?php

$conn = mysql_connect("localhost", "root", "");

if (!$conn) {
echo "Unable to connect to DB: " . mysql_error();
exit;
}

if (!mysql_select_db("greenlife")) {
echo "Unable to select greenlife: " . mysql_error();
exit;
}
//May want to add a WHERE here
$sql = "SELECT DATEDIFF (CURDATE(), HarvestDate) AS intval FROM vegeschedule ";
$result = mysql_query($sql);
if (!$result) {
echo "Could not successfully run query ($sql) from DB: " . mysql_error();
exit;
}

if (mysql_num_rows($result) == 0) {
echo "No rows found, nothing to print so am exiting";
exit;
}
while ($row = mysql_fetch_assoc($result)) {
echo $row["intval"]."<br />\n";
}

mysql_free_result($result);

?>#

Thanks 🙂

[bpat1434]

Don't forget to mark this thread as resolved if it is.