MySQL: Unknown column 'search_string' in 'field list' when using the LIKE operator

Question

My MySQL database gives me the Unknown column 'search_string' in 'field list' error message when I'm trying to run this query with 'search_string' as a parameter to the stored procedure:

DROP PROCEDURE IF EXISTS select_title_from_book_table_like;
CREATE PROCEDURE select_title_from_book_table_like(IN IN_TITLE VARCHAR(255))
BEGIN
    PREPARE stmt FROM
    "SELECT   b.book_id,
              LEFT(b.title, 36) AS title,
              b.edition,
              GROUP_CONCAT(CONCAT_WS(' ', a.last_name,
                                          a.first_name,
                                          a.patronymic)
                                 ORDER BY a.last_name
                                 SEPARATOR ', ') AS authors,
              YEAR(b.published) AS published,
              LEFT(b.pages, 8)  AS pages,
              b.isbn,
              s.subject,
              l.language,
              p.publisher
     FROM    (SELECT * FROM book WHERE title LIKE '%?%') AS b
              LEFT JOIN book_author AS ba
                     ON b.book_id = ba.book_id
              LEFT JOIN author AS a
                     ON ba.author_id = a.author_id
              LEFT JOIN language AS l
                     ON b.language_id = l.language_id
              LEFT JOIN subject AS s
                     ON b.subject_id = s.subject_id
              LEFT JOIN publisher AS p
                     ON b.publisher_id = p.publisher_id
     GROUP BY b.book_id
     ORDER BY b.title ASC";

    SET @param1 = IN_TITLE; 

    EXECUTE stmt USING @param1;
    DEALLOCATE PREPARE stmt;
END$$

This is the code that calls the procedure:

ShowStringGrid(StringGrid,
    'CALL select_title_from_book_table_like(' + Trim(SearchBox.Text) + ')');

score 0 · Accepted Answer · edited May 23 '17 at 12:04

0

I think your prepared statement goes wrong here:

WHERE    b.title LIKE '%?%'

You don't want to set the % there, because now your question-mark is in the '. A better option would be to use

WHERE    b.title LIKE ?

and then add the % to your actual parameter.

From the comment on the answer on this question you can also find you might do it like so in the query, but I have no experience with that:

WHERE    b.title LIKE '%' || ? || '%'

edited May 23 '17 at 12:04

Community

1
1

answered Apr 25 '13 at 07:57

Nanne

64,065
16
119
163

Yeah, I fixed it a little bit, but it now shows another error message. When I provide the query with a search string, for example 'PHP', it says Unknown column 'PHP' in 'field list'. – Mikhail Apr 25 '13 at 08:04

MySQL: Unknown column 'search_string' in 'field list' when using the LIKE operator

1 Answers1