Hi David,
I thought I saw a post in the message boards before about this but after searching for the last hour, I can't seem to find it. I'm looking to add a field to my search.php file. I'd like to add a price range to the products returned in the search results. For instance, as it is now, the search.php file lists products and lists the cheapest price next to each product. For products that have more than one merchant carrying them, I'd like to return a result of something like "From $5 to $7.99". Is this possible or is it too intensive on the DB?
Thanks! -Joe
The only thing I could think of is around line 86 in products.php. There's this line:
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($q)."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name ORDER BY relevance DESC LIMIT 3";I don't know enough about the query but is it maybe not calculating the correct number of merchants when there's a UPC/SKU because it's only counting "numMerchants" based on "name"?...or maybe I'm totally off.
No idea haha -Joe
Hello Joe,
Yes - it is almost certainly because the search page is only grouping by name, not SKU. If you could email me your search.php i'll take a look and have a think about the best way to do it...
Cheers,
David.
Hi David,
Thanks a lot for that bit of code, it's exactly what I was looking for but unfortunately brought another problem to the forefront on my site. I originally asked you this question because I thought my site used to say "from...$" when there was more than one merchant, however I hadn't been seeing this lately. So I checked the search.php and searchresults.php files and couldn't find any problems or major changes that I had made. I applied the new code and the site hasn't consistently been showing the "from $ to $" for all products with multiple merchants.
I think this could have something to do with products linked my UPC (with the UPC mod). Is this possible? For example, check out this search results page (if you can delete these links, that'd be great, thanks):
{link saved}
Then look at the fourth product on that search results list - it has two merchants but didn't show that on the results page (this product is linked by UPC from what I can remember).
Now look at these search results:
{link saved}
It has the proper code showing up on some results and then on another result it just has the "from $..." showing even though there are like 6 merchants on that product. Almost like it can't "figure out" that there are other merchants with that product until the product.php page is pulled-up.
I'm thinking that the UPC mod isn't being called early enough...like somehow from the searchresults.php page?
I haven't "custom" coded the mod at all. I'm going to look through the UPC mod threads to see if I can figure it out but thought I'd post this in the meantime in case maybe I'm missing something very simple.
Thanks as always David!
-Joe
Hi Joe,
Try putting this code at the top of your searchresults.php:
//**** Botch Count Code ****
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name = '".$product["name"]."' GROUP BY name";
database_querySelect($sqlprod,$nrows);
$product["numMerchants"] = $nrows[0]["numMerchants"];
$product["minPrice"] = $nrows[0]["minPrice"];
$product["maxPrice"] = $nrows[0]["maxPrice"];
// **** End Botch ****Place this at the top after:
<?php
if (file_exists("html/user_searchresults_before.php")) require("html/user_searchresults_before.php");
?>
<?php foreach($searchresults["products"] as $product): ?>As you can tell by the code comments, it's a bit of a botch and may impair performance - so you may want to remove the code again - but it should do the job. Depending on your search keyword depends on how many merchants are counted in the original script. i.e. you could have a dishwasher from, say, 10 merchants called the BX100. If you searched for BX100 then the count should be correct as this term will be in each merchant's product record, but if you searched for 'dishwasher' only records with the word 'dishwasher' in the name or description field will be counted so you may well have less than expected. The product.php counts purely on 'name' and hence is accurate.
Cheers.
Keeop
Hi Keeop,
Thanks for that bit of code. Is that what you're using on your site?
My idea is something like this in products.php:
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($q)."') OR sku AGAINST ('".database_safe($rows[0]["sku"])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name ORDER BY relevance DESC LIMIT 3";
or something like...
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($q)."') AND sku AGAINST ('".database_safe($rows[0]["sku"])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name ORDER BY relevance DESC LIMIT 3";
I'm not sure what applies or if I need to apply this code anywhere else? Do other people who have the SKU mod also have this problem?
Thanks for the help! -Joe
Hi Joe,
Yes I am and it works fine - doesn't have a noticeable impact on performance on the sites I'm using it on. If your products.php is showing the correct information, then you really just want to use the corresponding query in your searchresults.php to do the counting. The example I posted earlier was based on the standard query in products.php:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($q)."' ORDER BY price LIMIT ".$config_resultsPerPage;Have you changed this for your SKU mod? If so, just amend the code I posted for searchresults.html accordingly and you should be fine. For example:
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name = '".$product["name"]."' OR sku = '".$product["sku"]."' GROUP BY name";The problem here though is the grouping as it will either group by name or sku. Sorry, I don't know how your mod works. What's the sql in your products.php?
Cheers.
Keeop
Hi guys,
Thanks for adding some pointers here, Keeop.
As I understand it, the products page is working fine, but the problem comes with search.php as this is GROUPing by name, so the same product with a different name but the same SKU would appear as a separate result in therefore missed out of the range, so you might have:
Big Blue Widget - £10.00
Blue Widget - £11.50...instead of:
Blue Widget - from £10.00 to £11.50Joe - how many products are on your site as I think this will have a bearing on the possible solutions as to whether they would be viable from a performance point of view?
Cheers,
David.
Hi guys,
David, yes you are correct in your description of the problem. It's with the grouping of the products.
Thanks for the help so far! I have a lot of products on my site. So far my biggest section will be electronics and I'm sure will be over 1 million products in the future. At the moment I think it's only about 200,000 products but I'm not live yet since I'm still refining many things.
Keeop, the sku mod that I am refering to is:
http://www.pricetapestry.com/node/775
David,
In this line (86 from products.php):
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($q)."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name ORDER BY relevance DESC LIMIT 3";
How is the ".$where." calculated? (Just trying to figure it all out)
Thanks! -Joe
Hi Joe,
$where is constructed on the 10 lines immediately before that line. It first adds a clause to pick the related products from the same category as the product show (if it has a category value), and then adds a clause to exclude the product itself from the related products results...
Cheers,
David.
Thanks David,
I should've seen it right there. I've been trying to figure out a "GROUP BY" clause that works. I figured out you can easily do a GROUP BY...AND... (by using a ,) but I still don't think there's an easy way to use an OR statement with the GROUP BY clause.
Or maybe a way to count all "name" and count all "sku" for the same items and then add the values together?
I'm still trying to figure something out lol.
-Joe
Hi Joe,
How about this in your products.php then:
//**** Botch Count Code ****
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".$product["name"]."' LIMIT 1";
database_querySelect($sql,$rows);
if ($rows[0]["sku"])
{
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' or sku = '".database_safe($rows[0]["sku"])."'GROUP BY name";
}
else
{
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' GROUP BY name";
}
database_querySelect($sqlprod,$nrows);
$product["numMerchants"] = $nrows[0]["numMerchants"];
$product["minPrice"] = $nrows[0]["minPrice"];
$product["maxPrice"] = $nrows[0]["maxPrice"];
// **** End Botch ****Cheers.
Keeop
Hi Keeop,
Thanks for that! Where do you suggest to try it? At the top of the file again or in place of line 86 in products.php?
Have either of you guys tried using the UNION MySQL function or even joining two SELECT * statements together with a + symbol?
-Joe
I came up with this, but I don't know if this makes any sense or would work. I about to go to bed...it's pretty late here. Thanks for the help as always! Thanks David and Keeop!
$sql = "SELECT
(SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($q)."') OR sku AGAINST ('".database_safe($rows[0]["sku"])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name ORDER BY relevance DESC LIMIT 3")
+
(SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($q)."') OR sku AGAINST '".database_safe($rows[0]["sku"])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY sku ORDER BY relevance DESC LIMIT 3");-Joe
Hi Joe,
If you want to try out my code, just look at my previous posts on where to put it. Basically, near the top of products.php.
Cheers.
Keeop
Hi David and Keeop,
Keeop - I tried the code of
//**** Botch Count Code ****
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".$product["name"]."' LIMIT 1";
database_querySelect($sql,$rows);
if ($rows[0]["sku"])
{
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' or sku = '".database_safe($rows[0]["sku"])."'GROUP BY name";
}
else
{
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' GROUP BY name";
}
database_querySelect($sqlprod,$nrows);
$product["numMerchants"] = $nrows[0]["numMerchants"];
$product["minPrice"] = $nrows[0]["minPrice"];
$product["maxPrice"] = $nrows[0]["maxPrice"];
// **** End Botch ****<?php
if (file_exists("html/user_searchresults_before.php")) require("html/user_searchresults_before.php");
?>
<?php foreach($searchresults["products"] as $product): ?>It didn't seem to count the products properly. The way I implemented it, I think it made it worse. Instead of having some products read "from $ to $", it just changed them to one of the prices. I imagine this kind of code is on the right track though.
products.php is listing all of the merchants properly for each product...whether the product is related by name or SKU. So, I think it's just a matter of counting those shops somehow - like you said. My SQL knowledge is not good. I'm currently figuring it out slowly. I think this issue can be solved using a combination of PHP and MySQL together maybe.
Anyhow, let me know if you think that I might be doing something wrong or if you guys have any other ideas. I'm tearing my hair out on this one haha.
Thanks as always David!...and thanks a lot Keeop for your help on this one!
Hi Joe,
That sound like it's overwriting the min / max price variables when there are no results in the second query - so instead of:
database_querySelect($sqlprod,$nrows);
$product["numMerchants"] = $nrows[0]["numMerchants"];
$product["minPrice"] = $nrows[0]["minPrice"];
$product["maxPrice"] = $nrows[0]["maxPrice"];...try:
if (database_querySelect($sqlprod,$nrows))
{
$product["numMerchants"] = $nrows[0]["numMerchants"];
$product["minPrice"] = $nrows[0]["minPrice"];
$product["maxPrice"] = $nrows[0]["maxPrice"];
}Cheers,
David.
Hi David,
Thanks for the reply! Unfortunately still no luck :(...same result(s) as before. The only file that I've modified (in the last couple of attempts) has been the searchresults.php file at the top of the file. Does this make sense? I understand what the code should be doing so I'm not sure why it's not working. I'm not getting any errors so I guess that's a good thing haha ;)
Hi Joe,
Yeah, it should be placed under:
<?php
if (file_exists("html/user_searchresults_before.php")) require("html/user_searchresults_before.php");
?>
<?php foreach($searchresults["products"] as $product): ?>I'm not using the SKU mod so I'm using the cut down version of this count code that I pasted originally, and that does work for me so I'm guessing my ham-fisted approach of trying to amend it to fit in with your mod hasn't quite worked. Still, I'm sure David will be able to sort you out!
Actually, maybe the variables are getting overwritten as the $rows array is used lower down in searchresults.php?? You could try the same code with some new naming:
//**** Botch Count Code ****
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".$product["name"]."' LIMIT 1";
database_querySelect($sql,$rowsFirst);
if ($rowsFirst[0]["sku"])
{
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rowsFirst[0]["name"])."' or sku = '".database_safe($rowsFirst[0]["sku"])."'GROUP BY name";
}
else
{
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rowsFirst[0]["name"])."' GROUP BY name";
}
database_querySelect($sqlprod,$nrows);
$product["numMerchants"] = $nrows[0]["numMerchants"];
$product["minPrice"] = $nrows[0]["minPrice"];
$product["maxPrice"] = $nrows[0]["maxPrice"];
// **** End Botch ****Cheers.
Keeop
Hi Keeop,
David was able to solve the issue by placing the code into the $product variable loop below
<?php foreach($searchresults["products"] as $product): ?>
Thanks a lot for your help - I learned a lot through it all so it was worth it!
And David, thanks again! Much appreciated!
Hi David (and Keeop),
I'm reposting in this thread because it relates mostly to this question. I found that the count is still not spot-on. It's missing the correct "numMerchants" value for quite a few products for some reason. The code posted is working but I don't think it's getting along with the sku mod. I'm not sure. In the meantime, I was playing around with the MySQL Query Browser trying to get a query that would work for what I want (counting the number of merchants on the search.php page and returning all of the right results).
I don't want to change how PT works, but really just how it's counting the field of "numMerchants" for the search.php page so I can allow the code to count properly.
I came up with a query in MySQL that works but have been trying to figure out where I could put it into PT for it to work properly - or even if it's possible?
This is the code:
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('Product Name') AS relevance FROM `products` GROUP BY name
UNION
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('Product Name') AS relevance FROM `products` GROUP BY sku ORDER BY relevance + numMerchants DESC LIMIT 400Now as you can see there's no PHP code and I know PT takes care of some of these issues such as DESC and LIMIT, etc. Some from the config file, some from the search itself, etc. I'm most concerned with using the UNION statement and adding the ORDER BY statement to just the 2nd query. The UNION statement is just joining the results between the two SELECT statement together.
Can this be done or does it involve too much customization (to the point of no return? haha)
Thanks! -Joe
Hi Joe,
If those queries are generating the results you require, then how about:
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('Product Name') AS relevance FROM `products` GROUP BY name";
database_querySelect($sql,$nrows);
$nameCount = $nrows[0]["numMerchants"];
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('Product Name') AS relevance FROM `products` GROUP BY sku";
}
database_querySelect($sql,$nrows);
$skuCount = $nrows[0]["numMerchants"];
$product["numMerchants"] = $nameCount + $skuCount;Worth a pop anyway.
Cheers.
Keeop
Hi Keeop,
Thanks for that bit of code! Definitely helpful! Unfortunately it doesn't work...it's running out of memory and taking too long to run. Probably something simple so I'll just try to rework the code sample that you gave me. I'm going to see what I can figure out before I head to bed :P
Thanks again!! -Joe
---------------------
Figured it out some more by merging it with the old code. But now I'm getting MySQL errors on some products of:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result
So I need to figure out a way to recode it a little. This is what I did so far:
<?php
foreach($searchresults["products"] as $product):
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE name = '".$product["name"]."' LIMIT 1";
database_querySelect($sql,$rows);
if ($rows[0]["sku"] && $rows[0]["sku"] !== "000000000000")
{
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($rows[0]["name"])."') AS relevance FROM `".$config_databaseTablePrefix."products` GROUP BY name";
database_querySelect($sql,$nrows);
$nameCount = $nrows[0]["numMerchants"];
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($rows[0]["name"])."') AS relevance FROM `".$config_databaseTablePrefix."products` GROUP BY sku";
database_querySelect($sql,$nrows);
$skuCount = $nrows[0]["numMerchants"];
$product["numMerchants"] = $nameCount + $skuCount;
}
else
{
$sqlprod = "SELECT name , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' GROUP BY name";
}
if (database_querySelect($sqlprod,$nrows))
{
$product["numMerchants"] = $nameCount + $skuCount;
$product["minPrice"] = $nrows[0]["minPrice"];
$product["maxPrice"] = $nrows[0]["maxPrice"];
}
?>
If I remove the last "if" statement and the code following it, I don't get those errors and the search results come up. The problem is, any product where the count was by sku shows that it is available at 0 merchants (so the count code isn't working even though it's not creating errors). Let me know if you guys have any further ideas. I'm heading to bed for a bit :P
Thanks again for all of the help, I really appreciate it and am learning as fast as I can :) -Joe
Yes, it's me again! I know you guys are getting sick of me but I'm very close lol. I'm trying to figure this statement out in MySQL. I think this statement will get a proper count. My last statement didn't work out :(. This one is returning a MySQL error of "Column 'price' in field list is ambiguous":
<?php
$sqlprod = "SELECT name, sku, MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name = '".database_safe($rows[0]["name"])."' UNION SELECT b.name, b.sku, , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` AS a JOIN `".$config_databaseTablePrefix."products` AS b USING(sku) WHERE a.name = '".database_safe($rows[0]["name"])."'";
?>
I know what the error means, but I don't see the problem. Is it with the aliases or after the join? I haven't created a statement like this before so I'm not sure.
Any ideas?
Thanks, -Joe
Hi Joe,
The max price is also selected as available for display as $product["maxPrice"] within html/searchresults.php. The minPrice is currently displayed by the following code on line 25:
<em><?php print translate("from"); ?></em> <strong><?php print $config_currencyHTML.$product["minPrice"]; ?></strong><br />...so I would suggest replacing that with something like this, so that a range is only displayed if the minPrice and maxPrice are different:
<?php if ($product["minPrice"] < $product["maxPrice"]): ?><strong><em>from</em><?php print $config_currencyHTML.$product["minPrice"]; ?> <em>to</em> <?php print $config_currencyHTML.$product["maxPrice"]; ?></strong><br />
<?php else: ?>
<strong><em>from</em><?php print $config_currencyHTML.$product["minPrice"]; ?></strong><br />
<?php endif; ?>
Cheers,
David.