Hi there
Not too sure how to formulate this question, but here goes.
When I manage the database using phpMyAdmin, I can create a very specific SQL query search to filter results from the database to certain products etc.
Is it possible to use these SQL queries as the basis for a custom search results page?
P
Thanks David
I think that's what I need! (I'm a SQL & PHP novice). I tried your example but did not get exactly what I was after (if indeed I did it right)
Just to clarify, what I am after is to take the following SQL from phpMyAdmin, for example:
SELECT * FROM `products` WHERE (
`description` LIKE '%blue%'
AND
`description` LIKE '%baby%'
AND
`description` LIKE '%clothes%'
AND
`description` NOT LIKE '%shoes%'
)To produce a results page that shows only blue related baby clothes, but not any baby shoes. Is it possible to create a page like that, that you can just link to?
Apologies if you have in fact answered my questoin but I just can't see it.
Hi,
Sure you could do that - let's call it "preset1", so to add the query, look for the following line in search.php:
case "bw":...and REPLACE with:
case "preset1":
$where = "(
`description` LIKE '%blue%'
AND
`description` LIKE '%baby%'
AND
`description` LIKE '%clothes%'
AND
`description` NOT LIKE '%shoes%'
)";
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE ".$where;
$orderBySelection = $orderByDefault;
break;
case "bw":...and then to link to it, you would use:
search.php?q=preset1:Hope this helps!
Cheers,
David.
David
that is fantastic - EXACTLY what I was looking for.
Cheers!
P
Hi,
Sure - what you could do is add custom search handlers. If you take a look in search.php starting at line 56, you'll find the switch() statement that controls a section of code where the search SQL is constructed depending on the query. For example, when the query begins category:, brand: etc. - then one particular style of SQL is generated; bw: (the begins with operator) uses different SQL again; and finally the default case for normal queries.
It's probably easiest to use the bw: section as a template:
case "bw":$where = "search_name LIKE '".database_safe($parts[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;
break;
...simply changing the first line to the name of the operator you want to use for your custom query, for example:
case "myquery":You can then construct a WHERE clause as required using $parts[1] which is the section of the query after the ":", otherwise just set $where to "1" so that it is still compatible with the global $priceWhere variable.
Cheers,
David.