CODEWITHSUNDEEP
phpmysqlstored-procedures
Nikul
Nikul

Reputation: 495

Stored procedure working with the mysql but with the php getting error

I wrote a stored procedure for reports, it is working fine with the mysql but when I call it from PHP, I get an error:

SQLSTATE[42000]: Syntax error or access violation: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

PHP :

$crm_reports = "CALL CRM_Report_Total_Prize_Redemption('".$CrmreportsFromDate."','".$CrmreportsToDate."',$CampaignID);";
$total_prize =  Yii::app()->db->createCommand($crm_reports)->queryAll();

Here is my stored procedure

CREATE DEFINER=`esp_reward`@`%` PROCEDURE `CRM_Report_Total_Prize_Redemption`(
                        IN v_StartDate DATETIME,
                        IN v_EndDate DATETIME,
                        IN v_CampaignID INT
                    )
BEGIN
    DROP TEMPORARY TABLE IF EXISTS temp_prize_redemption;
    CREATE TEMPORARY TABLE temp_prize_redemption (
            ItemID INT(10)
            ,Qty INT(11)
            ,BenefitName VARCHAR(500)
    );
    /************************************* Item Data ****************************/
    SET @v_SelectPhysicalItemForPickWin = CONCAT(" SELECT cpw.ItemID AS PrizeID,cpw.Benefit AS BenefitName
                ,COUNT(DISTINCT PickWinWinnerID) AS Qty
                ,cpw.VoucherID,cpw.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID 
                INNER JOIN crm_pickwin_winner AS cpw ON cpw.PickWinID = cr.PickWinID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND cpw.BenefitType  = '1'
                GROUP BY PrizeID ");        

    SET @v_SelectPhysicalItemForPromotion = CONCAT(" SELECT crb.ItemID  AS PrizeID,crb.BenefitName
                ,COUNT(DISTINCT ID) AS Qty,crb.VoucherID,crb.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID AND cr.TypeID = '1' 
                INNER JOIN crm_receipt_benefit AS crb ON crb.PromotionID = cr.PromotionID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND crb.BenefitTypeID  = '1'
                GROUP BY PrizeID ");

    SET @ItemData = CONCAT(" SELECT PrizeID,SUM(Qty) AS Total,BenefitName FROM ( ",@v_SelectPhysicalItemForPickWin," UNION ",@v_SelectPhysicalItem, " ) AS  ItemData GROUP BY PrizeID ");           

    SET @v_data = CONCAT("INSERT INTO temp_prize_redemption", @ItemData);
    PREPARE s1 FROM @v_data;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;  
    /************************************* e-Voucher Data ****************************/
    SET @v_SelecteVoucherForPickWin = CONCAT(" SELECT cpw.VoucherID AS PrizeID,cpw.Benefit AS BenefitName
                ,COUNT(DISTINCT VoucherID) AS Qty
                ,cpw.VoucherID,cpw.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID 
                INNER JOIN crm_pickwin_winner AS cpw ON cpw.PickWinID = cr.PickWinID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND cpw.BenefitType  = '3'
                GROUP BY PrizeID ");        

    SET @v_SelecteVoucherForPromotion = CONCAT(" SELECT crb.VoucherID  AS PrizeID,crb.BenefitName
                ,COUNT(DISTINCT VoucherID) AS Qty,crb.VoucherID,crb.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID AND cr.TypeID = '1' 
                INNER JOIN crm_receipt_benefit AS crb ON crb.PromotionID = cr.PromotionID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND crb.BenefitTypeID  = '3'
                GROUP BY PrizeID ");

    SET @eVoucherData = CONCAT(" SELECT PrizeID,SUM(Qty) AS Total,BenefitName FROM ( ",@v_SelecteVoucherForPickWin," UNION ",@v_SelecteVoucherForPromotion, " ) AS  ItemData GROUP BY PrizeID ");           

    SET @v_data = CONCAT("INSERT INTO temp_prize_redemption", @eVoucherData);
    PREPARE s2 FROM @v_data;
    EXECUTE s2;
    DEALLOCATE PREPARE s2;  
    /************************************* Physical Voucher Data ****************************/
    SET @v_SelectPhysicalVoucherForPickWin = CONCAT(" SELECT cpw.VoucherAmount AS PrizeID,cpw.Benefit AS BenefitName
                ,COUNT(DISTINCT VoucherAmount) AS Qty
                ,cpw.VoucherID,cpw.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID 
                INNER JOIN crm_pickwin_winner AS cpw ON cpw.PickWinID = cr.PickWinID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND cpw.BenefitType  = '2'
                GROUP BY PrizeID ");        

    SET @v_SelectPhysicalVoucherForPromotion = CONCAT(" SELECT crb.VoucherAmount  AS PrizeID,crb.BenefitName
                ,COUNT(DISTINCT VoucherAmount) AS Qty,crb.VoucherID,crb.VoucherAmount,cpc.CampaignName
                FROM crm_promotion_campaign AS cpc 
                INNER JOIN crm_receipt AS cr ON cr.CampaignID = cpc.CampaignID AND cr.TypeID = '1' 
                INNER JOIN crm_receipt_benefit AS crb ON crb.PromotionID = cr.PromotionID
                WHERE 1  AND cpc.CampaignID = ",v_CampaignID," AND crb.BenefitTypeID  = '2'
                GROUP BY PrizeID ");

    SET @PhysicalVoucherVoucherData = CONCAT(" SELECT PrizeID,SUM(Qty) AS Total,BenefitName FROM ( ",@v_SelectPhysicalVoucherForPickWin," UNION ",@v_SelectPhysicalVoucherForPromotion, " ) AS  ItemData GROUP BY PrizeID ");           

    SET @v_data = CONCAT("INSERT INTO temp_prize_redemption", @PhysicalVoucherVoucherData);
    PREPARE s3 FROM @v_data;
    EXECUTE s3;
    DEALLOCATE PREPARE s3;  
    SELECT * FROM temp_prize_redemption;
    END$$

Can anyone please look into this and can give my any hint so i can solve this issues

Upvotes: 1

Views: 451

Answers (1)

symcbean
symcbean

Reputation: 48357

This should be a comment, but it's a bit long.

When I have a week or two to spare I'll start trying to make sense of your SQL - but there are a narrow set of use cases for PREPARE/EXECUTE and this does not appear to be one of them.

There are (still) critical parts of your PHP code missing.

Your insert statements do not explicitly state the columns the input should match too (not always fatal).

By random chance someone might post a solution which matches your problem, but in case that does not happen, you should reduce your SQL and PHP down to the minimum necessary to reproduce the fault.

Upvotes: 1

Related Questions