Firstly I'd like to say this is an excellent script.
What I'd like to do is add an additional filter to the category pages. What changes would need to be made for a drop down list to appear at the top of the category pages so when the user is browsing a category they can select a particular brand from a drop down menu so the product list is filtered to only those products in the category from that brand.
Ideally the drop down would only contain a list of brands which had products in that category.
For instance if you were browsing a category say Mens T-shirts - the drop down would contain brands such as Ben Sherman, Hugo Boss, Levis. Selecting a brand such as Ben Sherman would list Ben Sherman T-shirts.
Thanks for your quick response.
That worked well, except for when the results span more than one page. When clicking on page 2 the brandFilter= is lost, so I guess there needs to be a change there?
Yes - the brandFilter parameter must be persisted on the navigation panel; and also (if you wish) on the links to sort by relevance, rating or price.
For the navigation panel; you basically need to copy the way in which the "sort" parameter is added to the construction of the various HREF variables in html/navigation.php
For example, you would change:
$prevHREF = "?q=".urlencode($q)."&page=".$prevPage."&sort=".$sort;to:
$prevHREF = "?q=".urlencode($q)."&page=".$prevPage."&sort=".$sort."&brandFilter=".urlencode($_GET["brandFilter"]);There are 4 instances where this modification is required; on lines 16,62,75,99.
In search.php, you just need to make one change, from:
$sortHREF = $config_baseHREF."search.php?q=".urlencode($q)."&page=1&sort=";to:
$sortHREF = $config_baseHREF."search.php?q=".urlencode($q)."&page=1&brandFilter=".urlencode($_GET["brandFilter"])."&sort=";Let me know if that works as expected...
That worked well, except there was one small problem with $sortHREF
Changed it to
$sortHREF = $config_baseHREF."search.php?q=".urlencode($q)."&page=1&brandFilter=".urlencode($_GET["brandFilter"])."&sort=";I just spotted that! I've fixed it in the above post - thanks!
I'll roll some code for the Brand drop-down on the category page now... check back in a bit for that...
Ok, first thing to do is bring in the widget library (which is only used in the admin area) as it has a nice function for creating a drop-down menu from an array, so add the following line at the top of search.php
require("includes/widget.php");Next step is to get a distinct array of brands matching the current category query. You will know that you are in a category query if $parts[0] == "category", so you can put all the code for this inside an if statement with that test; and the drop down will only be displayed on the appropriate pages.
I would position this to start off with after the banner, and before the search results; so you will want to add this code just after the following line:
require("html/banner.php");Here's the PHP code that should create the drop down:
<?php
if ($parts[0] == "category")
{
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."'";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brands[$row["brand"]] = $row["brand"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("brandFilter",$brands,$_GET["brandFilter"],"");
print "<input type='hidden' name='q' value='".urlencode($q)."' />";
print "<input type='submit' value='Filter By Brand' />";
print "</form>";
}
?>
Fingers crossed...!
Cheers,
David.
David, thanks very much for the code.
It would not work with the line of code as below
print "<input type='hidden' name='q' value='".urlencode($q)."' />";but when changed to
print "<input type='hidden' name='q' value='".($q)."' />";Taking this one small step further. How about an alternative where you would have the brands as text links across the top of the results page such as
Browse by Brand: Brand 1 | Brand 2 | Brand 3 | Brand 4
Great!
> How about an alternative where you would have the brands as text links
In some respects that is easier - just create links to the appropriate search.php URL rather than using a FORM:
<?php
if ($parts[0] == "category")
{
print "Filter by Brand: ";
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."'";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brandHREF = $config_baseHREF."search.php?q=".urlencode($q)."&brandFilter=".urlencode($row["brand"]);
print "<a href='".$brandHREF."'>".$row["brand"]."</a> |";
}
}
}
?>
That should do the trick; although i'm not sure why urlencode didn't work in the first example - you may have the same problem here...!
Hope this helps,
David.
Excellent. Thanks very much for all your help David.
hi
I have just tried to implement this mod, every thing worked until i tried the paging, it just sent me to the front page. I noticed it was said there was an error,has this been rectifed.
I am trying to get a list of brands on the category page when searched.
Thanks
Brent
Hi Brent,
If you have not already made any modifications to html/navigation.php to include the new parameter on the page URLs then you will need to make the changes shown in the 4th message on the following thread:
http://www.pricetapestry.com/node/636
This will add the brandFilter parameter to the URL in each of the page links, although i'm not sure why you would be getting redirected to the front page. Have you already made changes to navigation.php - and is the navigation still working for a normal search?
Cheers,
David.
Hi
Thanks, just tried it again from the link you gave and works OK.
Brent
Hi david
Used the php above to create a list of brands at the top of the category search pages(works great), is it possible to have them in a-z order. Also how hard would it be to rewrite navigation urls with mod rewirte after using code in
http://www.pricetapestry.com/node/636
Brent
Hi Brent,
To make the list in alphabetical order change the SQL as follows:
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."' ORDER BY brand";With regards rewriting the navigation URLs, do you mean that you want to keep the re-written URL after selecting a brand filter; so you would have a URL like:
http://www.example.com/category/Widgets/3.html?brandFilter=Foo
Cheers,
David.
hi
Yes, its just to make it a bit more search engine friendly.
Brent
Firstly, you can detect if the source URL is rewritten by looking at the $rewrite variable; so in the code that generates the brand links you need to modify the part in the foreach loop something like this:
<?php
foreach($rows as $row)
{
if ($rewrite)
{
$brandHREF = "?brandFilter=".urlencode($row["brand"]);
}
else
{
$brandHREF = $config_baseHREF."search.php?q=".urlencode($q)."&brandFilter=".urlencode($row["brand"]);
}
print "<a href='".$brandHREF."'>".$row["brand"]."</a> |";
}
?>
Secondly, you need to modify the links generated in html/navigation.php to look to see if brandFilter is set, and add the parameter, for example:
<?php
if ($rewrite)
{
$prevHREF = $prevPage.".html";
// next line is the new code
if ($_GET["brandFilter"]) $prevHREF .= "?brandFilter=".urlencode($_GET["brandFilter"]);
}
?>
There are 3 more instances where a similar modification needs to be made; search for $pageOneHREF, $pageHREF and $nextHREF. The mod is almost the same, just use the appropriate variable in place of $prevHREF as used in the example...
Hope this helps!
Cheers,
David.
hi david
I have been using this mod for some time and it works great, however as the number of items have grown so as the list of brand names,and it can look a little over powering for customers, I tried to use the form mod above and it did not work, at present the mod is in user_search_results_before.php and the form one was under html/banner.php ,
I tried both, both gave errors. Would this mod work for the latest version and would there be any other solution that may look less in your face to customers. An example http://www.4utoday.co.uk/category/Womens/, this is not the largest.
I hope this makes sense.
thanks
Brent
Hi Brent,
Can you post the code you've added to user_search_results_before.php and i'll take a look. If you could also post the error messages you get that will help too...
Cheers,
David.
hi
The code was from above
<?php
if ($parts[0] == "category")
{
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."'";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brands[$row["brand"]] = $row["brand"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("brandFilter",$brands,$_GET["brandFilter"],"");
print "<input type='hidden' name='q' value='".urlencode($q)."' />";
print "<input type='submit' value='Filter By Brand' />";
print "</form>";
}
?>the error was
Fatal error: Call to undefined function: widget_selectarray() in /home/utodayno/public_html/html/user_searchresults_before.php on line 15
Hi Brent,
I think the only issue that it requires the widget.php include file which is not included by default.
To fix this, at the top of the file you have modified add:
require("includes/widget.php");That should do the trick!
Cheers,
David.
hi david
Done that also put in code to A-Z form comes up but it seems to have a glitch, please see http://www.4utoday.co.uk/category/Cleaning/
The code used is
<?php
require("includes/widget.php");
if ($parts[0] == "category")
{
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."' ORDER BY brand";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brands[$row["brand"]] = $row["brand"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("brandFilter",$brands,$_GET["brandFilter"],"");
print "<input type='hidden' name='q' value='".urlencode($q)."' />";
print "<input type='submit' value='Filter By Brand' />";
print "</form>";
}
?> When you try to go to another brand it shows blabk page, it seems to have inserted an extra character.
Thanks
Brent
Hi Brent,
I'm getting "Server not found..." on:
http://www.4utoday.co.uk/category/Cleaning/
Is that the correct URL?
Cheers,
David.
hi
Yes, just tried it at this end and it works OK
Brent
hi david
Its OK found the glitch, it was the same problem someone else had above.
Thanks for the help
Brent
It would not work with the line of code as below
print "<input type='hidden' name='q' value='".urlencode($q)."' />";but when changed to
print "<input type='hidden' name='q' value='".($q)."' />";Hi David,
I have just implemented this mod and have added an extra bit of code to correct the navigation when there is more than one page when the filter has been selected.
The change is in html/navigation.php.
Change this...
$pageHREF = "?q=".urlencode($q)."&page=".$i."&sort=".$sort;if ($_GET["brandFilter"]){
$pageHREF = "?q=".urlencode($q)."&page=".$i."&sort=".$sort."&brandFilter=".urlencode($_GET["brandFilter"]);
} else {
$pageHREF = "?q=".urlencode($q)."&page=".$i."&sort=".$sort;
}Thanks, Simon
Is it also possible to filter by price?
And behind the filter a total count of the products?
THx
HEnk
Hello Henk,
Do you remember implementing the min / max price search mods? That should enable you to filter by price if the changes are incorporated into this site.
I'm not sure what you mean by a count of the total products behind the filter - can you post an example of what you want to display?
I know you're using a heavily modified search.php, so it might help if you could email me your latest version then I can refer to that in my replies...!
Cheers,
David.
i am starting a new one again, so its not very modded.
what i want is a menu like this one:
< € 50 (1)
€ 50 - € 100 (6)
€ 100 - € 200 (109)
€ 200 - € 300 (284)
€ 300 - € 400 (95)
€ 400 - € 500 (65)
€ 500 - € 750 (59)
€ 750 - € 1000 (36)
€ 1000 - € 2000 (60)
€ 2000 - € 3000 (17)
€ 3000 - € 4000 (6)
€ 4000 - € 5000 (5)
€ 5000 > (5)Cheers HEnk
Hi Henk,
Thanks for that. I'm not sure about the practicalities of doing this on a per-search basis. This sort of range + number drop down is generally only done on a static basis against the entire data set - i.e. the ranges are calculated whenever the database is updated.
In it's simplest form it requires as many queries as there are ranges you want to support, and each query has to scan the entire table; but it should be possible to get someway towards the effect you want with a summary query. I'll look into it for you.
Cheers,
David.
Hi i have now a brandfilter and a model ( sub ) filter when you follow the link you see it in action.
is it possible to filter the sub when you click on the brand. (So its only showing the models thats links to the brand)
HEnk
Hello Henk,
Sure - without seeing your code i'm assuming that your model filter is simply based on all products, using a query something like this:
$sql = "SELECT model FROM products ORDER BY model";All you should need to do is test for $_GET["brandFilter"], and if it is set, use this value in the SQL to restrict the model selection to the selected brand, for example:
if ($_GET["brandFilter"])
{
$sql = "SELECT model FROM products WHERE brand='".database_safe($_GET["brandFilter"])."' ORDER BY model";
}
else
{
$sql = "SELECT model FROM products ORDER BY model";
}That should do the trick!
Cheers,
David.
Sry here is my code:
$sql = "SELECT DISTINCT sub FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."' ORDER BY sub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subs[$row["sub"]] = $row["sub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("subFilter",$subs,$_GET["subFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter model' />";
print "</form>";Her is my code for the filter model.
Hi Henk,
The mods should be something like this to filter the model (sub) list based on the brand filter:
if ($_GET["brandFilter"])
{
$sql = "SELECT DISTINCT sub FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."' AND brand='".database_safe($_GET["brandFilter"])."' ORDER BY sub";
}
else
{
$sql = "SELECT DISTINCT sub FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."' ORDER BY sub";
}
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subs[$row["sub"]] = $row["sub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("subFilter",$subs,$_GET["subFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter model' />";
print "</form>";Hope this helps!
Cheers,
David.
Hi Henk,
Do you mean your model (sub) select box?
If so, I can see why it is empty on the merchant page. The query is currently SELECTing based on category; whereas on the merchant page $parts[1] is the merchant name. It should work by changing the code as follows; and this will then work on the category, merchant AND brand pages:
switch($parts[1])
{
case "merchant":
$where = "merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where = "category = '".database_safe($parts[1])."'";
break;
case "brand":
$where = "brand = '".database_safe($parts[1])."'";
break;
}
if (($parts[1] != "brand") && $_GET["brandFilter"])
{
$where .= " AND brand='".database_safe($_GET["brandFilter"])."' ";
}
$sql = "SELECT DISTINCT sub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY sub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subs[$row["sub"]] = $row["sub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("subFilter",$subs,$_GET["subFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter model' />";
print "</form>";Cheers,
David.
Hi i have now an error on this page:
Link this is because its more then 3 letter word
And an error on this page:
it doesnt link forward to the selected filter.
Thx HEnk
Hi Henk,
Sorry - it has to be modified for the pages that aren't merchant, category or brand, and also I used $parts[1] in the switch() it should be $parts[0]...
Also, it looks like you have 2 versions of the code now - can you remove the old one and just have this:
switch($parts[0])
{
case "merchant":
$where = "merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where = "category = '".database_safe($parts[1])."'";
break;
case "brand":
$where = "brand = '".database_safe($parts[1])."'";
break;
default:
$where = "1";
break;
}
if (($parts[1] != "brand") && $_GET["brandFilter"])
{
$where .= " AND brand='".database_safe($_GET["brandFilter"])."' ";
}
$sql = "SELECT DISTINCT sub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY sub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subs[$row["sub"]] = $row["sub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("subFilter",$subs,$_GET["subFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter model' />";
print "</form>";Cheers,
David.
Hello Henk,
I see one little problem - it was selecting empty models (subs), so I have fixed that with the modified version below.
On the search page, it is doing what the code below will do, but because there is no merchant, category or brand selected it is selecting EVERY model in the database, and is therefore quite slow.
The safest thing to do is probably to not do this for normal searches, so use the following code altogether:
if ($parts[1])
{
$where = "sub <> ''";
switch($parts[0])
{
case "merchant":
$where = " AND merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where = " AND category = '".database_safe($parts[1])."'";
break;
case "brand":
$where = " AND brand = '".database_safe($parts[1])."'";
break;
}
if (($parts[1] != "brand") && $_GET["brandFilter"])
{
$where .= " AND brand='".database_safe($_GET["brandFilter"])."' ";
}
$sql = "SELECT DISTINCT sub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY sub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subs[$row["sub"]] = $row["sub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("subFilter",$subs,$_GET["subFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter model' />";
print "</form>";
}That should work better.
Cheers,
David.
Sorry Henk,
I forgot to change the "=" to ".=" after adding the last change.. Try:
if ($parts[1])
{
$where = "sub <> ''";
switch($parts[0])
{
case "merchant":
$where .= " AND merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where .= " AND category = '".database_safe($parts[1])."'";
break;
case "brand":
$where .= " AND brand = '".database_safe($parts[1])."'";
break;
}
if (($parts[1] != "brand") && $_GET["brandFilter"])
{
$where .= " AND brand='".database_safe($_GET["brandFilter"])."' ";
}
$sql = "SELECT DISTINCT sub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY sub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subs[$row["sub"]] = $row["sub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("subFilter",$subs,$_GET["subFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter model' />";
print "</form>";
}Fingers crossed!
Cheers,
David.
Hi Henk,
I'm not quite sure what you mean - do you want another drop-down with categories?
Yes Sry, only now not the whole list of categories whats in the database but filtered.
Thx
HEnk
Hi, almost done the categories.
Only one thing, when click on the link there are three options, filter sub, filter brand, filter brand.
what i want is when you filter sub, the others must change, you have already don it with the brand filter.
link
Here is the code:
<? if ($parts[1])
{
$where = "catsub <> ''";
switch($parts[0])
{
case "merchant":
$where .= " AND merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where .= " AND category = '".database_safe($parts[1])."'";
break;
case "brand":
$where .= " AND brand = '".database_safe($parts[1])."'";
break;
}
if (($parts[1] != "brand") && $_GET["brandFilter"])
{
$where .= " AND brand='".database_safe($_GET["brandFilter"])."' ";
}
$sql = "SELECT DISTINCT catsub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY catsub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subcats[$row["catsub"]] = $row["catsub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("catsubFilter",$subcats,$_GET["catsubFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter Subcategorie' />";
print "</form>";
}
?>
</p>
<p>
<?php
if ($parts[0] == "category")
{
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."'";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brands[$row["brand"]] = $row["brand"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("brandFilter",$brands,$_GET["brandFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter By Brand' />";
print "</form>";
}
?>
</p>
<p>
<? if ($parts[1])
{
$where = "sub <> ''";
switch($parts[0])
{
case "merchant":
$where .= " AND merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where .= " AND category = '".database_safe($parts[1])."'";
break;
case "brand":
$where .= " AND brand = '".database_safe($parts[1])."'";
break;
}
if (($parts[1] != "brand") && $_GET["brandFilter"])
{
$where .= " AND brand='".database_safe($_GET["brandFilter"])."' ";
}
$sql = "SELECT DISTINCT sub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY sub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subs[$row["sub"]] = $row["sub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("subFilter",$subs,$_GET["subFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter model' />";
print "</form>";
}
?>
Thx HEnk
Hello Henk,
I think i'm with you here. Try something like this - it makes it consistent for all 3 filters:
<p>
<?
if ($parts[1])
{
$where = "";
switch($parts[0])
{
case "merchant":
$where .= " merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where .= " category = '".database_safe($parts[1])."'";
break;
case "brand":
$where .= " brand = '".database_safe($parts[1])."'";
break;
}
if ($_GET["brandFilter"])
{
$where .= " AND brand='".database_safe($_GET["brandFilter"])."' ";
}
if ($_GET["subFilter"])
{
$where .= " AND sub='".database_safe($_GET["subFilter"])."' ";
}
$sql = "SELECT DISTINCT catsub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY catsub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subcats[$row["catsub"]] = $row["catsub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("catsubFilter",$subcats,$_GET["catsubFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter Subcategorie' />";
print "</form>";
}
?>
</p>
<p>
<?php
if ($parts[1])
{
$where = "";
switch($parts[0])
{
case "merchant":
$where .= " merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where .= " category = '".database_safe($parts[1])."'";
break;
case "brand":
$where .= " brand = '".database_safe($parts[1])."'";
break;
}
if ($_GET["catsubFilter"])
{
$where .= " AND catsub='".database_safe($_GET["catsubFilter"])."' ";
}
if ($_GET["subFilter"])
{
$where .= " AND sub='".database_safe($_GET["subFilter"])."' ";
}
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY brand";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brands[$row["brand"]] = $row["brand"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("brandFilter",$brands,$_GET["brandFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter By Brand' />";
print "</form>";
}
?>
</p>
<p>
<?php
if ($parts[1])
{
$where = "";
switch($parts[0])
{
case "merchant":
$where .= " merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where .= " category = '".database_safe($parts[1])."'";
break;
case "brand":
$where .= " brand = '".database_safe($parts[1])."'";
break;
}
if ($_GET["catsubFilter"])
{
$where .= " AND catsub='".database_safe($_GET["catsubFilter"])."' ";
}
if ($_GET["brandFilter"])
{
$where .= " AND brand='".database_safe($_GET["brandFilter"])."' ";
}
$sql = "SELECT DISTINCT sub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY sub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subs[$row["sub"]] = $row["sub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("subFilter",$subs,$_GET["subFilter"],"");
print "<input type='hidden' name='q' value='".($q)."' />";
print "<input type='submit' value='Filter model' />";
print "</form>";
}
?>Hope this helps!
Cheers,
David.
Almost, thx.
But :) there is a problem with brands who are in differents categories so the filter doesn't work.
Hope you have a sollution for this .
THX
HENK
Hello Henk,
Can you give an example? What should I select in the boxes and then what is wrong on the next page...
Thanks,
David.
Hi, 'differents categories' must be sub
ok select
haardroger -( hairdryer ) filter
then select brand braun filter.
its shows all braun brands products.
Thx HEnk
Hello Henk,
I understand... I think this can be done by adding the other 2 filter values as hidden fields in each form... try this:
<p>
<?
if ($parts[1])
{
$where = "";
switch($parts[0])
{
case "merchant":
$where .= " merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where .= " category = '".database_safe($parts[1])."'";
break;
case "brand":
$where .= " brand = '".database_safe($parts[1])."'";
break;
}
if ($_GET["brandFilter"])
{
$where .= " AND brand='".database_safe($_GET["brandFilter"])."' ";
}
if ($_GET["subFilter"])
{
$where .= " AND sub='".database_safe($_GET["subFilter"])."' ";
}
$sql = "SELECT DISTINCT catsub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY catsub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subcats[$row["catsub"]] = $row["catsub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("catsubFilter",$subcats,$_GET["catsubFilter"],"");
print "<input type='hidden' name='brandFilter' value='".htmlentities($_GET["brandFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='subFilter' value='".htmlentities($_GET["subFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='q' value='".htmlentities($q,ENT_QUOTES,$config_charset)."' />";
print "<input type='submit' value='Filter Subcategorie' />";
print "</form>";
}
?>
</p>
<p>
<?php
if ($parts[1])
{
$where = "";
switch($parts[0])
{
case "merchant":
$where .= " merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where .= " category = '".database_safe($parts[1])."'";
break;
case "brand":
$where .= " brand = '".database_safe($parts[1])."'";
break;
}
if ($_GET["catsubFilter"])
{
$where .= " AND catsub='".database_safe($_GET["catsubFilter"])."' ";
}
if ($_GET["subFilter"])
{
$where .= " AND sub='".database_safe($_GET["subFilter"])."' ";
}
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY brand";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brands[$row["brand"]] = $row["brand"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("brandFilter",$brands,$_GET["brandFilter"],"");
print "<input type='hidden' name='catsubFilter' value='".htmlentities($_GET["catsubFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='subFilter' value='".htmlentities($_GET["subFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='q' value='".htmlentities($q,ENT_QUOTES,$config_charset)."' />";
print "<input type='submit' value='Filter By Brand' />";
print "</form>";
}
?>
</p>
<p>
<?php
if ($parts[1])
{
$where = "";
switch($parts[0])
{
case "merchant":
$where .= " merchant = '".database_safe($parts[1])."'";
break;
case "category":
$where .= " category = '".database_safe($parts[1])."'";
break;
case "brand":
$where .= " brand = '".database_safe($parts[1])."'";
break;
}
if ($_GET["catsubFilter"])
{
$where .= " AND catsub='".database_safe($_GET["catsubFilter"])."' ";
}
if ($_GET["brandFilter"])
{
$where .= " AND brand='".database_safe($_GET["brandFilter"])."' ";
}
$sql = "SELECT DISTINCT sub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY sub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subs[$row["sub"]] = $row["sub"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("subFilter",$subs,$_GET["subFilter"],"");
print "<input type='hidden' name='catsubFilter' value='".htmlentities($_GET["catsubFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='brandFilter' value='".htmlentities($_GET["brandFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='q' value='".htmlentities($q,ENT_QUOTES,$config_charset)."' />";
print "<input type='submit' value='Filter model' />";
print "</form>";
}
?>Cheers!
David.
SUPER !!!
Thx
its real fun now to see that some big i mean big companies doesnt have there product sorted :)
Thx
Henk
hi again,
a bit stupid question, how can i make it 5 rows high and link on select.
Thx HEnk
Hello Henk,
Should be straight forward to do; but both these involve changing the widget_selectArray() function. What I would do is edit includes/widget.php and create a new function called widgetSelectArray2() as follows:
function widget_selectArray2($name,$array,$default,$null_option)
{
// here are the changes to make if 5 rows high and link on select:
print "<select size='5' onchange='JavaScript:this.form.submit();' name='".$name."'>";
if (!$array[$default] && $null_option)
{
echo("<option value=''>".$null_option."</option>");
}
foreach($array as $k => $v)
{
if ($default == $k)
{
$selected = "selected='selected'";
}
else
{
$selected = "";
}
print "<option value='".htmlentities($k)."' ".$selected.">".htmlentities($v)."</option>";
}
print "</select>";
}Then, in your code above, simply change each of:
widget_selectArrayto
widget_selectArray2Cheers,
David.
Cool, and if there is no value is it possible to let it vanish.
Thx HEnk
Hi Henk,
Sure - all you need to do is bring the form HTML code inside the if (database_querySelect()) control structure, for example where you have:
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY brand";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brands[$row["brand"]] = $row["brand"];
}
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("brandFilter",$brands,$_GET["brandFilter"],"");
print "<input type='hidden' name='catsubFilter' value='".htmlentities($_GET["catsubFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='subFilter' value='".htmlentities($_GET["subFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='q' value='".htmlentities($q,ENT_QUOTES,$config_charset)."' />";
print "<input type='submit' value='Filter By Brand' />";
print "</form>";...change this to:
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY brand";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brands[$row["brand"]] = $row["brand"];
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray("brandFilter",$brands,$_GET["brandFilter"],"");
print "<input type='hidden' name='catsubFilter' value='".htmlentities($_GET["catsubFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='subFilter' value='".htmlentities($_GET["subFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='q' value='".htmlentities($q,ENT_QUOTES,$config_charset)."' />";
print "<input type='submit' value='Filter By Brand' />";
print "</form>";
}That will do the trick!
Cheers,
David.
uhm it doesnt vanish the the table.
$sql = "SELECT DISTINCT catsub FROM `".$config_databaseTablePrefix."products` WHERE ".$where." ORDER BY catsub";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$subcats[$row["catsub"]] = $row["catsub"];
}
print "<form method='GET' action='".$config_baseHREF."search.php'>";
widget_selectArray2("catsubFilter",$subcats,$_GET["catsubFilter"],"");
print "<input type='hidden' name='brandFilter' value='".htmlentities($_GET["brandFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='subFilter' value='".htmlentities($_GET["subFilter"],ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='q' value='".htmlentities($q,ENT_QUOTES,$config_charset)."' />";
print "</form>";
}
}
?>i changed it to this.
HEnk
Hi Henk,
With the code above, it should only show the catsubFilter form if there are items in the table, because database_querySelect($sql,$rows) should return 0 if there is nothing to display.
Is that not happening? Can you send me a link to page if not and your script - i'll take a look...
Cheers,
David.
Hi,
Is it possible to count the products in the filter and show it.
Thx Henk
Hi Henk,
It certainly is in theory; but I wouldn't recommend this in practice because of the number of queries required. - one count(*) query requiring a full table scan (only one index can be used at a time in SQL) per item per filter...
Cheers,
David.
hi,
i implemented this code.
<?php
{
print "Filter by Brand: ";
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."'";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brandHREF = $config_baseHREF."search.php?q=".urlencode($q)."&brandFilter=".urlencode($row["brand"]);
print "<a href='".$brandHREF."'>".$row["brand"]."</a> | ";
}
}
}
?>is it possible to have a URL friendly output for the mod above?
Hi Atman,
Generic search queries don't have a re-write version i'm afraid... and since you would require the brand filter anyway, there's not really much to be gained in this instance... Sorry!
Cheers,
David.
Hiya,
Have a go with this:
<?php
{
$max = 5;
$cur = 0;
print "Filter by Brand: ";
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."'";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brandHREF = $config_baseHREF."search.php?q=".urlencode($q)."&brandFilter=".urlencode($row["brand"]);
print "<a href='".$brandHREF."'>".$row["brand"]."</a> | ";
$cur++;
if ($cur==$max && !$_GET["more"])
{
$href = $_SERVER["QUERY_STRING"];
if ($href) { $href .= "&more=1"; } else { $href = "?more=1"; }
print "<a href='".$href."'>More...</a>";
break;
}
}
}
}
?>
Cheers,
David.
thanks again! :)
Can the result be sorted with the first 5 brands with the most number of products?
The brands with few products should go last or on the MORE page.
regards,
atman
Sure - but keep an eye on performance if you have a large number of products. To give it a go, replace:
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."'";with:
$sql = "SELECT brand, COUNT(*) AS count FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."' GROUP BY brand ORDER BY count DESC";Cheers,
David.
Hi David,
I would be interested to know more on how to create the 'price range' based search refinements. You have mentioned either doing this at import time or by summary queries. Could you paste up some code pointing me in the right direction please, or if anyone else has done this, maybe some help?
Cheers.
Keeop
Hi Keeop,
Do you mean the min/max price mods? This is essentially just the following code added to the top of search.php
$minPrice = ($_GET["minPrice"]?sprintf("%.2f",$_GET["minPrice"]):"");
$maxPrice = ($_GET["maxPrice"]?sprintf("%.2f",$_GET["maxPrice"]):"");
if ($minPrice || $maxPrice)
{
if ($minPrice && $maxPrice)
{
$priceWhere = " AND price BETWEEN '".$minPrice."' AND '".$maxPrice."' ";
}
elseif ($minPrice)
{
$priceWhere = " AND price > '".$minPrice."' ";
}
elseif ($maxPrice)
{
$priceWhere = " AND price < '".$maxPrice."' ";
}
}...and then $priceWhere is appended to the normal WHERE clause in of the search SQL statements. The minPrice and maxPrice variables also need to be added to the sort links, and to the previous / next page links in html/navigation.php. I'll email you the modified versions of the distribution copies of these files to look at for the full picture...
Cheers,
David.
Hi David,
Sorry, I was looking more at a method for refining the search based on price bands, as Henk was after, i.e.
£0 - £50
£51 - £100
£101 - £150
etc......
Obviusly this will need the min/max mod to work, so I'll have a gander at that, but earlier in this thread you mentioned that you could generate a breakdown of this type either batch-style when the database is updating, or in real time using summary queries. I am curious as to how to get this working - either way. I can probably have a stab at it but I expect I'll end up with a hundred queries, heavily nested loops, and generally some pretty inefficient code so I'm hoping you can point me in the right direction to begin with.
Cheers.
Keeop
Hi,
What I suggested before was a simple method to combine the min and max price into a single variable, which can be submitted easily from a drop-down form (with the form also re-submitting $q and $sort). In the min/max price mods I sent you, in place of:
$minPrice = ($_GET["minPrice"]?sprintf("%.2f",$_GET["minPrice"]):"");
$maxPrice = ($_GET["maxPrice"]?sprintf("%.2f",$_GET["maxPrice"]):"");...use this instead:
$minPrice = ($_GET["minPrice"]?sprintf("%.2f",$_GET["minPrice"]):"");
$maxPrice = ($_GET["maxPrice"]?sprintf("%.2f",$_GET["maxPrice"]):"");
if ($_GET["minmaxPrice"])
{
$parts = explode("|",$_GET["minmaxPrice"]);
$minPrice = $parts[0];
$maxPrice = $parts[1];
}...and then, your price range form can be done as follows:
<?php
print "<form method='GET' action='".$config_baseHREF."search.php'>";
print "<input type='hidden' name='q' value='".htmlentities($q,ENT_QUOTES,$config_charset)."' />";
print "<input type='hidden' name='sort' value='".$sort."' />";
print "<select name='minmaxPrice'>";
$options = array();
$options["0.00|50.00"] = "£0 - £50";
$options["50.01|100.00"] = "£50 - £100";
$options["100.01|200.00"] = "£100 - £200";
$options["200.01"] = "£200+";
foreach($options as $k => $v)
{
print "<option value='".$k."' ".($_GET["minmaxPrice"]==$k?"selected='selected'":"").">".$v."</option>";
}
print "</select>";
print "</form>";
?>
Hope this helps!
Cheers,
David.
Thanks David,
I think I'm trying to be more awkward in that I would like to create the price ranges dynamically so would need to query the database to get the min and max values for a particular data set within a particular range. So, for example, I'm selecting data like this:
$sqlprice = "SELECT MIN( price ) AS minPrice FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."' ".$where." GROUP BY name ORDER BY price ASC";Now I can do some calculations based on the min prices to produce my ranges, which I haven't quite finished as yet! So, if I have some resultant data like this:
minPrice
1.99
2.99
3.01
3.99
4.51
5.01
...I would need to split these in to ranges. There's only a few values here for simplicity so let's just say I make the following ranges:
£0.00 - £3.49 (3)
£3.50 - £4.99 (2)
£5.00 - £5.99 (1) = number of products in range
....what's the best way of adding those rows up that are in these ranges to get the number of products per range? I think you said before that you need to run a query against each range to find the number of rows and this could be very slow but you also mentioned a way of doing it with summary queries? This is really the bit I need help with, the best way to get a count of products per range. I think I can have a crack at the rest now I have the mod, thanks.
Thanks!
Keeop
Hi Keeop,
Does this logic sound correct;
For each search:
1) Query the min(price) and max(price) for the search term
2) Derive a suitable array of price brackets
3) For each price bracket, query the number of products in that bracket
4) Generate the price range drop-down
Cheers,
David.
Hi David,
I'm just using min(price) as the lowest price is all the customer's probably interested in and then ordering the min(price) to, in effect, get a min and max price. But yes, apart from that, that's the logic, the only thing being I probably won't be using a drop-down, just a 'li' list of the various ranges so that I can 'div' about with them!
This is the query I'm using to generate the list of prices. Does this seem the best method?
$sqlprice = "SELECT MIN( price ) AS minPrice FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."' ".$where." GROUP BY name ORDER BY minPrice ASC";Cheers.
Keeop
Hi,
There's probably no need for GROUP BY name, so that will make they query more efficient (however MySQL may be ignoring it anyway) - otherwise that looks fine!
Cheers,
David.
Hi David,
I was using the 'Group By' so that when I did a product count I only counted unique products. Mind you, I haven't worked out the best way of doing that yet so I might not need it. How would you recommend counting the products?
Cheers.
Keeop
Ah - I understand now - in that case yes, use GROUP BY...! And you should be able to use COUNT(id) to get the count...
$sqlprice = "SELECT COUNT(*) AS numProducts, MIN( price ) AS minPrice FROM `".$config_databaseTablePrefix."products` WHERE category = '".database_safe($parts[1])."' ".$where." GROUP BY name ORDER BY minPrice ASC";Cheers,
David.
Hi David,
Adding the COUNT will only give me a count of the produts though and will also skew the count as there will be multiple entries for the same product from different merchants. I really need to count the number of actual rows returned based on their price value. Any ideas?
Cheers.
Keeop
Hi,
I don't think that's possible in a summary query i'm afraid, it would have to be done after the price bands had been established, with one query for each band, e.g.
SELECT COUNT(*) FROM products WHERE (same_where_clause_as_search) AND price BETWEEN 100.00 AND 199.99Cheers,
David.
Hi David,
Thanks. I'll try it and see what sort of performance hit I get. Apart from that, I've got it all working based on a slected 'category' but was wondering how much harder it would be to do the same sort of thing based on just a search string and on 'brand'. i.e., if someone searches for the word 'shoe' to then bring up the brands, price ranges and maybe categories that contain that key word. Plus, the same thing for searching a brand, which would then bring up a list of categories and price ranges for all items of that selected brand.
Does this seem feasible or is it just a world of pain?!
Cheers.
Keeop
Hi Keeop,
It should work fine on a niche site - exactly the same SQL, just replacing the WHERE clause with exactly the WHERE clause used in the query that generated the search results.
To make this easier, it might be worthwhile slightly restructuring the section of code in search.php where the main product selection SQL is generated (the block beginning with switch($parts[0]) on line 34) so that the WHERE clause is constructed into a single variable name, and then you can use the variable later on in your price range selection code...
Cheers,
David.
Hi David,
Yes, it's getting a bit cumbersome now as I'm pretty much duplicating the whole of search.php to do the refinements. Because of this, I was thinking of maybe streamlining search.php a tad. I'm wondering if there's any specific reason you coded the search engine to use a colon as a separator and use exploded parts rather than just using separate variables in the http string?
i.e.
search.php?q=category:shoessearch.php?q=&category=shoesI'm just thinking I could maybe streamline the search query to just having one query and building up the 'where' clause by GETting any variables in the url. This would then be easier to replicate in my refinement search and should bring back exactly the same results.
It's just at the moment if I search on brand and refine by category I would need a string like:
search.php?q=brand:jones&category=shoessearch.php?q=category:shoes&brand=jonesNow, if I just had a generic query checking for variables, I would only need the one query:
search.php?q=&brand=jones&category=shoesDoes this make sense? Sorry, it's a bit of a ramble. What do you think?
Cheers.
Keeop
Hi Keeop,
The benefit of keeping it to one parameter is that it is only that one parameter that needs to be propagated to subsequent pages, and in the links to the change sort order etc.
Something I have helped another user with in the past, when they wished to limit down by additional fields (i.e. to specify a category and a brand) is to extend the reach of the "colon" method to support a query such as:
merchant:Some Merchant:brand:Some Brand:category:Some Category
Perhaps the best way would be to re-structure this section of the search code to make the format of the query totally flexible (i.e. they can appear in any order). To have a go at this, in place of the following code (line 41 of search.php):
$where = " ".$parts[0]."='".database_safe($parts[1])."' ";...try something like this:
for($i=0;$i<=count($parts);$i+=2)
{
$where .= " `".database_safe($parts[$i])."` = '".database_safe($parts[($i+1)])."' ";
}Cheers,
David.
Hi David,
Makes sense. That does lead on to another thing I was thinking about and that was dumping all of the ordering information in to a session cookie and doing it that way rather than putting it in the url.
Hmm...I'll have a think!
Cheers.
Keeop
Hi David,
Just got another question for you with regards to all this.
I don't know if I'll keep with it as there are now a shed load of extra queries being run, so what I'm wanting to know is, if you know of any way of caching the results to make the whole process a little less intensive? Any ideas as to whether something can be done?
Cheers.
Keeop
Hi Keeop,
MySQL is generally pretty good at caching results so if there are sufficient resources available the difference between submitting duplicate queries to MySQL and making your own cache of the results should be negligible, but of course if there are minor differences each time it all has to be queried again. And the more traffic you get, the quicker the cache is exhausted / refreshed etc.
It might be worth looking at a compromise, so rather than displaying the actual number of products within each price range - just display the price ranges- as users will soon select another range if there are no results within the range selected (depending on their budget, of course!)
Cheers,
David.
Hi David,
OK, I guess I'll just have to keep an eye on it. Thanks.
I've put an extra config variable in so that I can easily switch off the counting if required and it's only the count in the 'price range' section that requires extra queries. I'm just concerned at the extra queries as a whole, but we'll see!
Cheers.
Keeop
Hi David,
I've implemented a 'refine search' function on one of my web sites which seems to work pretty well - it's even quick when doing the products counts! However, there are two problems with it:
a.) it's bloated - I've added 34Kb's worth of code which I am sure could be cut right down by a pro!
b.) it's too restricted. At the moment, the search is confined to keyword, category, brand and three subcategories. I would ideally have this code more generic so that if I decide to search through or refine through more fields, they could easily be added - at the moment I would have to rewrite the whole thing!
I know earlier in this thread and also in another thread (this one? http://www.pricetapestry.com/node/2779) you mention a possible method to easily search within extra fields and to propagate this throughout the script.
Anyway, could I possibly email you the code I've come up with so you can take a look?
Cheers.
Keeop
Hi Keeop,
Yes - the trick is to combine all parts into a single value of $q, so that it can be propagated to the next pages, sort order etc. without having to add lots of new fields each time. It's a modification to the section of search.php that handles the merchant: brand: category: searches etc., in order to make it support a query such as:
merchant:Merchant Name:brand:Brand Name:category:Category Name:keywords(or any combination thereof)
The new code below needs to replace the section of code immediately after the following section:
case "merchant":
// pass through to category
case "category":
// pass through to brand
case "brand":...all the way down to the break; statement. Here's the new version:
$wheres = array();
foreach($parts as $k => $part) if (!$part) unset($parts[$k]);
$j = count($parts);
for($i=0;$i<$j-1;$i+=2)
{
if ($validFields[$parts[$i]]) $wheres[] = " `".$parts[$i]."` = '".database_safe($parts[$i+1])."' ";
}
$where = implode("AND",$wheres);
if ($j%2) $where .= " AND search_name LIKE '%".database_safe(tapestry_search($parts[$j-1]))."%' ";
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere;
$orderBySelection = $orderByDefault;Hope this helps!
Cheers,
David.
Hi David,
First, thanks for the great script. Everything I want (so far :-) is possible.
I put the above mod into the site, and it works. Just before that i put the category field into search, similar as discussed here: http://www.pricetapestry.com/node/1224. I used an empty database and reregistred the feeds. It worked.
After i put up the above mode this extra search function doesn't work anymore.
Here is the code of the search.php:
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH (name,category) AGAINST ('".database_safe($parts[0])."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH (name,category) AGAINST ('".database_safe($parts[0])."') GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH (name,category) AGAINST ('".database_safe($parts[0])."')";Do you know whats wrong?
Hi drBas,
Thank you for your comments!
It sounds like the full text index including name and category hasn't been built.
The thread you referred to contains a makeIndex.php script as shown below;
modified to add the category instead of the description field....
<?php
set_time_limit(0);
ignore_user_abort();
require("includes/common.php");
$sql =
"CREATE FULLTEXT INDEX namecategory ON ".$config_databaseTablePrefix."products (name,category)";
database_queryModify($sql,$result);
print "Done.";
?>
If you run that from your main Price Tapestry installation folder (just once),
that should be all it is...
Cheers,
David.
Hi David,
Thats exactly what i did. And no luck.
Tried it just now again and still it doesn't include the category field to search.
Hi,
First things first; could you run the following script (same place as makeIndex.php)
and let me know what is displayed...
showIndex.php
<?php
set_time_limit(0);
ignore_user_abort();
require("includes/common.php");
header("Content-Type: text/plain");
$sql =
"SHOW INDEX FROM ".$config_databaseTablePrefix."products";
database_querySelect($sql,$result);
print_r($result);
?>
Cheers,
David.
Sorry my bad.
The correct display is:
Array
(
[0] => Array
(
[Table] => products
[Non_unique] => 0
[Key_name] => PRIMARY
[Seq_in_index] => 1
[Column_name] => id
[Collation] => A
[Cardinality] => 1275
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
)
[1] => Array
(
[Table] => products
[Non_unique] => 0
[Key_name] => dupe_filter
[Seq_in_index] => 1
[Column_name] => dupe_hash
[Collation] => A
[Cardinality] => 1275
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
)
[2] => Array
(
[Table] => products
[Non_unique] => 1
[Key_name] => merchant
[Seq_in_index] => 1
[Column_name] => merchant
[Collation] => A
[Cardinality] =>
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
)
[3] => Array
(
[Table] => products
[Non_unique] => 1
[Key_name] => name
[Seq_in_index] => 1
[Column_name] => name
[Collation] => A
[Cardinality] =>
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
)
[4] => Array
(
[Table] => products
[Non_unique] => 1
[Key_name] => search_name
[Seq_in_index] => 1
[Column_name] => search_name
[Collation] => A
[Cardinality] =>
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
)
[5] => Array
(
[Table] => products
[Non_unique] => 1
[Key_name] => category
[Seq_in_index] => 1
[Column_name] => category
[Collation] => A
[Cardinality] =>
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
)
[6] => Array
(
[Table] => products
[Non_unique] => 1
[Key_name] => brand
[Seq_in_index] => 1
[Column_name] => brand
[Collation] => A
[Cardinality] =>
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => BTREE
[Comment] =>
)
[7] => Array
(
[Table] => products
[Non_unique] => 1
[Key_name] => name_2
[Seq_in_index] => 1
[Column_name] => name
[Collation] =>
[Cardinality] =>
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => FULLTEXT
[Comment] =>
)
[8] => Array
(
[Table] => products
[Non_unique] => 1
[Key_name] => namedescription
[Seq_in_index] => 1
[Column_name] => name
[Collation] =>
[Cardinality] =>
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => FULLTEXT
[Comment] =>
)
[9] => Array
(
[Table] => products
[Non_unique] => 1
[Key_name] => namedescription
[Seq_in_index] => 2
[Column_name] => description
[Collation] =>
[Cardinality] =>
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => FULLTEXT
[Comment] =>
)
[10] => Array
(
[Table] => products
[Non_unique] => 1
[Key_name] => namecategory
[Seq_in_index] => 1
[Column_name] => name
[Collation] =>
[Cardinality] =>
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => FULLTEXT
[Comment] =>
)
[11] => Array
(
[Table] => products
[Non_unique] => 1
[Key_name] => namecategory
[Seq_in_index] => 2
[Column_name] => category
[Collation] =>
[Cardinality] =>
[Sub_part] =>
[Packed] =>
[Null] =>
[Index_type] => FULLTEXT
[Comment] =>
)
)Hi,
That looks fine...
Now, the section of code you posted above that you modified in search.php;
only applies to queries that actually use the FULLTEXT index (i.e. every
word in the query is at least 4 characers);
Would this apply to the test queries that you have been making?
To apply the same changes to non full-text queries, look for the following
section of code beginning at line 89:
foreach($words as $word)
{
$wheres[] = "search_name LIKE '%".database_safe($word)."%'";
}...and REPLACE that with:
foreach($words as $word)
{
$wheres[] = "(search_name LIKE '%".database_safe($word)."%' OR category LIKE '%".database_safe($word)."%') ";
}Hope this helps...!
Cheers,
David.
Hi,
My test queries are longer than 4 characters.
One of my categories is "Dried doogfood" if i do an exact search for this term there are no results.
Hope you can help.
Hi,
I notice from the warnings above that it looks like you're running on a
local server. Is this site mirrored online anywhere that I could take
a look at...
If you could email me a link if so, and also
your modified search.php i'll check it out...
Cheers,
David.
Hi David,
How can I translate the above explanation (on how to make a brand filter in a dropdown) to the new version of pricetapestry?
Thank you.
Hi,
It would be easier to base the modification on the Filtering Results by Category described in the following thread...
http://www.pricetapestry.com/node/3112
However since there are several steps involved; perhaps the easiest thing to do would be if you could email me your search.php (in case you have already made any other modifications) I will add the brand filter code for you...
Cheers,
David.
Hi david
is there any impact to navigation.php ?
What do we need to change in this file in order that both proce bracket filter and navigation link work ?
Cheers
Guillaume
Hi Guillaume,
In search.php, look for the following code near the end of the file:
if (isset($navigation)) require("html/navigation.php");...and rather than modify every link construction in navigation.php with any filter parameters that you have created, the easiest way is to merge them with the "sort" parameter, like this:
if ($minPrice || $maxPrice)
{
$sort .= "&minPrice=".$minPrice."&maxPrice=".$maxPrice;
}
if ($_GET["brandFilter"])
{
$sort .= "&brandFilter=".urlencode($_GET["brandFilter"]);
}
if (isset($navigation)) require("html/navigation.php");Cheers,
David.
Hello David,
I'm sorry to keep you so busy.
I have added a category filter to my search.php, if I emailed it to you, would you possibly be able to add the brand filter for me, as i'm getting a bit confused between the old PT version and the new.
Regards
Hayden
Hi Hayden,
Sure I'll patch it in for you...
Cheers,
David.
Hi David,
Got everything working nicely with the BrandFilter mod but noticed that Navigation has stopped working now - Relevance, Product Rating, Low to High and High to Low.
Where's the best place to start looking at this?
THanks,
Neil
Hi Neil,
It's straight forward to patch into the follow on links - if you could
email me your search.php I'll modify it for you...
Cheers,
David.
This mod is great however, there's so much to this thread that I'm lost!
I just added the following to my search.php and it 'looked' fine.
{
$max = 10;
$cur = 0;
print "Filter by Brand: ";
$sql = "SELECT DISTINCT brand FROM `".$config_databaseTablePrefix."products` WHERE
category = '".database_safe($parts[1])."'";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
$brandHREF =
$config_baseHREF."search.php?q=".urlencode($q)."&brandFilter=".urlencode($row["bran
d"]);
print "<a href='".$brandHREF."'>".$row["brand"]."</a> | ";
$cur++;
if ($cur==$max && !$_GET["more"])
{
$href = $_SERVER["QUERY_STRING"];
if ($href) { $href .= "&more=1"; } else { $href = "?more=1"; }
print "<a href='".$href."'>More...</a>";
break;
}
}
}
}However, it didn't work. I think I should add something else to the top of the PHP code on search.php to make it functional but I think an earlier post here may be an older version of search.php as it didn't match up to mine.
I'd like to add two lines to my search results page, filter by brand and filter by category - two lines with 10 links each of the 10 most numerous.
tx
Hello tx,
Apologies this has become quite a complex thread - I do have a nice mod to add category/brand (and merchant/min/max price filtering but you can hide these if not desired) to the search results. If you would like to email me your search.php and html/searchresults.php i'll make the changes for you as whilst they're straight forward and quick for me to do they are quite extensive and difficult to document through the forum...
Cheers,
David.
Hi guys,
Just in case you guys wondering how to do all the modifications described above, fret not.
I have checked with David, and he mentioned that the latest version 12/10A already have the multi search functionality included. So get the latest version and use it!
The reason why i mentioned it here is because, doing a search on the forum for how to add brand and merchant search to category all kinda lead to this thread. So just to make everyone's and David's life easier!
cheers,
Henry
Thanks, Henry!
When 12/10A is released I intend to work on the forum to make it easier
to find relevant information as it's grown quite large over the years!
Cheers,
David.
--
PriceTapestry.com
Hi David
please show where to 'enable' this multi search in the latest version 12/10A.
like to try this brand filter, but don't know where to enable this function.
//Brian
Hi Brian,
I'll email you a version of search.php from 12/10A with the merchantFilter, categoryFilter and brandFilter code in place...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Can you send me over to the search.php for the 12/10A.
Thanks,
Leo
Hi David,
I'm working on a new site so it might be a good time to look at that search.php file. Can you please email it over?
Cheers.
Keeop
Hi, David can you help me with the filter on search(categoryFilter ,merchantFilter,brandFilter).
I manage to make the filter on category pages, but on search results i realy don't know where to start.
Thank U
Hi,
Sure - if you want to email me your
search.php
html/searchresults.php
...i'll add the filter code for you...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Just a quick note regarding the new search.php - can you double check the 'implode' statements as I think some may be the wrong way round?
With the new method of search/filtering, is it easy to add in extra fields to filter by?
Cheers.
Keeop
Well spotted! I've corrected for consistency in the distribution, however it appears the order of the parameters doesn't matter...!
"implode() can, for historical reasons, accept its parameters in either order. For consistency with explode(), however, it may be less confusing to use the documented order of arguments." (php.net)
Regarding filters, the easiest method is the one that I use in the sidebar filters mod, by extending the $priceWhere variable to include additional clauses. For example to add the brandFilter parameter, look for the following code at line 45:
else
{
$priceWhere = "";
}..and REPLACE with:
else
{
$priceWhere = "";
}
if ($_GET["brandFilter"]) $priceWhere .= " AND brand='".database_safe($_GET["brandFilter"])."' ";It then needs to be tagged onto the sort URL base, so look for the following code beginning at line 291:
if ($minPrice || $maxPrice)
{
$sortHREF .= "minPrice=".$minPrice."&maxPrice=".$maxPrice."&";
}...REPLACE with:
if ($minPrice || $maxPrice)
{
$sortHREF .= "minPrice=".$minPrice."&maxPrice=".$maxPrice."&";
}
if ($brandFilter)
{
$sortHREF .= "brandFilter=".urlencode($brandFilter)."&";
}...and finally for the navigation, look for the following code beginning at line 371:
if ($minPrice || $maxPrice)
{
$sort .= "&minPrice=".$minPrice."&maxPrice=".$maxPrice;
}...and REPLACE with:
if ($minPrice || $maxPrice)
{
$sort .= "&minPrice=".$minPrice."&maxPrice=".$maxPrice;
}
if ($brandFilter)
{
$sort .= "&brandFilter=".urlencode($brandFilter);
}Obviously replacing "brand" with whatever field (existing or new) you wish to filter against of course...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Probably explains why it still seemed to work OK!
Can you please email me over that sidebar mod and I'll have a play? If I get in to trouble, I may well have to ask for further assistance though, please!
Cheers.
Keeop
Hi David,
Making good progress with these, thanks. I was wondering, in the sidebar.php file I would like to include the product counts alongside the brands, categories, merchants etc. Can this be done more efficiently using the SELECT SQL_CALC_FOUND_ROWS, as in search.php, rather than running two separate queries?
Cheers.
Keeop
Hi Keeop,
I don't think so i'm afraid - the filter queries only SELECT the distinct
list of merchants/categories/brands, so SQL_CALC_FOUND_ROWS would only
return the total number of merchant/categories/brands rather than the
total number of products under those indexes...
Cheers,
David.
--
PriceTapestry.com
Hi David,
No probs. Got a strange one here. When using the full text search, the $where and $priceWhere variables seem to be disappearing and are hence empty by the time it gets to the sidebar script, but using the non-full text, it all works! Any ideas??
Cheers.
Keeop
Hi Keeop,
Sounds strange! If you're getting search results then $where must be present at the same point regardless of full text or otherwise! If you're still not sure if you could email me the relevant files I'll take a look...
Cheers,
David.
--
PriceTapestry.com
Hi,
Thanks for your comments!
It would certainly be possible to do this - it's basically the addition of an SQL "WHERE" clause when selecting from the category index.
Rather than post a load of code in one go; it will be best to tackle the implementation in stages, so that you can get each part working before it all comes together.
The first stage would be to add a new parameter to search.php that adds the appropriate WHERE clause if a brandFilter has been supplied in the URL. To do this, you can just make the following addition to search.php
case "merchant":// pass through to category
case "category":
// pass through to brand
case "brand":
$where = " ".$parts[0]."='".database_safe($parts[1])."' ";
//******************//
// NEW CODE (BEGIN) //
if ($_GET["brandFilter"])
{
$where .= "AND brand='".database_safe($_GET["brandFilter"])."' ";
}
// NEW CODE (END) //
//****************//
After making that change; test the new function by constructing an appropriate search.php URL; for example:
http://www.example.com/search.php?q=category:Jeans&brandFilter=Levis
Let me know if this works as you're expecting, then i'll work through the code required to create a drop down...