Hi David
[SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('Interpet No 4 Green Away 100ml') AS relevance FROM `pets_products` WHERE category = 'Pets' AND MATCH name AGAINST ('Interpet No 4 Green Away 100ml') AND name <> 'Interpet No. 4 Green Away 100ml' GROUP BY name ORDER BY relevance DESC LIMIT 3][Can't find FULLTEXT index matching the column list]
Any ideas what's causing this error on products.php
Chris
Hi David
Thanks for explaining that, I must put my hand up and admit deleting name_2 from the products table a couple weeks back after noticing a warning in phpmyadmin (More than one FULLTEXT key was created for column `name`) thinking it was not needed. So i've rebuilt the DB from scratch which seems to have fixed most errors I was getting (obviously).
Could you look at these as i'm still getting undefined index on line 7 of jump.php and undefined index on line 88 of products.php
Thanks
Chris
Hi Chris,
It looks like there have been quite a few changes on your site - please could you post the corresponding lines of each file and I'll advise accordingly...
Cheers,
David.
--
PriceTapestry.com
Hi David
In jump.php
$product = $rows[0];
In products.php
$where .= "MATCH name AGAINST ('".database_safe($q)."') AND name <> '".database_safe($product["products"][0]["name"])."'";
Chris
Hi Chris,
I assume these are coming from error logs rather than viewed pages as both of these are actually related to requests for products that no longer exist on your site, so almost certainly a result of crawlers requesting expired URLs.
If not already, make sure that jump.php is excluded in your robots.txt file, e.g.
User-Agent: *
Disallow: /jump.phpEither way, to prevent expired products resulting in a warning generated by jump.php, look for the following code at line 6:
database_querySelect($sql,$rows);...and REPLACE with:
if (!database_querySelect($sql,$rows))
{
header("HTTP/1.0 404 Not Found");
exit();
}products.php is actually designed to show related products on expired pages and this has highlighted an unhandled potential for an undefined index warning. To prevent this from resulting in further warnings in your logs, REPLACE the above line with:
$where .= "MATCH name AGAINST ('".database_safe($q)."') ";
if (isset($product))
{
$where .= " AND name <> '".database_safe($product["products"][0]["name"])."'";
}I will address both of these warning conditions in the distribution;
Cheers,
David.
--
PriceTapestry.com
Hi Chris,
The error message is indicating that for some reason the FULLTEXT indexing against `name` on its own no longer exists. Assuming that there are no errors during normal search, this would imply that you have $config_searchDescription = TRUE (config.advanced.php, line 10).
The error originates from the Related Products query, so there are 2 ways to resolve this. The easiest is probably to make the query use the same FULLTEXT index as search, so to do this, locate the following code in products.php:
$where .= "MATCH name AGAINST ('".database_safe($q)."') AND name <> '".database_safe($product["products"][0]["name"])."'";$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";
...and REPLACE with;
$where .= "MATCH name,description AGAINST ('".database_safe($q)."') AND name <> '".database_safe($product["products"][0]["name"])."'";$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name,description AGAINST ('".database_safe($q)."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name ORDER BY relevance DESC LIMIT 3";
Alternatively, the FULLTEXT index against `name` on its own can be restored with the following dbmod.php script. Create the file, upload to the main installation folder and browse to dbmod.php once, and then delete the file...
<?phprequire("includes/common.php");
$sql = "CREATE FULLTEXT INDEX name_3 ON `".$config_databaseTablePrefix."products` (name)";
database_queryModify($sql,$result);
print "Done.";
?>
Hope this helps!
Cheers,
David.
--
PriceTapestry.com