Show the visitors which category has new ads

[pat01]

If a visitor hits the "New Listings" link, he'll get a list of the latest ads from all categories. I think it would be very helpfull for the users if they can see right away which category has new ads.

On the index.php, all the categories and subcategories are displayed. How about if behind the name of each category which has new ads a "NEW" sign would be displayed? The "NEW" can be as text or an image.

I did try it but now I'm stuck...

I don't know how to calculate the date and time correctely and how to assign these information to each category. Maybe somebody has a solution for this?

And here is what I did in index.php:

PHP Code:

# START FUNCTION SHOW NEW 
$sSQL3 ="SELECT id, dateadded FROM ".PREFIX."products WHERE id=".$rs['id'];
        $result3=$db->query($sSQL3);
        $newads=$result3->fetch();

$new="NEW ads!";

# $timeframe = "24";  // Not used yet, this value tells for how many hours an ad is 'new' = from now minus $new the $newmessage is shown behind the category name. For testing I use the -1 below.

$now = mktime(0,0,0,date("m"),date("d")-1,date("Y"));
$adsdate = $newads['dateadded'];
$strip = substr($adsdate,0,10);

if($strip > $now) {
$newmessage = $new;
}
else
                {
$newmessage = "";
                }

# END FUNCTION SHOW NEW 


And I added '.$newmessage.'

to
$add_cats.='<a href="category.php?type='.$rs['id'].'&sec='.$arow['id'].'">'.$arow['name'].'</a> ';


What do you think about this feature?
Any help would be appreciated, thank you very much!

[Eric Barnes]

I think you are on the right track but I would do it a different way.

Since on the index page you already know the category you could change your query to something like this:

PHP Code:

$yesterday = date('Y-m-d', mktime(0, 0, 0, date("Y") , date("m"), date("d") - 1));
$sSQL="SELECT id FROM ".PREFIX."products WHERE section=".$sectionid." AND dateadded >= '".$yesterday."' AND active='Y'"; 


[pat01]

No matter what I do, I get the following error:

Notice: Query failed: 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 'AND dateadded >= '1969-12-31' AND active='Y'' at line 1 SQL: SELECT id FROM class_products WHERE section= AND dateadded >= '1969-12-31' AND active='Y'

Any idea? OK, the section= is empty... is that supposed to be like that?

[juven14]

Should $sectionid be $section?

Maybe arow['id']?

[pat01]

I guess it has more likely to do with the date:
AND dateadded >= '1969-12-31' AND active='Y

I'm wondering about 1969-12-31 ?

EDIT: I changed the $yesterday to:
$yesterday = date('Y-m-d', mktime(0,0,0,date("m"),date("d")-1,date("Y")));

Now I get the correct date, but still a SQL error. I guess there is a typo in the query?

EDIT2: Guess I found it. Changed the query to
$sSQL="SELECT id FROM ".PREFIX."products WHERE section='".$sectionid."' AND dateadded >= '".$yesterday."' AND display='Y'";

At least no more SQL errors

[pat01]

Originally Posted by juven14

Maybe arow['id']?

That did the trick, thanks!

Finally, it works:

PHP Code:

$yesterday = date('Y-m-d', mktime(0,0,0,date("m"),date("d")-1,date("Y")));
$sSQL="SELECT id FROM ".PREFIX."products WHERE section='".$arow['id']."' AND dateadded >= '".$yesterday."' AND display='Y'"; 
        $result3=$db->query($sSQL);
        $newads=$result3->fetch();
        $new="NEW ads!";
if($newads > 0) {
    $newmessage = $new;
}
else    {
    $newmessage="";
} 


The only problem is, that I can define to show the $newmessage in days only, not in hours. I can't define show me the $newmessage if an ad is newer than 5 hours. At least, the above function is a start...

Maybe somebody has an idea how to improve this?

[pat01]

OK, here it is:

PHP Code:

$timeframe = "12"; # Max. age of a new ad in hours

$timecalc = date("Y-m-d H:i:s", time()-(($timeframe)*(3600)));

$sSQL="SELECT id,dateadded FROM ".PREFIX."products WHERE section='".$arow['id']."' AND dateadded >= '".$timecalc."' AND display='Y'"; 
        $result3=$db->query($sSQL);
        $newads=$result3->fetch();

$new="NEW ads!";

if($newads > 0) {
    $newmessage = $new;
}
else    {
    $newmessage="";
} 


I'm still testing it, but so far it looks good.

Maybe somebody can tell me how to integrate this function into the category.php with subcategories.tpl.php and showlistings.tpl.php? They have a different code, I guess I must write something like [[newmessage]] into subcategories.tpl.php and {$newmessage} into showlistings.tpl.php? But what needs to be changed in the function itself?