Hiya
Hope you're well on this sunny Fridya afternoon! (Nearly beer time woo hoo!)
How would I go about adding an extra sort by clause?
I have a seperate table which logs views for each product name, and would like the search results to be ordered by the number of views.
I've looked at the "if ($useFullText)" section of search.php, and think that by using this query it may work?
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, views as views, MATCH name AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products`, counts WHERE MATCH name AGAINST ('".database_safe($parts[0])."') GROUP BY name, order by views";I've added the 'views as views' ', counts' and 'orderby views'.
Any thoughts?
Hi, I'm wondering if this use of "views" is similar to product "clicks" from this thread: http://www.pricetapestry.com/node/1832
I had modified jump.php to capture clicks to the merchant by product (for my own tracking, really), but thought it might be possible to count the "views" of each product, still within my site. I might be over-thinking... I can probably find page-views in my own stats program.
Thanks!
Hi,
Similar - but the modifications need to go in products.php to support it, although remember that a product page is constructed from multiple product records, so they would all have their "views" field updated at the same time.
It's easy to do, just add a field called "views" to the products table of type INT(11), and then look for the following code in products.php...
if ($q)
{..and insert the following on the next line:
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET views = views + 1 WHERE name = '".database_safe($q)."'";
database_queryModify($sql,$result);Cheers,
David.
It follows, then, that I would create a second table for views...? This is to enable products to "back-fill" upon import..
Just checking.. this is what I have started to do...
Thanks!
T.
Hi Theresa,
Yes - that's correct, my apologies for overlooking that - the views will get deleted upon refreshing the feeds otherwise. In that case; it would be a views table that would be required, updated at the same time as the views column in the products table; and then back-filled after importing. This can be done with a function similar to admin_importReviews() (includes/admin.php), and then call that function at the same place as admin_importReviews() within scripts/import.php and admin/feeds_import.php.
Cheers,
David.
I have successfully created the new "views" table (now populating), and added both clicks & views to the admin_import function. I will test the backfill function a little later, but have more than enough sample code to work with!
Thanks... and thanks to Matthew for the original post!
T.
Hi,
Nice one. Got it working.
Had to do a join between my products table and the counts table which holds the views data.
Seems to work, (but don't ask me to explain it!). I'll post the code if anyone wants / needs it :o)
Matt
Hi there... I just realized that while my views have been appending in the "view" table, the merchant has been missing (as a result, the backfill doesn't work as I haven't captured merchant name). View table consists of id, product_name, view, merchant - everything is added except merchant.
Deleting records & starting over isn't a problem, but I'm lost as to why merchant isn't added. Here's the relevant section from products.php:
if ($q)
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET views = views + 1 WHERE name = '".database_safe($q)."'";
database_queryModify($sql,$insertID);
//
$result = mysql_query("SELECT product_name FROM views WHERE product_name='".database_safe($q)."'");
$num = mysql_num_rows($result);
if(empty($num)) {
$sql = "INSERT INTO views SET product_name='".database_safe($q)."', merchant='".$product["merchant"]."', view='1'";
database_queryModify($sql,$insertID);
}
else {
$sql = "UPDATE views SET view=view+1 WHERE product_name='".database_safe($q)."' AND merchant='".$product["merchant"]."'";
database_queryModify($sql,$insertID);
} //Hi Theresa,
It looks like the $product variable has not been set at the point at which this code runs; meaning that the views will only update for the product name + empty merchant, which is what I think you implied is happening.
To fix this, I would move this code to the bottom of the if ($q) section; immediately AFTER the following line:
$header["meta"]["keywords"] = htmlentities($q,ENT_QUOTES,$config_charset);In addition, instead of $product["merchant"], you would need to use:
$product["products"][0]["merchant"];...as there could be multiple merchants selling the same product - so bear in mind that this code will only update the views table with product name + cheapest merchant...
Cheers,
David.
$product["products"][0]["merchant"];
That did it! All good now...
Thanks, David, for the help.. was driving me nuts..
Is there a way to prioritize results with the same price value that do not contain a specific term in the merchant name (but not the whole name)?
My need is as follows:
on http://rewardsdb.com/product/Buy.html
I would like merchants with the term 'Via' in their name to appear below all other merchants with the same price.
In this example
Delta Airlines Skymiles MILES USA
should appear before
Delta Airlines Skymiles Via AirMilesMart MILES USA
and
Midwest Airlines Midwestmiles MILES USA
should appear before
Midwest Airlines Midwestmiles Via Points.com MILES USA
Thanks
Hi Andrew,
Sure - this is almost identical to the method used in this thread to promote particular merchants.
In your case; the replacement SQL to go in products.php would be something like this:
$sql = "SELECT *,IF(merchant LIKE '%Via%',1,0) AS weighting FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price,weighting LIMIT ".$config_resultsPerPage;This will select a weighting value of 1 if the merchant name contains "Via" (the % character is a wildcard), and since the default sort order is ASCending; this will position those merchants with Via in the title below other merchants at the same price...
Cheers,
David.
Hi Matthew,
Adding an ORDER BY clause at that point will break the SQL as the order by is added later in the code by the following block, starting at line 105:
if (isset($orderBySelection[$sort])){
$sql .= " ORDER BY ".$orderBySelection[$sort];
}
If the views field is part of the products table then you won't need "views as views" (as every field is selected by virtue of the "*"). Therefore, if you revert back to the orignal code for the fulltext query section; and then look for the following section of code beginning at line 14:
$orderByDefault = array();$orderByDefault["rating"] = "rating DESC";
$orderByDefault["priceAsc"] = "minPrice ASC";
$orderByDefault["priceDesc"] = "minPrice DESC";
$orderByFullText = array();
$orderByFullText["relevance"] = "relevance DESC";
$orderByFullText["rating"] = "rating DESC";
$orderByFullText["priceAsc"] = "minPrice ASC";
$orderByFullText["priceDesc"] = "minPrice DESC";
Firstly, you would need to add your new sort order to these arrays as follows:
$orderByDefault = array();$orderByDefault["rating"] = "rating DESC";
$orderByDefault["priceAsc"] = "minPrice ASC";
$orderByDefault["priceDesc"] = "minPrice DESC";
$orderByDefault["views"] = "views DESC";
$orderByFullText = array();
$orderByFullText["relevance"] = "relevance DESC";
$orderByFullText["rating"] = "rating DESC";
$orderByFullText["priceAsc"] = "minPrice ASC";
$orderByFullText["priceDesc"] = "minPrice DESC";
$orderByFullText["views"] = "views DESC";
...and finally, if you want views as the default sort order; change line 8 from:
$sort = (isset($_GET["sort"])?$_GET["sort"]:"relevance");...to:
$sort = (isset($_GET["sort"])?$_GET["sort"]:"views");That should do the trick!
Cheers,
David.