Hi,
I wish to have on my first page:
- "Top 10 Merchants" (based on numbers of clicks/feed)
- "Top 10 Products" (based on product rating)
I can`t figure it out.
The pupil beats the Master.
I'm impressed by myself, well ok maybe a little impressed but
Forget the top 10 products, why not have the top 10 merchants? This is based on click thrus
<?php
$sql = "SELECT merchant FROM `".$config_databaseTablePrefix."feeds` ORDER BY clicks DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
foreach($rows as $merchant)
{
if ($config_useRewrite)
{
$href = $config_baseHREF."merchant/".tapestry_hyphenate($merchant["merchant"])."/";
}
print "<a href='".$href."'>".$merchant["merchant"]."</a><br>";
}
}
?>
I have left the else bit out mainly because I couldnt get it to work and most people will have mod rewrite on anyway
Here is the code that I was working on for the else bit
else
{
$href = $config_baseHREF."search.php?q=".urlencode":"($merchant["merchant"]).":";
}
print "<li><a href='".$href."'>".$merchant["merchant"]."</a></li>";Regards
Mark
Why would you want a top 10 merchants list? I can see where a top 10 products list would come in handy but as for merchants, I'm not so sure.
How about creating a new table with 2 columns: product and count? In the products table, we cannot use ID as that changes upon import. But we can use product name as that remains consistent.
On jump.php, get it to search this new table and see if the product exists in it. If it does, simply add one the count value. Otherwise, add it as a fresh row.
Then use a select command to select 10 records ordered by count desc.
Just an idea!
Having any kind of internal links to pages is a good thing, doesn't matter what it is, it could be best toilet paper but it would come in handy.
My highest merchant on click thrus so far is an office supplier, infact
This is my top ten
Viking Direct - Link
Euro Office - Link
Freemans - Link
Kiddicare - Link
Love Film - Link
Additions Direct - Link
1st 4 Phonecards - Link
247 Electrical - Link
3M Select - Link
3 Mobile - Link
So those 10 merchants, and 10 links to thousands of products, might see me with sales coming from those merchants. Remember it's better that not everything is the same, if we all had the same top 10 products or merchants etc, then bots wouldnt like it.
I do agree though, it would be good to have a click thru system for products rather than a review based top 10, as it shows what is popular for people clicking thru and hopefully spending.
At the moment people would be searching more for BBQ and other garden products, if we do actually get a summer, but then it will go onto christmas products.
Anyway enough of my babble, just woke up so going on the second work stint now, then bed at 2am no doubt :)
Morning David
Is it possible to code in columns and limit characters in the above code?
Chris
Hi Chris,
Columns involve creating table HTML rather than a simple list. Based on the example code in the first reply in this thread, try something like this:
<?php
$sql = "SELECT name FROM `".$config_databaseTablePrefix."products` ORDER BY rating DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
print "<table>";
print "<tr>";
$column = 0;
foreach($rows as $product)
{
$column++;
if ($column == 4)
{
print "</tr>";
print "<tr>";
$column = 0;
}
if ($config_useRewrite)
{
$href = $config_baseHREF."product/".tapestry_hyphenate($product["name"]).".html";
}
else
{
$href = $config_baseHREF."products.php?q=".urlencode($product["name"]);
}
print "<td><a href='".$href."'>".$product["name"]."</a></td>";
}
print "</tr>";
print "</table>";
}
?>
For more or less columns simply change
if ($column == 4)To limit the characters, this can be done using substr() where the product name is displayed. From the code above, change this line:
print "<td><a href='".$href."'>".$product["name"]."</a></td>"; print "<td><a href='".$href."'>".substr($product["name"],0,30)."</a></td>";...where 30 is the max number of characters to display.
Hope this helps,
Cheers,
David.
David
Is it possible to code in even output per column?
ie 2 columns, 10 products, 5 per column.
Thanks
Chris
Hi Chris,
If you change the code to:
if ($column == 3)...it should then limit to 2 columns, and with 10 products selected will display 5 products per column. Hope this is what you're after...
Cheers,
David.
Hi David,
What would the top rated products code be for 15/09A please?
I'd like it to look how related products is presented so it would have the image, name and price along with rating.
Also, is it possible to do 'latest reviews' in this way, showing 4 or 5 products with image, name, price, rating and review snippet?
I was thinking these for the index page
Many thanks!
Hi,
Here's a PHP segment to display top 4 rated products in search results format (Related Products uses html/searchresults.php) but with the description replaced by the top rated comment...
<?php
$sql = "SELECT DISTINCT(normalised_name) FROM `".$config_databaseTablePrefix."products` ORDER BY rating DESC LIMIT 4";
$searchresults["numRows"] = database_querySelect($sql,$rows);
$searchresults["products"] = $rows;
if ($searchresults["numRows"])
{
$ins = array();
foreach($rows as $row)
{
$ins[] = "'".database_safe($row["normalised_name"])."'";
}
$in = implode(",",$ins);
$sql2 = "SELECT id,name,normalised_name,image_url,description,price,rating,MIN(price) AS minPrice, COUNT(id) AS numMerchants
FROM `".$config_databaseTablePrefix."products` WHERE normalised_name IN (".$in.") GROUP BY name ORDER BY NULL";
database_querySelect($sql2,$rows2);
$rows3 = array();
foreach($rows2 as $row2)
{
$rows3[strtolower($row2["normalised_name"])] = $row2;
}
foreach($searchresults["products"] as $k => $p)
{
if (!isset($rows3[strtolower($p["normalised_name"])]))
{
unset($searchresults["products"][$k]);
continue;
}
$searchresults["products"][$k] = array_merge($searchresults["products"][$k],$rows3[strtolower($p["normalised_name"])]);
$sql4 = "SELECT comments FROM `".$config_databaseTablePrefix."reviews`
WHERE product_name='".database_safe($searchresults["products"][$k]["normalised_name"])."' AND comments <> '' ORDER BY rating DESC LIMIT 1";
if (database_querySelect($sql4,$rows4))
{
$searchresults["products"][$k]["description"] = $rows4[0]["comments"];
}
else
{
$searchresults["products"][$k]["description"] = "";
}
$searchresults["products"][$k]["productHREF"] = tapestry_productHREF($searchresults["products"][$k]);
}
require("html/searchresults.php");
}
?>
To do the same for latest reviews; REPLACE the first line of the segment with the alternative SQL as follows;
$sql = "SELECT DISTINCT(product_name) AS normalised_name FROM `".$config_databaseTablePrefix."reviews` ORDER BY id DESC LIMIT 4";If you wish to title the areas on the home page (and using Foundation templates), you'll need to create a containing row, for example (outside of PHP section)...
<div class='row'>
<div class='small-12 columns'>
<h4>Latest Reviews</h4>
</div>
</div>Cheers,
David.
--
PriceTapestry.com
Hi David,
I've been trying to work out some "Top Lists" features, I've gone though various threads and experimented with many but so far I could only get this to work:
<?php
$sql = "SELECT name FROM `".$config_databaseTablePrefix."products` ORDER BY rating DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
foreach($rows as $product)
{
if ($config_useRewrite)
{
$href = $config_baseHREF."product/".tapestry_hyphenate($product["name"]).".html";
}
else
{
$href = $config_baseHREF."products.php?q=".urlencode($product["name"]);
}
print "<li><a href='".$href."'>".$product["name"]."</a></li>";
}
}
?>What I am after really is a Top Products and especially a Top Merchant page (given the nature of my site this would be great to have but I rather not have to manage the "ranking").
I was thinking to get started with a similar method than to the above or either using a similar process to Related Items (pulled by search results) - or even better... a Merchant page just like the original one but Sorted by Click as they appear in Admin Feed Mgnt.
Eventually for this I would also like to pull additional information and relative hrefs for each such as Product Qty, Ratings, Reviews, etc...
I know its a lot asking so a minimum info pointing in the right directions would be more than appreciated and no rush.
Many thanks,
Antony
Hi Antony,
If you are using individual feeds per merchant than a "Top Merchants" by clicks is straight forward for example;
$sql = "SELECT merchant FROM `".$config_databaseTablePrefix."feeds` ORDER BY clicks DESC";
if (database_querySelect($sql,$rows))
{
print "<ul>";
foreach($rows as $row)
{
if (file_exists("logos/".$row["merchant"].$config_logoExtension))
{
$html = "<img src='".$config_baseHREF."logos/".str_replace(" ","%20",$row["merchant"]).$config_logoExtension."' />";
}
else
{
$html = $row["merchant"];
}
print "<a href='".tapestry_indexHREF("merchant",$row["merchant"])."'>".$html."</a>";
}
print "</ul>";
}In terms of popular products, it is possible to modify the database so that product records are preserved as per this thread following which you can add a views column as described in here if that is the kind of thing you were looking for...
Cheers,
David.
--
PriceTapestry.com
Thank you David, this is a perfect lead for me to work onto and it's actually a similar code to the other I'm using to fetch the Top 10 Products. Just what I was hopping for.
Cheers!
Ant
Hi David,
Hope you've had a good weekend!
I've been working on the Top 10 Products & Top 10 Merchants modules but I'm struggling to integrate Ratings and Prices within.
Top 10 Products:
{code saved}
1 - How add include "ellipsis" on this example from the above module?
.substr($product["description"],0,24)..substr($product["description"],24,"...").Then Top 10 Merchants:
{code saved}
1- is it easily possible to calculate an "average" star ratings for each merchant and return stars?
2- How to integrate "Prices" as From {price}
NB: I actually managed to call the prices but I have the following in includes/tapestry.php and Zero prices come up as 0.00 where it should be "NO"
function tapestry_price($price)
{
global $config_currencyHTML;
global $config_currencySeparator;
global $config_currencyHTMLAfter;
if ($price=="0.00")
{
return "NO";
}
else
{
return $config_currencyHTML.intval($price);
}
}I feel like I've been abusing this forum lately with support request Im so sorry about that and hope you dont mind, I have tried millions of different ways this weekend to make this function, some outputs better than others but nothing 100% satisfying - Im almost done now so this should be one of the last touch to finish. Thank you so much.
PS: all the above is on the same file (html/index.php)
Ant
Hello Ant,
For the ellipses use;
.substr($product["description"],0,24)."...".`rating` and `price` fields are included in the result; so just a <div> on their own (so would just need to add the required classes) e.g.;
print "<div>".tapestry_price($product["price"])."</div>";
print "<div>".tapestry_stars($product["rating"],"")."</div>";...or for rating with the smaller star images;
print "<div>".tapestry_stars($product["rating"],"s")."</div>";For the top merchants average rating and minimum price; within the code the inner loop begins with:
foreach($rows as $row)...REPLACE with:
$ins = array();
foreach($rows as $row)
{
$ins[] = "'".database_safe($row["merchant"])."'";
}
$sql2 = "SELECT merchant,CEIL(AVG(rating)) AS avgRating, MIN(price) as minPrice FROM `".$config_databaseTablePrefix."products` WHERE merchant IN (".implode(",",$ins).") GROUP BY merchant";
database_querySelect($sql2,$rows2);
$merchantSummary = array();
foreach($rows2 as $row2)
{
$merchantSummary[$row2["merchant"]] = $row2;
}
foreach($rows as $row)And then inside the loop, you can display using (example <div> only as above)
print "<div>Average Rating: ".tapestry_stars($merchantSummary[$row["merchant"]]["avgRating"],"")."</div>";
print "<div>Prices From: ".tapestry_price($merchantSummary[$row["merchant"]]["minPrice"])."</div>";For the price "NO" try a numeric comparison instead e.g.
if ($price==0)Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Superstar Man! This is perfect. Thanks you!
Hi David,
Hope you're well,
Whilst Im very happy with my top 10 lists, I'm curious to know if a direct link to the merchant can be added to the top 10 merchants using existing affiliate links (can be random product link). Any thoughts on this?
Thanks,
Antony
Hello Ant,
Sure - in the code added above to get avgRating and minPrice:
$sql2 = "SELECT merchant,CEIL(AVG(rating)) AS avgRating, MIN(price) as minPrice FROM `".$config_databaseTablePrefix."products` WHERE merchant IN (".implode(",",$ins).") GROUP BY merchant";...you can add buy_url e.g.
$sql2 = "SELECT buy_url,merchant,CEIL(AVG(rating)) AS avgRating, MIN(price) as minPrice FROM `".$config_databaseTablePrefix."products` WHERE merchant IN (".implode(",",$ins).") GROUP BY merchant";And then to use that instead of the link to the merchant results on your site; use:
print "<a href='".$row["buy_url"]."'>".$html."</a>";(or as well as of course, as a separate link)
Cheers,
David.
--
PriceTapestry.com
Hi David, thank for your quick response as always ... tried it but unfortunately did not work :( the output is EMPTY/BLANK
we're looking at 3rd PRINT line after for the placement of the external link.
Any idea?
Many thanks,
Ant
{code saved}
Hello Ant,
An empty/blank page would normally indicate a PHP error but the syntax is fine in this case - error reporting is normally disabled on product servers so you could try temporarily adding the following to the top;
<?php
ini_set('display_errors','on');error_reporting(E_ALL);
?>
Apologies if mis-understood, if you could copy the actual HTML being generated (I'll remove before publishing your reply) I'll see if I can spot the issue from there...
Cheers,
David.
--
PriceTapestry.com
Hi David ... My appologies for the confusion ... the HTML page renders fine - Its the direct link to Merchant that we're trying to get that remains empty but where we should see a jump.php at end of the URL. The jump part missing.
Thanks
Ant
Hello Ant,
Ah sorry - $merchantSummary wasn't being used; this line:
print "<div class='col-sm-3 py-2'>".tapestry_stars($merchantSummary[$row["merchant"]]["avgRating"],"")."<br><a class='small text-secondary' href='".$row["buy_url"]."' title='".$row["merchant"]." Bonuses'><i class='fas fa-link mr-1'></i>".$html."</a></div>";...should be:
print "<div class='col-sm-3 py-2'>".tapestry_stars($merchantSummary[$row["merchant"]]["avgRating"],"")."<br><a class='small text-secondary' href='".$merchantSummary[$row["merchant"]]["buy_url"]."' title='".$row["merchant"]." Bonuses'><i class='fas fa-link mr-1'></i>".$html."</a></div>";But if you'd prefer it to go via jump.php, change the SQL to select "id" instead of "buy_url":
$sql2 = "SELECT id,merchant,CEIL(AVG(rating)) AS avgRating, MIN(price) as minPrice FROM `".$config_databaseTablePrefix."products` WHERE merchant IN (".implode(",",$ins).") GROUP BY merchant";And then in place of the above, use:
print "<div class='col-sm-3 py-2'>".tapestry_stars($merchantSummary[$row["merchant"]]["avgRating"],"")."<br><a class='small text-secondary' href='".tapestry_buyURL(array("id"=>$merchantSummary[$row["merchant"]]["id"]))."' title='".$row["merchant"]." Bonuses'><i class='fas fa-link mr-1'></i>".$html."</a></div>";Cheers,
David.
--
PriceTapestry.com
Wow great! The fist example fits perfectly and generates my request as intended!
End results in action here: {link saved}
With this now in place, I think that it concludes Phase 1 - I'm really pleased with the overall outcome ... Now I guess its up to me to further work on content and marketing.
You're the very best man!
Best,
Antony
Hello Antony,
Thanks for your comments and all the best with your project!
Cheers,
David.
--
PriceTapestry.com
Hi Gabriel,
The SQL to select the top 10 products would be as follows:
SELECT * FROM products ORDER BY rating DESC limit 10Here's some sample code to display the results:
<?php$sql = "SELECT name FROM `".$config_databaseTablePrefix."products` ORDER BY rating DESC LIMIT 10";
if (database_querySelect($sql,$rows))
{
foreach($rows as $product)
{
if ($config_useRewrite)
{
$href = $config_baseHREF."product/".tapestry_hyphenate($product["name"]).".html";
}
else
{
$href = $config_baseHREF."products.php?q=".urlencode($product["name"]);
}
print "<li><a href='".$href."'>".$product["name"]."</a></li>";
}
}
?>
As product click data is only recorded at product level (not merchant level) it is not straight forward to establish a merchant "Top 10" - but also note that this statistic (if available) would be skewed in favor of merchants with more products...
Hope this helps!
Cheers,
David.