Symfony1/Doctrine : SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Question

I'm having a problem with a Doctrine_Query actually. It a Symfony 1 project (1.4.20).

Here's the final error message :

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Here's the code to build the query :

public function search ($keywords_article, $lang, $keywords_produit = '', $limit = 0, $offset = 0)
{
    $q = $this->createQuery('a')
        ->innerJoin('a.FlatSearchArticles fa')
        ->innerJoin('a.Translation t')
        ->where('t.lang = ?', $lang)
        ->addWhere('fa.lang = ?', $lang)
        ->addWhere('fa.reference LIKE "%?%" Or fa.nom LIKE "%?%"', array($keywords_article, $keywords_article));

    if ('' != $keywords_produit)
        $q->innerJoin('fa.Produits fp')
            ->addWhere('fp.lang = ?', $lang)
            ->addWhere('fp.reference LIKE "%?%" Or fp.nom LIKE "%?%"', array($keywords_produit, $keywords_produit));

    if (0 != $limit)
        $q->limit($limit);

    if (0 != $offset)
        $q->offset($offset);

    return $q->execute();
}

The stack trace :

1. at ()
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php line 1082 ...
2. at Doctrine_Connection->rethrowException(object('PDOException'), object('Doctrine_Connection_Statement'))
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection/Statement.php line 269 ...
3. at Doctrine_Connection_Statement->execute(array('fr', 'fr', 'ALLUM', 'ALLUM', 'fr', 'IDRA', 'IDRA'))
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php line 1006 ...
4. at Doctrine_Connection->execute('SELECT i.id AS i__id, i.reference AS i__reference, i.art_code AS i__art_code, i.commentaire AS i__commentaire, i.image AS i__image, i.marque_id AS i__marque_id, i.article_type_id AS i__article_type_id, i.reference_fournisseur AS i__reference_fournisseur, i.historique AS i__historique, i.article_id AS i__article_id, i.reference_client AS i__reference_client, i.domaine_id AS i__domaine_id, i.migration_id AS i__migration_id, i.date_validite AS i__date_validite, i.list_pays AS i__list_pays, i.type AS i__type, i.created_at AS i__created_at, i.updated_at AS i__updated_at, f.id AS f__id, f.reference AS f__reference, f.nom AS f__nom, f.lang AS f__lang, f.article_id AS f__article_id, i2.id AS i2__id, i2.nom AS i2__nom, i2.lang AS i2__lang, f2.id AS f2__id, f2.reference AS f2__reference, f2.nom AS f2__nom, f2.lang AS f2__lang, f2.produit_id AS f2__produit_id, f2.domaine_id AS f2__domaine_id, f2.marque_id AS f2__marque_id FROM item i INNER JOIN flat_search_article f ON i.id = f.article_id INNER JOIN item_translation i2 ON i.id = i2.id INNER JOIN flat_search_article_produit f3 ON (f.id = f3.article_id) INNER JOIN flat_search_produit f2 ON f2.id = f3.produit_id WHERE (i2.lang = ? AND f.lang = ? AND (f.reference LIKE "%?%" OR f.nom LIKE "%?%") AND f2.lang = ? AND (f2.reference LIKE "%?%" OR f2.nom LIKE "%?%") AND (i.type = 1))', array('fr', 'fr', 'ALLUM', 'ALLUM', 'fr', 'IDRA', 'IDRA'))
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php line 983 ...
5. at Doctrine_Query_Abstract->_execute(array())
in SF_SYMFONY_LIB_DIR/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php line 1033 ...
6. at Doctrine_Query_Abstract->execute()
in SF_ROOT_DIR/lib/model/doctrine/ArticleTable.class.php line 90 ...

When I try the same query in mysql console client, it works :

mysql> SELECT i.id AS i__id, i.reference AS i__reference, i.art_code AS i__art_code, i.commentaire AS i__commentaire, i.image AS i__image, i.marque_id AS i__marque_id, i.article_type_id AS i__article_type_id, i.reference_fournisseur AS i__reference_fournisseur, i.historique AS i__historique, i.article_id AS i__article_id, i.reference_client AS i__reference_client, i.domaine_id AS i__domaine_id, i.migration_id AS i__migration_id, i.date_validite AS i__date_validite, i.list_pays AS i__list_pays, i.type AS i__type, i.created_at AS i__created_at, i.updated_at AS i__updated_at, f.id AS f__id, f.reference AS f__reference, f.nom AS f__nom, f.lang AS f__lang, f.article_id AS f__article_id, i2.id AS i2__id, i2.nom AS i2__nom, i2.lang AS i2__lang, f2.id AS f2__id, f2.reference AS f2__reference, f2.nom AS f2__nom, f2.lang AS f2__lang, f2.produit_id AS f2__produit_id, f2.domaine_id AS f2__domaine_id, f2.marque_id AS f2__marque_id FROM item i INNER JOIN flat_search_article f ON i.id = f.article_id INNER JOIN item_translation i2 ON i.id = i2.id INNER JOIN flat_search_article_produit f3 ON (f.id = f3.article_id) INNER JOIN flat_search_produit f2 ON f2.id = f3.produit_id WHERE (i2.lang = 'fr' AND f.lang = 'fr' AND (f.reference LIKE "%ALLUM%" OR f.nom LIKE "%ALLUM%") AND f2.lang = 'fr' AND (f2.reference LIKE "%IDRA%" OR f2.nom LIKE "%IDRA%") AND (i.type = 1));
***/***
35 rows in set (0.00 sec)

I do not understand why Symfony1/Doctrine/PHP Pdo do not "like" my query.

Thanks for your support, David.

Answer 1

The other solution I've found by my side :

public function search ($keywords_article, $lang, $keywords_produit = '', $limit = 0, $offset = 0)
{
    $q = $this->createQuery('a')
        ->innerJoin('a.FlatSearchArticles fa')
        ->innerJoin('a.Translation t')
        ->where('t.lang = :lang')
        ->addWhere('fa.lang = :lang')
        ->addWhere('fa.reference LIKE "%:kwa%" OR fa.nom LIKE "%:kwa%"');

    if ('' != $keywords_produit)
        $q->innerJoin('fa.FlatSearchProduits fp')
            ->addWhere('fp.lang = :lang')
            ->addWhere('fp.reference LIKE "%:kwp%" OR fp.nom LIKE "%:kwp%"');

    if (0 != $limit)
        $q->limit($limit);

    if (0 != $offset)
        $q->offset($offset);

    return $q->execute(array(
        ':lang' => $lang,
        ':kwa' => $keywords_article,
        ':kwp' => $keywords_produit,
    ));
}

I finaly prefer to use ":key".

Thanks to Marek for this answer which works fine too.

Bye, David.

Answer 2

? is not a token in this:

->addWhere('fa.reference LIKE "%?%" Or fa.nom LIKE "%?%"', array($keywords_article, $keywords_article));

Instead pass it this way:

->addWhere('fa.reference LIKE ? Or fa.nom LIKE ?', array('%' . $keywords_article . '%', '%' . $keywords_article . '%'));