Conditional output based on date

[MrCreeky]

Hi,

 

I'm trying to find a way to write a different output depending on how long something has been on the database for.

 

In the code below I'm trying to get the server to check the date stored in the db and if it's more than 7 days from todays date, it should output "hello world".

However it does not work and I have reached the end of my limited knowledge, can anyone give me some pointers?

 

mysql_select_db($database_encomSQL, $encomSQL);
$query_rs1 = "SELECT service_id, date_format(service_date,'%D %M %Y') AS 'fdate' FROM service ORDER BY service_id ASC";
$rs1 = mysql_query($query_rs1, $encomSQL) or die(mysql_error());
$row_rs1 = mysql_fetch_assoc($rs1);
$totalRows_rs1 = mysql_num_rows($rs1);


date_default_timezone_set('Europe/London');

$bookedin = $row_rs1['fdate'];
$date = date("D-M-Y");// current date

if ($date = strtotime($bookedin("D-M-Y", strtotime($date)) . " +7 day")) echo "hello world";

[teynon]

I'm not quite sure what you are trying to do with this line:

if ($date = strtotime($bookedin("D-M-Y", strtotime($date)) . " +7 day")) echo "hello world";

 

Maybe

if ($date == date("D-M-Y", strtotime($bookedin." +7 days"))) { echo "hello world"; }

[MrCreeky]

That no longer shows an error but it does not give an output either.

 

Is there a better way of achieving this result?

[PFMaBiSmAd]

Do you only want to retrieve data that is more than 7 days from todays date or do you want to retrieve all the data and do one thing if the data is more than 7 days from todays date and do something else if it is not?

[MrCreeky]

I would like to have 3 conditions, the first should check if the entry is more than 7 days old, the second is for 14 days and the third is 30+. Depending on the results it should output 3 different results that I can convert in to CCS formatting.

 

 

[PFMaBiSmAd]

7 days old

 

Old implies dates in the past. Do you actually want to test if something is in the future (greater than today's date) or in the past (less than today's date)?

 

If it seems like all I am doing is asking questions and not offering anything that helps, we cannot help in coding without an exact statement of the problem.

 

In any case, you can only do greater-than or less-than comparisons between textual dates when the format of the dates is something like date('Y-m-d') because the fields must be left to right, most significant part (year) to least significant part (day.)

 

Edit: If you need to display the dates using your "D-M-Y" format, continue to SELECT that using the date_format(), but for date manipulation in the php code, you should also SELECT the raw service_date column.

[MrCreeky]

That's ok I understand your questioning

 

I'm trying to a less than today's date query. Trying to highlight records that were made 7 days ago, 14 days ago and 30 days ago.

 

[PFMaBiSmAd]

<?php
mysql_select_db($database_encomSQL, $encomSQL);
$query_rs1 = "SELECT service_id, date_format(service_date,'%D %M %Y') AS 'fdate',service_date FROM service ORDER BY service_id ASC";
$rs1 = mysql_query($query_rs1, $encomSQL) or die(mysql_error());
$totalRows_rs1 = mysql_num_rows($rs1);
date_default_timezone_set('Europe/London');

$row_rs1 = mysql_fetch_assoc($rs1);

if($row_rs1['service_date'] < date('Y-m-d',strtotime('-30 day'))){
echo "More than 30 days ago";
} else if($row_rs1['service_date'] < date('Y-m-d',strtotime('-14 day'))){
echo "More than 14 days ago";
} else if($row_rs1['service_date'] < date('Y-m-d',strtotime('-7 day'))){
echo "More than 7 days ago";
} else {
echo "Within the past 7 days or in the future";
}
?>

[MrCreeky]

@PFMaBiSmAd Thanks that works!

 

I have one problem when implementing it on the page with a repeat region. Without adding the repeat region the page returns all the results on that table. When I add the condition it's limiting the output to just two results. I'm guessing it's because I'm calling this condition in-between the repeat region.

 

Here is the code on the page:

 

<?php do { ?>
<div style="background-color:#<?php
date_default_timezone_set('Europe/London');

$row_rs1 = mysql_fetch_assoc($rs1);

if($row_rs1['service_date'] < date('Y-m-d',strtotime('-30 day'))){
echo "FFB2B2";
} else if($row_rs1['service_date'] < date('Y-m-d',strtotime('-14 day'))){
echo "D1C1F0";
} else if($row_rs1['service_date'] < date('Y-m-d',strtotime('-7 day'))){
echo "C1E0FF";
} else {
echo "ffffff";
}
?>;"><?php echo $row_rs1['service_id']; ?>
</div>
<?php } while ($row_rs1 = mysql_fetch_assoc($rs1)); ?>

[PFMaBiSmAd]

A do/while loop is almost never used because it requires extra code to setup the data before the first pass through the loop. I'm not sure you are doing that before the start of the loop in the code you posted, but you ARE fetching a row from the result set INSIDE of the loop and also in the while() statement, thereby skipping over every other row.

 

When fetching multiple rows from a query, you should almost always use a while(){} loop -

 

<?php
mysql_select_db($database_encomSQL, $encomSQL);
$query_rs1 = "SELECT service_id, date_format(service_date,'%D %M %Y') AS 'fdate',service_date FROM service ORDER BY service_id ASC";
$rs1 = mysql_query($query_rs1, $encomSQL) or die(mysql_error());

$totalRows_rs1 = mysql_num_rows($rs1); // I'm going to assume that your actual code is using this value?

date_default_timezone_set('Europe/London'); // you would only do this ONCE, before the start of the loop

// code to do things before the start of the loop goes here...

while($row_rs1 = mysql_fetch_assoc($rs1)){

// code to do things for each row in the result set goes here...

if($row_rs1['service_date'] < date('Y-m-d',strtotime('-30 day'))){
	echo "More than 30 days ago";
} else if($row_rs1['service_date'] < date('Y-m-d',strtotime('-14 day'))){
	echo "More than 14 days ago";
} else if($row_rs1['service_date'] < date('Y-m-d',strtotime('-7 day'))){
	echo "More than 7 days ago";
} else {
	echo "Within the past 7 days or in the future";
}
}

// code to do things after the end of the loop goes here...

?>

[MrCreeky]

Here is the full code from the page so you can see the full story:

 

<?php require_once('Connections/encomSQL.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_encomSQL, $encomSQL);
$query_rs1 = "SELECT service_id, service_date FROM service ORDER BY service_id ASC";
$rs1 = mysql_query($query_rs1, $encomSQL) or die(mysql_error());
$row_rs1 = mysql_fetch_assoc($rs1);
$totalRows_rs1 = mysql_num_rows($rs1);
?>

<?php do { ?>
<div style="width:100px;padding:10px;text-align:center;background-color:#<?php
date_default_timezone_set('Europe/London');

$row_rs1 = mysql_fetch_assoc($rs1);

if($row_rs1['service_date'] < date('Y-m-d',strtotime('-30 day'))){
echo "FFB2B2";
} else if($row_rs1['service_date'] < date('Y-m-d',strtotime('-14 day'))){
echo "D1C1F0";
} else if($row_rs1['service_date'] < date('Y-m-d',strtotime('-7 day'))){
echo "C1E0FF";
} else {
echo "ffffff";
}
?>;"><?php echo $row_rs1['service_id']; ?>
</div>
<?php } while ($row_rs1 = mysql_fetch_assoc($rs1)); ?>

<?php
mysql_free_result($rs1);
?>

 

It's working from the standard Dreamweaver codes, I know that php purists might dislike their techniques however.

[MrCreeky]

Thinking about it, would it not be better to do something like this?

 

<style type="text/css">
.holder {background-color:#<?php
date_default_timezone_set('Europe/London');

if($repair_rs1['service_date'] < date('Y-m-d',strtotime('-30 day'))){
echo "FFB2B2";
} else if($repair_rs1['service_date'] < date('Y-m-d',strtotime('-14 day'))){
echo "D1C1F0";
} else if($repair_rs1['service_date'] < date('Y-m-d',strtotime('-7 day'))){
echo "C1E0FF";
} else {
echo "ffffff";
}
?>;
}
</style>


<?php do { ?>
<div class="holder">
hello world
</div>
<?php } while ($row_repair_rs1 = mysql_fetch_assoc($repair_rs1)); ?>

 

The only problem is that this returns on the "FFB2B2" formatting showing that the code is not getting past the first condition.