php - 准备好的语句绑定(bind)参数错误

Question

在执行以下查询时出现此错误

Fatal error: 
    Uncaught exception 'Zend_Db_Statement_Exception' with message 'Invalid bind-variable position ':individualSexConstantsFirst'' 
    in C:\xampp\htdocs\magento\app\code\core\Zend\Db\Statement.php:258  
Stack trace: 
#0 C:\xampp\htdocs\magento\app\code\local\Customer\Userprofile\Model\Customersettings.php(1201): Zend_Db_Statement->bindParam(':individualSexC...', 36) 
#1 C:\xampp\htdocs\magento\UserProfile\SearchUserFilters.php(33): Customer_Userprofile_Model_Customersettings->getUserFilterData('1992-07-16', '1', '', '', '', '', 'es') 
#2 C:\xampp\htdocs\magento\UserProfile\SearchUserFilters.php(55): SearchFilterData->userFilterData('1992-07-16', '1', '', '', '', '', 'es') 
#3 {main} thrown in C:\xampp\htdocs\magento\app\code\core\Zend\Db\Statement.php on line 258

这里是查询

 $userFilterDataQuery=$magentoDatabaseConnection->prepare("SELECT var.value AS product_name,var.entity_type_id as id
            FROM magento_catalog_product_entity e, magento_eav_attribute eav, magento_catalog_product_entity_varchar var
            WHERE
            e.entity_type_id = eav.entity_type_id
            AND eav.attribute_code = 'name'
            AND eav.attribute_id = var.attribute_id
            AND var.entity_id = e.entity_id AND e.entity_id IN ( SELECT DISTINCT pt.product_id FROM sohyper_product_detailtab_details_int pdti,sohyper_product_detailtab pdt,sohyper_product_tab pt WHERE pdt.details_name = :individualSexConstantsFirst AND pt.tab_id = pdti.tab_id AND pdt.details_id= pdti.details_id AND ( pdti.details_value = :identitySexFirst OR pdti.details_value = :eventTypeConstantsFirst )) AND e.entity_id IN ( SELECT DISTINCT eb.product_id FROM sohyper_product_detailtab_details_dateonly e ,sohyper_product_detailtab ea ,sohyper_product_tab eb, sohyper_product_detailtab_details_dateonly de ,sohyper_product_detailtab dea ,sohyper_product_tab deb WHERE ea.details_name = :individualBornFromConstantsFirst AND eb.tab_id = e.tab_id AND ea.details_id= e.details_id AND e.tab_id  = de.tab_id AND dea.details_name = :individualBornToConstantsFirst AND deb.tab_id = de.tab_id AND dea.details_id= de.details_id AND (:birthFilterFirst BETWEEN e.details_value AND de.details_value) )
            AND var.value LIKE :searchQueryFirst
            UNION ALL
            SELECT value AS product_name, entity_type_id as id
            FROM magento_catalog_category_entity_varchar
            WHERE
            attribute_id = (SELECT ea.attribute_id FROM magento_eav_attribute ea, magento_eav_entity_type et
            WHERE
            et.entity_type_code = 'catalog_category'
            AND ea.attribute_code = 'name'
            AND et.entity_type_id = ea.entity_type_id)
            AND entity_id
            IN (SELECT DISTINCT pdti.details_value
            FROM sohyper_product_tab pt, sohyper_product_detailtab pdt, sohyper_product_detailtab_details_int pdti
            WHERE
            pdti.tab_id=pdt.tab_id AND pdt.tab_id IN ( SELECT DISTINCT pt.tab_id FROM sohyper_product_detailtab_details_int pdti,sohyper_product_detailtab pdt,sohyper_product_tab pt WHERE pdt.details_name = :individualSexConstantsSecond AND pt.tab_id = pdti.tab_id AND pdt.details_id= pdti.details_id AND ( pdti.details_value = :identitySexSecond OR pdti.details_value = :eventTypeConstantsSecond )) AND pdt.tab_id IN ( SELECT DISTINCT e.tab_id FROM sohyper_product_detailtab_details_dateonly e ,sohyper_product_detailtab ea ,sohyper_product_tab eb, sohyper_product_detailtab_details_dateonly de ,sohyper_product_detailtab dea ,sohyper_product_tab deb WHERE ea.details_name = :individualBornFromConstantsSecond AND eb.tab_id = e.tab_id AND ea.details_id= e.details_id AND e.tab_id  = de.tab_id AND dea.details_name = :individualBornToConstantsSecond AND deb.tab_id = de.tab_id AND dea.details_id= de.details_id AND (:birthFilterSecond BETWEEN e.details_value AND de.details_value) )
            AND pdti.details_id=pdt.details_id
            AND pdt.details_name=:distActivityConstants)
            AND value LIKE :searchQuerySecond");
        $userFilterDataQuery->bindParam(':individualSexConstantsFirst', $individualSexConstants);
        $userFilterDataQuery->bindParam(':identitySexFirst', $identitySex);
        $userFilterDataQuery->bindParam(':eventTypeConstantsFirst', $eventTypeConstants);
        $userFilterDataQuery->bindParam(':individualBornFromConstantsFirst', $individualBornFromConstants);
        $userFilterDataQuery->bindParam(':individualBornToConstantsFirst', $individualBornToConstants);
        $userFilterDataQuery->bindParam(':birthFilterFirst', $birthFilter);
        $userFilterDataQuery->bindParam(':searchQueryFirst', $searchQuery);
        $userFilterDataQuery->bindParam(':individualSexConstantsSecond', $individualSexConstants);
        $userFilterDataQuery->bindParam(':identitySexSecond', $identitySex);
        $userFilterDataQuery->bindParam(':eventTypeConstantsSecond', $eventTypeConstants);
        $userFilterDataQuery->bindParam(':individualBornFromConstantsSecond', $individualBornFromConstants);
        $userFilterDataQuery->bindParam(':individualBornToConstantsSecond', $individualBornToConstants);
        $userFilterDataQuery->bindParam(':birthFilterSecond', $birthFilter);
        $userFilterDataQuery->bindParam(':distActivityConstants', $distActivityConstants);
        $userFilterDataQuery->bindParam(':searchQuerySecond', $searchQuery);
        $userFilterDataQuery->execute();
        $userFilterData=$userFilterDataQuery->fetchAll();

Answer 1

尝试使用相同的参数值

 $userFilterDataQuery=$magentoDatabaseConnection->prepare("SELECT var.value AS product_name,var.entity_type_id as id
            FROM magento_catalog_product_entity e, magento_eav_attribute eav, magento_catalog_product_entity_varchar var
            WHERE
            e.entity_type_id = eav.entity_type_id
            AND eav.attribute_code = 'name'
            AND eav.attribute_id = var.attribute_id
            AND var.entity_id = e.entity_id AND e.entity_id IN ( SELECT DISTINCT pt.product_id FROM sohyper_product_detailtab_details_int pdti,sohyper_product_detailtab pdt,sohyper_product_tab pt WHERE pdt.details_name = :individualSexConstants AND pt.tab_id = pdti.tab_id AND pdt.details_id= pdti.details_id AND ( pdti.details_value = :identitySex OR pdti.details_value = :eventTypeConstants )) AND e.entity_id IN ( SELECT DISTINCT eb.product_id FROM sohyper_product_detailtab_details_dateonly e ,sohyper_product_detailtab ea ,sohyper_product_tab eb, sohyper_product_detailtab_details_dateonly de ,sohyper_product_detailtab dea ,sohyper_product_tab deb WHERE ea.details_name = :individualBornFromConstants AND eb.tab_id = e.tab_id AND ea.details_id= e.details_id AND e.tab_id  = de.tab_id AND dea.details_name = :individualBornToConstants AND deb.tab_id = de.tab_id AND dea.details_id= de.details_id AND (:birthFilterSecond BETWEEN e.details_value AND de.details_value) )
            AND var.value LIKE :searchQuery
            UNION ALL
            SELECT value AS product_name, entity_type_id as id
            FROM magento_catalog_category_entity_varchar
            WHERE
            attribute_id = (SELECT ea.attribute_id FROM magento_eav_attribute ea, magento_eav_entity_type et
            WHERE
            et.entity_type_code = 'catalog_category'
            AND ea.attribute_code = 'name'
            AND et.entity_type_id = ea.entity_type_id)
            AND entity_id
            IN (SELECT DISTINCT pdti.details_value
            FROM sohyper_product_tab pt, sohyper_product_detailtab pdt, sohyper_product_detailtab_details_int pdti
            WHERE
            pdti.tab_id=pdt.tab_id AND pdt.tab_id IN ( SELECT DISTINCT pt.tab_id FROM sohyper_product_detailtab_details_int pdti,sohyper_product_detailtab pdt,sohyper_product_tab pt WHERE pdt.details_name = :individualSexConstants AND pt.tab_id = pdti.tab_id AND pdt.details_id= pdti.details_id AND ( pdti.details_value = :identitySex OR pdti.details_value = :eventTypeConstants )) AND pdt.tab_id IN ( SELECT DISTINCT e.tab_id FROM sohyper_product_detailtab_details_dateonly e ,sohyper_product_detailtab ea ,sohyper_product_tab eb, sohyper_product_detailtab_details_dateonly de ,sohyper_product_detailtab dea ,sohyper_product_tab deb WHERE ea.details_name = :individualBornFromConstants AND eb.tab_id = e.tab_id AND ea.details_id= e.details_id AND e.tab_id  = de.tab_id AND dea.details_name = :individualBornToConstants AND deb.tab_id = de.tab_id AND dea.details_id= de.details_id AND (:birthFilterSecond BETWEEN e.details_value AND de.details_value) )
            AND pdti.details_id=pdt.details_id
            AND pdt.details_name=:distActivityConstants)
            AND value LIKE :searchQuery");

        $userFilterDataQuery->bindParam(':individualSexConstants', $individualSexConstants);
        $userFilterDataQuery->bindParam(':eventTypeConstants', $eventTypeConstants);
        $userFilterDataQuery->bindParam(':individualBornFromConstants', $individualBornFromConstants);
        $userFilterDataQuery->bindParam(':individualBornToConstants', $individualBornToConstants);
        $userFilterDataQuery->bindParam(':birthFilterSecond', $birthFilter);
        $userFilterDataQuery->bindParam(':distActivityConstants', $distActivityConstants);
        $userFilterDataQuery->bindParam(':identitySex', $identitySex);
        $userFilterDataQuery->bindParam(':searchQuery', $searchQuery);
        $userFilterDataQuery->execute();
        $userFilterData=$userFilterDataQuery->fetchAll();