Adding FULLTEXT INDEX to custom column with an underscore

Submitted by Convergence on Thu, 2012-06-14 21:21 in Price Tapestry

v12/10B - modded

Greetings,

Need some guidance, please.

Have a custom column named 'manufacturer_model' that we added during our initial install. Would like to make this column and it's contents searchable.

However, due to the underscore, running a dbmod.php file such as the one below doesn't work:

<?php

  set_time_limit(0);

  require("includes/common.php");

  $sql = "CREATE FULLTEXT INDEX name_manufacturer_model

            ON `".$config_databaseTablePrefix."products` (name,manufacturer_model)";

  database_queryModify($sql,$result);

  print "Done.";

?>

Should we rename the column first?

alter table products change manufacturer_model manufacturermodel varchar (255) ;

Then change all the corresponding files to the new column name:

/config.advanced.php
/search.php
/html/product.php

And then, finally, run the FULLTEXT INDEX again?

<?php

   set_time_limit(0);

   require("includes/common.php");

   $sql = "CREATE FULLTEXT INDEX name_manufacturermodel

             ON `".$config_databaseTablePrefix."products` (name,manufacturermodel)";

   database_queryModify($sql,$result);

   print "Done.";

 ?>

Will feeds need to be registered again? Chances of losing any data in that column?

What would be the best route to take?

Thanks!

Hi, I don't think the

Submitted by support on Fri, 2012-06-15 09:59

Hi,

I don't think the underscore should make any difference to be honest - what I would recommend first is running the dbmod.php to create the index with database debug mode enabled, e.g.

<?php
  set_time_limit(0);
  require("includes/common.php");
  $config_databaseDebugMode = TRUE;
  $sql = "CREATE FULLTEXT INDEX name_manufacturer_model
            ON `".$config_databaseTablePrefix."products` (name,manufacturer_model)";
  database_queryModify($sql,$result);
  print "Done.";
?>

This should generate additional MySQL error information which should indicate the problem. If you're not sure from the message how to correct the situation let me know what is displayed and I'll take a look for you..

Cheers,
David.
--
PriceTapestry.com

Hi David, My concern is that

Submitted by Convergence on Fri, 2012-06-15 19:38

Hi David,

My concern is that there is already a fieldSet called "model" in the db. We actually want to do FULLTEXT INDEX for 3 additional fields including "model" and "manufacturer model" (as we as a third).

So wouldn't things get muddled with a command like:

$sql = "CREATE FULLTEXT INDEX name_manufacturer_model_model

How would it know that the _model in manufacturer_model is not the 2nd _model?

Thanks!

Hi, The actual name of the

Submitted by support on Sat, 2012-06-16 09:57

Hi,

The actual name of the index doesn't have to bear any relationship to the field names actually used to built it - that's entirely down to the ON (field1,field2,etc.) part of the SQL...

Cheers,
David.
--
PriceTapestry.com

Thanks David, We must have

Submitted by Convergence on Sat, 2012-06-16 19:24

Thanks David,

We must have messed something else up - will try again.

Thanks for the education!