PDO - SQLSTATE [HY093]：UNION查询的参数号无效

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?

Answer 1

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.