0

I'm making a PHP + MySQL app for company records management and I need an joint result from several tables based on fulltext search in every table.

The query is formed dynamically and in worst case looks so:

SELECT `contracts_info`.`contract_number`, `contracts_info`.`balance`, `contracts_info`.`conclusion_date`, `contracts_info`.`activation_date`, `contracts_info`.`deactivation_date`,
`parents`.`mother_fullname`, `parents`.`mother_email`, `parents`.`mother_phone`, `parents`.`father_fullname`, `parents`.`father_email`, `parents`.`father_phone`, `parents`.`postal_office`,
`students`.`name`, `students`.`second_name`, `students`.`surname`, `students`.`form_number`, `students`.`form_letter`,
`students_info`.`medical_features`, `students_info`.`psychological_features`
FROM contracts_info
JOIN parents USING(contract_number)
JOIN students USING(contract_number)
JOIN students_info USING(contract_number)
WHERE MATCH (`contracts_info`.`contract_number`) AGAINST (? IN BOOLEAN MODE)
OR MATCH (`parents`.`contract_number`, `parents`.`mother_fullname`, `parents`.`mother_email`, `parents`.`father_fullname`, `parents`.`father_email`) AGAINST (? IN BOOLEAN MODE)
OR MATCH (`students`.`name`, `students`.`surname`, `students`.`contract_number`) AGAINST (? IN BOOLEAN MODE)
OR MATCH (`students_info`.`medical_features`, `students_info`.`psychological_features`, `students_info`.`contract_number`) AGAINST (? IN BOOLEAN MODE);

The problem is that when the query above is executed, I get an error from PDO class

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'contracts_info.contract_number' in 'where clause'

I've trying running a query with names enclosed in backquotes or not, this doesn't make any difference. Queries without JOINS work well. Also I investigated these questions

mySQL MATCH across multiple tables

MySQL Optional LEFT JOIN With MATCH

but they didn't seem to have alike problem. I don't understand why this happens because I give the exact name of columns and don't use AS aliases.

So why MySQL fails in finding and existing column from existing table?

MySQL version(): 5.6.29-76.2, PHP 5.5.9

Thank you in advance.

EDIT:

Table definitions:

CREATE TABLE IF NOT EXISTS `contracts_info` (
  `contract_number` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
  `balance` decimal(7,2) unsigned zerofill NOT NULL,
  `conclusion_date` date NOT NULL,
  `activation_date` date NOT NULL,
  `deactivation_date` date NOT NULL,
  PRIMARY KEY (`contract_number`),
  FULLTEXT KEY `contract_number` (`contract_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `students` (
    `contract_number` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
    `name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `second_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `surname` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `form_number` enum('1','2','3','4','5','6','7','8','9','10','11','12') COLLATE utf8_unicode_ci NOT NULL,
    `form_letter` enum('А','Б','В','Г','Д') COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`contract_number`),
    FULLTEXT KEY `name` (`name`),
    FULLTEXT KEY `surname` (`surname`),
    FULLTEXT KEY `contract_number` (`contract_number`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `students_info` (
  `contract_number` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
  `medical_features` text COLLATE utf8_unicode_ci NOT NULL,
  `psychological_features` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`contract_number`),
  FULLTEXT KEY `medical_features` (`medical_features`),
  FULLTEXT KEY `psycological_features` (`psychological_features`),
  FULLTEXT KEY `contract_number` (`contract_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `parents` (
  `contract_number` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
  `mother_fullname` varchar(90) COLLATE utf8_unicode_ci NOT NULL,
  `mother_email` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `mother_phone` decimal(15,0) NOT NULL,
  `father_fullname` varchar(90) COLLATE utf8_unicode_ci NOT NULL,
  `father_email` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `father_phone` decimal(15,0) NOT NULL,
  `postal_office` int(4) NOT NULL,
  PRIMARY KEY (`contract_number`),
  FULLTEXT KEY `contract_number` (`contract_number`),
  FULLTEXT KEY `mother_fullname` (`mother_fullname`),
  FULLTEXT KEY `mother_email` (`mother_email`),
  FULLTEXT KEY `father_fullname` (`father_fullname`),
  FULLTEXT KEY `father_email` (`father_email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

0 Answers0