Hello,
I have this code in my index.php:
<?php
unset($featured);
$seed = intval(date("Ymd"));
$sql = "SELECT name FROM `".$config_databaseTablePrefix."products` ORDER BY RAND(".$seed.") LIMIT 10";
if (database_querySelect($sql,$rows))
{
$sqlNames = array();
foreach($rows as $featured)
{
$sqlNames[] = "'".$featured["name"]."'";
}
$sqlIn = implode(",",$sqlNames);
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name IN (".$sqlIn.") GROUP BY name";
database_querySelect($sql,$rows);
$featured["products"] = $rows;
foreach($featured["products"] as $k => $product)
{
if ($config_useRewrite)
{
$featured["products"][$k]["productHREF"] = "product/".tapestry_hyphenate($product["name"]).".html";
$featured["products"][$k]["reviewHREF"] = "review/".tapestry_hyphenate($product["name"]).".html";
}
else
{
$featured["products"][$k]["productHREF"] = "products.php?q=".urlencode($product["name"]);
$featured["products"][$k]["reviewHREF"] = "reviews.php?q=".urlencode($product["name"]);
}
}
}
?>
I have added a new field. All things are working ok.
In the sql the PUBDATE = Wed, 24 Jan 2007 15:44:02 +0000
How to sort by date?
I have replaced "RAND(".$seed.")" with "ORDER BY PUBDATE DESC LIMIT 10" but it`s not working. I think i can`t sort because of pubdate.
Thanks,
Gabriel
Hi Gabriel,
This is probably because the pubdate is a text field (VARCHAR) in the database and not a native "date" data type. What I would do is add another field to your products table of type INT(11) (select type INT and width 11) called pubdate_time.
Then, in includes/admin.php within the admin__importRecordHandler() function, modify the SQL to set the new field pubdate_time with a modification similar to the following:
$sql = sprintf("INSERT INTO `".$config_databaseTablePrefix."products` SETmerchant='%s',
name='%s',
description='%s',
image_url='%s',
buy_url='%s',
price='%s',
search_name='%s',
category='%s',
brand='%s',
dupe_hash='%s'
pubdate='%s',
pubdate_time='%s'
",
database_safe($admin_importFeed["merchant"]),
database_safe($record[$admin_importFeed["field_name"]]),
database_safe(isset($record[$admin_importFeed["field_description"]])?$record[$admin_importFeed["field_description"]]:""),
database_safe(isset($record[$admin_importFeed["field_image_url"]])?$record[$admin_importFeed["field_image_url"]]:""),
database_safe($record[$admin_importFeed["field_buy_url"]]),
database_safe($record[$admin_importFeed["field_price"]]),
database_safe($searchName),
database_safe($category),
database_safe($brand),
$dupe_hash,
database_safe($record[$admin_importFeed["field_pubdate"]]),
strtotime($record[$admin_importFeed["field_pubdate"]])
);
As you can see, that sets pubdate_time to the return value of the strtotime() function against the pubdate field. Just copy the extra lines from the code above into your existing version, making sure you have added commas where needed.
Finally, in your SQL you should then be able to use "ORDER BY pubdate_time DESC LIMIT 10"...
Hope this helps,
Cheers,
David.