[SOLVED] how do I show if something is new?

[acidglitter]

I want to have the words NEW next to any tutorial on my site thats been there for 1 week or less. In the table, the date of the tutorial is saved as DATE. How can I show which tutorials are new?

[Wuhtzu]

You might have to joggle around a bit with your date, since it's easiest to work with timestamps, but when you have your date (http://no2.php.net/manual/en/function.strtotime.php) as a timestamp you simply do something like this:

 

<?php

while($row = mysql_fetch_array($query)) {

    //Convert your date (yyyy-mm-dd) into timestamp if nessasary
    //This of course comes from $row['date'] or a conversion of it
    $tutorial_date = "some timestamp";
     

    $time_diff = time() - $tutorial_date;

    //Check if it's less than a week since creation
    if($time_diff < 7*24*60*60) {
        echo "NEW " . $row['title'];
    }
    else {
        echo $row['title'];
    }
    
}

?>

[roopurt18]

Don't do this with PHP at all; use MySQL to do the work for you.  Assuming the MySQL records have a DATETIME column named entered_dt:

 

SELECT *,
  IF(
    NOW() - INTERVAL 1 WEEK < entered_dt,
    1, 0
  ) AS `NewPost`
FROM <table_name> WHERE <where_condition>

 

This will return a column named 'NewPost' that is a 1 if the post is new, 0 if it is old.  This reduces your PHP code to:

 

<?php
  $q = mysql_query($sql);
  if($q){
    while($row = mysql_fetch_assoc($q)){
      if($row["NewPost"] == 1){
        // New post
      }else{
        // Old post
      }
    }
  }
?>

 

I wish people would stop recommending to use PHP's date / time functions on data that is coming from MySQL.

[Wuhtzu]

Okay you win roopurt18 It's smarter to use SQL here. But sometimes it's smartest to store the timestamp and use that to calculate stuff in PHP. And yes I know so little about SQL that I store all my dates as timestamps.

[acidglitter]

what you put is way beyond me but i'm trying to understand it. i've put this as the new query

 

$q=mysql_query("SELECT date,IF(NOW() - INTERVAL 1 WEEK < date, 1, 0) AS `NewPost` FROM tutorials WHERE type='tutorial'") or die(mysql_error());

 

and got this 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 'WEEK < date, 1, 0) AS `NewPost` FROM tutorials WHERE type='tutorial'' at line 1

[roopurt18]

Hmmm.

 

The syntax should be ok, I ran this and came up with results:

SELECT NOW( ) , NOW( ) - INTERVAL 1 WEEK, IF( NOW( ) - INTERVAL 1 WEEK < NOW( ) , 1, 0 ) AS date
FROM Asset
WHERE 1
LIMIT 1 

 

I can only think of two things.

 

1)  date is a MySQL keyword, try enclosing it in backticks

 

2)  Your version of MySQL doesn't support this feature (would seem unlikely)

[acidglitter]

I think I must have messed it up when I changed it but I don't know where.

[roopurt18]

You might try putting NOW() - INTERVAL 1 WEEK inside of parens.

[acidglitter]

okay i changed it to 7 DAY instead of 1 WEEK so that part works. this is all of it right now.

 

$msql=mysql_query("SELECT id,title,video FROM tutorials WHERE type='tutorial' ORDER BY title ASC");
while($mrow=mysql_fetch_array($msql)){
echo "<a href=\"temp.php?tid={$mrow['id']}\">{$mrow['title']}</a>";
if($mrow['video']==1){
	echo " (video)";
}
$q=mysql_query("SELECT date,IF((NOW() - INTERVAL 7 DAY) < date, 1, 0) AS `NewPost` FROM tutorials WHERE type='tutorial'") or die(mysql_error());

    while($row = mysql_fetch_assoc($q)){
      if($row["NewPost"] == 1){
        echo "1";
      }else{
       echo "2";
      }
    }
    echo "<br />\n";
}

 

and after every tutorial, no matter how new, it puts 211... ???

[roopurt18]

Add this to your loop:

 

echo "<pre style=\"text-align: left;\">" . print_r($row, true) . "</pre>";

 

 

[acidglitter]

Okay thanks! I was able to figure it out with that

 

This is the new code. I realized the problem was I was selecting that entire section of the table even though it was already inside a loop. So I changed the WHERE to the tutorials specific id and that fixed it

$msql=mysql_query("SELECT id,title,video FROM tutorials WHERE type='tutorial' ORDER BY title ASC");
while($mrow=mysql_fetch_array($msql)){
echo "<a href=\"tutorials.php?tid={$mrow['id']}\">{$mrow['title']}</a>";
if($mrow['video']==1){
	echo " (video)";
}
$thetutid=$mrow['id'];
$q=mysql_query("SELECT date,IF((NOW() - INTERVAL 7 DAY) < date, 1, 0) AS `NewPost` FROM tutorials WHERE id='$thetutid'");
while($row = mysql_fetch_assoc($q)){
	if($row["NewPost"]== 1){
		echo " new!";
	}
}
echo "<br />\n";
}

 

Thanks for the help!

[roopurt18]

That echo statement I gave you is your best friend when debugging.  Program not working?  Dump the data you have, compare it with the data you should have, fix and repeat as necessary.

 

Glad you got it resolved!