I am writing a code prepared request:

$queryMask = "SELECT (`id`, CONCAT(?) AS NAME, MATCH(?) AGAINST(?) AS RELEV) FROM `table` WHERE MATCH(?) AGAINST(?) ORDER BY RELEV DESC"; // подготавливаем запрос $this->stmt = $this->db->prepare($queryMask); if (!($this->stmt)) throw new SearchException(3, "Error set prepare request: ".$this->db->error);

And the output throws an exception:

 Error set prepare request: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS NAME, MATCH(?) AGAINST(?) AS RELEV) FROM `table` WHERE MATCH(?) AGAINST(?) O' at line 1.

I already broke my whole head, I don’t understand what he doesn’t like?

UPD # 1: The joke is that the view request

 SELECT `id`, CONCAT(`rusname`, ' (',`origname`,') [',`year`,']') AS NAME, MATCH(`rusname`,`origname`,`year`) AGAINST('>(+пира +моря +свет) (пира* моря* свет*)' IN BOOLEAN MODE) AS RELEV FROM `films` WHERE MATCH(`rusname`,`origname`,`year`) AGAINST('>(+пира +моря +свет) (пира* моря* свет*)' IN BOOLEAN MODE) ORDER BY RELEV DESC;

passes without problems. Magic.

UPD # 2: It seems still not my day. I solved the problem by using a simple and native query (), but I decided to score on all kinds of fashionable troubles like prepare (), because it is not clear what it needs.

  • Does the query work if you substitute names? - msi
  • And even if I substitute names, it also does not work ... - ReklatsMasters
  • What is the dialect of SQL? - Sh4dow
  • Hz, there is usual MySQL 5.1.58 base. - ReklatsMasters
  • Ah, pdo'shniki) Did you try to execute it there before checking? It is obvious that the request is invoked in the form in which it is. - Sh4dow

4 answers 4

In MySQL, in prepared statements you can set placeholders only instead of values, but not instead of the names of tables, fields, or control structures. Those. is a SELECT a FROM tbl WHERE b = ? will be correct, and SELECT ? FROM tbl WHERE a = 3 SELECT ? FROM tbl WHERE a = 3 anymore.

The fact is that the prepared statements, as the name implies, are prepared for execution at the request preparation stage and by this time MySQL needs to know which tables and fields will be used to calculate the query execution strategy in advance.

  • And with the expression in AGAINST what to do? - ReklatsMasters

And why brackets in SELECT?

Try this:

 SELECT `id`, CONCAT(?) AS NAME, MATCH(?) AGAINST(?) AS RELEV FROM `table` WHERE MATCH(?) AGAINST(?) ORDER BY RELEV DESC;
  • No, that doesn't work. Actually, I put the brackets, after it did not work without brackets. - ReklatsMasters

You might be interested in this . For some reason it seems to me that this is not the first request and you have not closed the previous prepare. That is, the request must be either completed or cleared. If this does not help, we write minimal code to catch the error. (connect, prepare, your request)

UPDATE In general, I repent a little, because the joint is still in match. As I understood it: the query is checked not only for syntactic correctness, but also for the presence of a FULLTEXT index for the fields in the MATCH part. That is, for normal operation, you must add a list of fields in MATCH.

PS: I'm not at all a fan of any PDO, because this thing was completely alien.

ZZY: here is your request

 SELECT `id`, CONCAT(?) AS NAME, MATCH(`rusname`,`origname`,`year`) AGAINST(?) AS RELEV FROM `table` WHERE MATCH(`rusname`,`origname`,`year`) AGAINST(?) ORDER BY RELEV DESC
  • The fact of the matter is that this is the first request. The code is written in a cap, in my connection there is nothing special, everything is usually. Swears precisely on MATCH (?) AGAINST (?) ... Even from the console did not immediately start (upd # 1). - ReklatsMasters
  • Mmm, apparently, I very quietly updated the answer) @ReklatsMasters, check plz - Sh4dow
  • $ _mask = "your request"; if ($ this-> db-> prepare ($ _ mask)) throw new SearchException (7, "not work"); else throw new SearchException (7, "work"); Throws out "not work". This is not PDO, but mysqli. I did not begin to deal with PDO for mysqli as something more familiar and easier. - ReklatsMasters
  • So this is mysqli o_O And you have mixed up the conditions)) everything works. Pereksotrite your comment) - Sh4dow
  • Leave, right, these troubles. - ReklatsMasters
 $queryMask = "SELECT `id`, CONCAT(?) AS NAME, MATCH(?), AGAINST(?) AS RELEV FROM `table` WHERE MATCH(?) and AGAINST(?) ORDER BY RELEV DESC";

well, maybe you didn't make friends with punctuation :) well, and the brackets nafig

  • Hm Your option even in sonsoli does not work. There should be nothing between MATCH and AGAINST. Maybe not my day (a week?). - ReklatsMasters