[Resolved] using if

[slimjim]

In my database table i am using the date 0000-00-00
When a person submits a link the current date gets inserted with the date below.
But i also want to show on a page a graphic saying NEW if the date is less then a month old, and if the date is older then one month I want to show a different graphic.
Not really sure on how to do this.

Any help would be great....

$date=date("Y-m-d");

[drawmack]

<?php
$ts = strtotime('-1 month');
$epoch = date('Y-m-d',$ts);
?>

[iceraider]

Ok then what you do is

Query: SELECT (month-INTERVAL 1 MONTH)-(NOW()-INTERVAL 1 MONTH) as current FROM table WHERE conditions

then u get your result

if($result['current'] > 0)
{
        // NEW
}
else
{
        // OLD
}

[slimjim]

Here is my original query below, now would the query you mentioned go above the one I am using now to display results or do i just modifiy the one I am using.

<?PHP
$query3="SELECT * FROM subcat where categoryg='$cate' and subcat='$categoryb' and status='1' ORDER BY hits DESC";
	$result3 = mysql_query($query3) or die( "ERROR: " . mysql_error() . "\n");
		$j=1;
	$k=1;	

$num_products = mysql_num_rows($result3);	

while ($myrow = mysql_fetch_array($result3)) {	    

   if($k<=$from*$record_per_page and $k>($from-1)*$record_per_page){		
	$subcatid=$myrow["subcatid"];
	$categoryg=$myrow["categoryg"];
    $subcat=$myrow["subcat"];
    $website=$myrow["website"];
    $adate=$myrow["adate"];
    $hits=$myrow["hits"];
    $status=$myrow["status"];
  ?>
Then of course this is where i would ave html and echo the recults....
	  <?

}
	     $k++;
$j++;
	  	};
    ?>

Also i display the results like this..

Updated: <? echo $adate;?>

adate is the date the link was added.

But of course now I would use the if statement that you mentioned to also show the graphic.

But unsure on where and how to add what you mentioned.

[slimjim]

Can anyone help me out with this please.

[Roger_Ramjet]

Several ways to do this depending on your requirements. See here: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html for a full list of date functions.

My preferance would be for an expression that returns true or false as this would make the if statement very simple ie

SELECT adate, DATE_ADD(adate, INTERVAL 1 MONTH) > CURDATE() AS is_new, .... + the rest of your fields and where etc

$is_new = $myrow['is_new']
if $is_new { ..... } else { .... }

This will add 1 month to your date, compare it to today's date and return true if the result is greater, false if equal to or less.

You could also use DATE_SUB (subtracts the interval) or DATEDIFF (returns the no of days between two dates as an integer). Use whichever you find easiest to work with.

[slimjim]

But I also want to select all * and it seems with your code I would be only selecting adate, or am i not understanding this.

Here is my original select :

$query3="SELECT * FROM subcat where categoryg='$cate' and subcat='$categoryb' and status='1' ORDER BY hits DESC"; 

Originally posted by Roger Ramjet
Several ways to do this depending on your requirements. See here: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html for a full list of date functions.

My preferance would be for an expression that returns true or false as this would make the if statement very simple ie

SELECT adate, DATE_ADD(adate, INTERVAL 1 MONTH) > CURDATE() AS is_new, .... + the rest of your fields and where etc

$is_new = $myrow['is_new']
if $is_new { ..... } else { .... }

This will add 1 month to your date, compare it to today's date and return true if the result is greater, false if equal to or less.

You could also use DATE_SUB (subtracts the interval) or DATEDIFF (returns the no of days between two dates as an integer). Use whichever you find easiest to work with.

[slimjim]

You mentioned this also :
if $is_new { ..... } else { .... }

if $is_new ( what ? )

Not sure what to do here , I mean is it greater then something or less then something, I am stumped.

[slimjim]

Ok i figured it out. All I had to do is read more on your post. I add the other colums in the q and added my where info to it and then the row info and then added the if on the page for a test and changed the date a few times in the database and it worked perfect.
Working with dates it seems is tough and confusing cause i spent all day long trying to figure out dates and no such luck, Based on your code though i was really close at one point.

Thanks Roger and everyone else. I will keep reading and practicing dates to get better at it.

[Roger_Ramjet]

Glad I could help. Just keep playing around with DATE_ADD and DATEDIFF until you are confident with them. They are invaluable in database work.