PDO - SQLSTATE[HY093]: Invalid parameter number with UNION query

Question

I spent my whole evening researching and trying to figure out what's wrong with my search query. I do some wildcard search using union queries and pagination.

$current_page = 0;

$search1 = $search;
$search2 = $search."%";
$search3 = "%".$search."%";

$pdo = DB::connection()->getPdo();

$stmt = $pdo->prepare('
    SELECT id, desc FROM table WHERE desc LIKE :search1 LIMIT :skip, 15
    UNION
    SELECT id, desc FROM table WHERE desc LIKE :search2 LIMIT :skip, 15
    UNION
    SELECT id, desc FROM table WHERE desc LIKE :search3 LIMIT :skip, 15
');

$stmt->bindParam(':search1', $search1);
$stmt->bindParam(':search2', $search2);
$stmt->bindParam(':search3', $search3);
$stmt->bindParam(':skip', $current_page, PDO::PARAM_INT);

$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

The first query (without the unions) works fine, or if I remove the :skip parameter, it works fine as well.

Any ideas what's wrong?

`desc` is a reserved word (as is `table` but I doubt that's what you're using as a table name, right? *wink*). It needs to be wrapped in backticks. http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html --- **wait for the answers** to pile in after this comment... (*I'd hate to fall into a potential can of worms if I do.*) — Funk Forty Niner, Jan 21 '14 at 00:54
Why would you want to use the offset with placeholder,which is default anyway. — Mihai, Jan 21 '14 at 01:00
can't you just do `desc LIKE :search1 OR desc LIKE :search2 OR desc LIKE :search3`? — Class, Jan 21 '14 at 01:07
I used union because I needed the results in this specific order (exact matches first, etc as you can see from the wildcards). The table and column names were just simplified for the example :) The :skip parameter ($current_page) changes with the pagination. — orszaczky, Jan 21 '14 at 01:32

score 7 · Accepted Answer · edited May 23 '17 at 12:34

Using named parameters in PDO, you can't use the same name more than once, unless you do:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

But if you do that, the values for :skip will be interpolated into the query as quoted strings, which are not valid in the context of LIMIT. In other words the following:

LIMIT '0', 15

results in syntax error, because LIMIT wants only true integers as its arguments.

For more examples and explanation, see my answer to Parametrized PDO query and LIMIT clause - not working

So your choices are to add a separate parameter for each occurrence of :skip:

$stmt = $pdo->prepare('
    SELECT id, desc FROM table WHERE desc LIKE :search1 LIMIT :skip1, 15
    UNION
    SELECT id, desc FROM table WHERE desc LIKE :search2 LIMIT :skip2, 15
    UNION
    SELECT id, desc FROM table WHERE desc LIKE :search3 LIMIT :skip3, 15
');

$stmt->bindParam(':search1', $search1);
$stmt->bindParam(':search2', $search2);
$stmt->bindParam(':search3', $search3);
$stmt->bindParam(':skip1', $current_page, PDO::PARAM_INT);
$stmt->bindParam(':skip2', $current_page, PDO::PARAM_INT);
$stmt->bindParam(':skip3', $current_page, PDO::PARAM_INT);

Or else interpolate the value into the query yourself, without quoting the integer.

But I agree with the comment from @Class, you don't have to do UNION at all. If nothing else, '%search%' works for both of the other two patterns: 'search%' and '%search'. You don't have to search all three.

Thanks for the great explanation, it works perfectly! (I needed union for the reasons mentioned above.) — orszaczky, Jan 21 '14 at 01:34

score 1 · Answer 2 · answered Jan 25 '14 at 14:25

Just as an update, if someone ended up here trying to implement a similar search that returns results by relevancy, the original query above will duplicate some of the results, and the limit for each select will skip some of the results.

After some testing the following query seems to do the job as expected:

$current_page = ($_GET['page'] - 1) * 15;

$search1 = $search;
$search2 = $search."%";
$search3 = "%".$search."%";

$pdo = DB::connection()->getPdo();

$stmt = $pdo->prepare('
    SELECT id, desc FROM table WHERE desc LIKE :search1
    UNION
    SELECT id, desc FROM table WHERE desc LIKE :search2 AND desc NOT LIKE :search1a
    UNION
    SELECT id, desc FROM table WHERE desc LIKE :search3 AND desc NOT LIKE :search2a
    LIMIT :skip, 15
');

$stmt->bindParam(':search1', $search1);
$stmt->bindParam(':search1a', $search1);
$stmt->bindParam(':search2', $search2);
$stmt->bindParam(':search2a', $search2);
$stmt->bindParam(':search3', $search3);
$stmt->bindParam(':skip', $current_page, PDO::PARAM_INT);

$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

score 0 · Answer 3 · answered Jan 21 '14 at 01:03

Put each SELECT in parentheses.

from documentation

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

$stmt = $pdo->prepare('
    (SELECT id, desc FROM table WHERE desc LIKE :search1 LIMIT :skip, 15)
    UNION
    (SELECT id, desc FROM table WHERE desc LIKE :search2 LIMIT :skip, 15)
    UNION
    (SELECT id, desc FROM table WHERE desc LIKE :search3 LIMIT :skip, 15)
');

PDO - SQLSTATE[HY093]: Invalid parameter number with UNION query

3 Answers3