0

I have migrated from myisam to innodb and i had to remove fulltext indexes from tables. now some parts of my script doesn't work, how can i convert the below code so it will work with innodb?

$posts_fields = "SELECT SQL_CALC_FOUND_ROWS id, autor, " . PREFIX . "_post.date AS newsdate, " . PREFIX ."_post.date AS date, short_story AS story, " . PREFIX . "_post.xfields AS xfields, title, descr, keywords, category, alt_name,comm_num AS comm_in_news, allow_comm, rating, news_read, flag, editdate, editor, reason, view_edit, tags, '' AS output_comms";
$posts_from = "FROM " . PREFIX . "_post";
$sql_find = "$sql_fields $posts_from $where";
DaveRandom
  • 87,921
  • 11
  • 154
  • 174
Crazy_Bash
  • 1,755
  • 10
  • 26
  • 38
  • 3
    What doesn't work anymore? The issue may well be in `$where` which is not provided, if you're using for example 'MATCH(..) AGAINST(..)'. – Rudi Visser Jul 10 '12 at 14:48
  • 3
    MySQL 5.6 supports FULLTEXT indexes on InnoDB tables: http://dev.mysql.com/tech-resources/articles/mysql56-labs-july2011.html. That being said, your PHP is useless - show us what the generated query string looks like. – Marc B Jul 10 '12 at 14:49
  • 1
    All that code does is select a field list from a table. There is no code that would be storage engine dependent, and no code that depends on an index, FULLTEXT or otherwise. Please show the error message that you get when trying to run the query. – DaveRandom Jul 10 '12 at 14:49
  • this is the error im getting `MySQL Error! ------------------------ The Error returned was: The used table type doesn't support FULLTEXT indexes Error Number: 1214 SELECT SQL_CALC_FOUND_ROWS id, autor, dle_post.date AS newsdate, dle_post.date AS date, short_story AS story, dle_post.xfields AS xfields, title, descr, keywords, category, alt_name,comm_num AS comm_in_news, allow_comm, rating, news_read, flag, editdate, editor, reason, view_edit, tags, '' AS output_comms FROM dle_post WHERE dle_post.approve=1 AND MATCH(title,short_story,full_story,dle_post.xfields) AGAINST ('test' LIMIT 0,36` – Crazy_Bash Jul 10 '12 at 18:40
  • upgrading to 5.6 solve my problem – Crazy_Bash Jul 10 '12 at 20:13

1 Answers1

0
$posts_fields = "SELECT SQL_CALC_FOUND_ROWS id, autor, " . PREFIX . "_post.date AS newsdate, " . PREFIX ."_post.date AS date, short_story AS story, " . PREFIX . "_post.xfields AS xfields, title, descr, keywords, category, alt_name,comm_num AS comm_in_news, allow_comm, rating, news_read, flag, editdate, editor, reason, view_edit, tags, '' AS output_comms";
$posts_from = "FROM " . PREFIX . "_post";
$sql_find = "$sql_fields $posts_from $where";

should be changed to 

$posts_fields = "SELECT SQL_CALC_FOUND_ROWS id, autor, " . PREFIX . "_post.date AS newsdate, " . PREFIX ."_post.date AS date, short_story AS story, " . PREFIX . "_post.xfields AS xfields, title, descr, keywords, category, alt_name,comm_num AS comm_in_news, allow_comm, rating, news_read, flag, editdate, editor, reason, view_edit, tags, '' AS output_comms";
$posts_from = "FROM " . PREFIX . "_post";
$sql_find = "$post_fields $posts_from $where";

and also double check your where condition

satdev86
  • 800
  • 7
  • 14