Hi David,
I've a date field in my database and I'm looking to sort products by date.
Any ideas?
Thanks
Adrian
Hi David,
Thanks for your response on this and apologies on the delay in coming back to you!
Format of field is varchar(255) latin1_swedish_ci
Example Field values
03-SEP-08
29-JAN-08
Many thanks
Adrian
Hi Adrian,
Firstly to convert the field to an integer so that it can be sorted, run the following dbmod.php script in the Price Tapestry installation folder:
<?php
require("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
CHANGE `date` `date` INT(11)";
database_queryModify($sql,$result);
print "Done.";
?>
Next, create a new filter in the Price Tapestry file includes/filter.php as follows:
/*************************************************/
/* strToTime */
/*************************************************/
$filter_names["strToTime"] = "strToTime";
function filter_strToTimeConfigure($filter_data)
{
print "<p>There are no additional configuration parameters for this filter.</p>";
}
function filter_strToTimeValidate($filter_data)
{
}
function filter_strToTimeExec($filter_data,$text)
{
return strtotime($text);
}And then for any feeds that have a date field (or as a global filter) add a new strToTime filter to the date field so that it is converted, and re-import.
Now to add sort by date, in pto_search.php look for the following code at line 308:
$orderBy["rating"] = "rating DESC";...and REPLACE with:
$orderBy["rating"] = "rating DESC";
$orderBy["date"] = "`date` ASC";And then the following code at line 481:
$html = str_replace("%PRICEDESC%",$sortBaseHREF."priceDesc",$html);...REPLACE with:
$html = str_replace("%DATE%",$sortBaseHREF."date",$html);Then in the Search / Banner template (wp-admin > Settings > PriceTapestry.org) look for the following markup:
Order By:..and insert the %DATE% placeholder as required e.g.
Order By:
%DATE%Now the final change this is required is that with the above applied, %DB_date% in your templates (Search Results / Each and Product / Main) will display as an integer. To convert back to a readable date, first in pto_search.php look for the following code at line 512:
$each = str_replace("%PRODUCT_NAME%",$row->name,$each);...and REPLACE with:
$each = str_replace("%PRODUCT_NAME%",$row->name,$each);
$each = str_replace("%DB_date%",date("d-M-y",$row->date),$each);And in pto_product.php look for the following code at line 386:
$html_product = str_replace("%BUY_URL%",pto_common_buyURL($product),$html_product);...and REPLACE with:
$html_product = str_replace("%BUY_URL%",pto_common_buyURL($product),$html_product);
$html_product = str_replace("%DB_date%",date("d-M-y",$product->date),$html_product);In each of the last 2 modifications you'll see the translated date format "d-M-y" which returns the closest to your original values e.g. 03-Sep-08 but if you're prefer an alternative display you can define whatever format you wish - see PHP's date() function for the full format specification...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Thanks a million for that, nearly working...
When it converts the date back I'm getting 01-Jan-70 on dates for all products. Can see by searching Google that this is a common problem but don't know how to rectify in your code above!!
Any ideas?
Thanks
Adrian
Hi Adrian,
As you've probably discovered from your research 01-Jan-70 is the date of the Unix "epoch" - and indicates that the value being converted is zero! Now for search results, I just realised that `date` needs to be added to the re-query; so in pto_search.php look for the following code at line 377:
$sql2 = "SELECT id,name,normalised_name,image_url,description,price,rating FROM `".$pto_config_databaseTablePrefix."products` WHERE id IN (".$in.")";...and REPLACE with:
$sql2 = "SELECT id,name,normalised_name,image_url,description,price,rating,date FROM `".$pto_config_databaseTablePrefix."products` WHERE id IN (".$in.")";And finally the following code at line 402:
$pto_searchResults[$k]->rating = $rows3[$product->id]->rating;...and REPLACE with:
$pto_searchResults[$k]->rating = $rows3[$product->id]->rating;
$pto_searchResults[$k]->date = $rows3[$product->id]->date;If you then browse merchant search results for a merchant who's feed you know contains good date field values that should confirm that it's being imported correctly and should also display on the product page, if not, let me know and I'll investigate further of course...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Thanks for your reply.
I already have releasedate in each of these locations in pto_search.php, I can email you this file if you like?
Thanks
Adrian
Hi Adrian,
No probs - I'll check it out for you...
Cheers,
David.
--
PriceTapestry.com
Hi Adrian,
Date sorting can be done - but needs to be applied against a timestamp (an integer representation of a date/time) rather than the date as a string - and this can be derived using PHP's strtotime() function...
What is the current format of your custom `date` field, e.g. VARCHAR(255) and if so, can you post a couple of example field values...
Thanks,
David.
--
PriceTapestry.com