php - 从 PHP 脚本调用的 MySQL 存储过程不返回结果

Question

我有一个由 php 执行的存储过程，实际的 SQL 输出在我的 SQL 管理器 [SQLYog] 中工作正常，但在 PHP 脚本中不返回任何结果。

阅读文档后，我似乎必须声明一些 OUT 变量才能使其正常工作，我已经这样做了 - 但它仍然没有返回结果。

请参阅下面的代码:

while ($row = $results->fetch(PDO::FETCH_ASSOC)) {

    $depth = $parentDepth;

    $cid = $row['id'];

    $ultimatesql = "DROP PROCEDURE IF EXISTS getParentArray;

    CREATE PROCEDURE getParentArray(IN cid INT, IN target INT, OUT parent_id_array VARCHAR(255), OUT my_parent VARCHAR(255))

    BEGIN

        DECLARE pid INT;
        DECLARE parent_id_array VARCHAR(255) DEFAULT '';
        DECLARE my_parent VARCHAR(255) DEFAULT '';

        REPEAT

            SET pid = (SELECT `sc`.`parent` FROM `modx_site_content` `sc` WHERE `id` = cid);

            SET parent_id_array = CONCAT(pid,',',parent_id_array);

            SET cid = pid;

        UNTIL cid = 0 END REPEAT;

        SET my_parent = (SELECT SUBSTRING_INDEX((SELECT SUBSTRING_INDEX(parent_id_array, ',', target)), ',', -1));

        IF my_parent = '' 

            THEN SET my_parent = -1; 

        END IF;

        SELECT parent_id_array , target , my_parent;

    END;

    CALL getParentArray($cid, $depth, @out_a, @out_b);";

$statement = $modx->prepare($ultimatesql);

// The SQL echoed here works - results get returned if pasted into SQL studio 
echo '<pre>';print_r($statement);echo '</pre>';

$statement->execute();


while($result = $statement->fetch(PDO::FETCH_ASSOC)){

    // no results get returned here, 
    echo '<br> my result = ' . print_r($result);echo '<br>';

}

}

我没有看到我做错了什么，知道为什么这不返回记录集吗？

Answer 1

看起来 Fabricator 是对的——如果我像这样把它分解，一切都很好[虽然看起来有点乱:(]

$drop_proc = "DROP PROCEDURE IF EXISTS getParentArray;";

$create_proc = " CREATE PROCEDURE getParentArray(IN cid INT, IN target INT, OUT parent_id_array VARCHAR(255), OUT my_parent VARCHAR(255))

    BEGIN

        DECLARE pid INT;
        DECLARE parent_id_array VARCHAR(255) DEFAULT '';
        DECLARE my_parent VARCHAR(255) DEFAULT '';

        REPEAT

            SET pid = (SELECT `sc`.`parent` FROM `modx_site_content` `sc` WHERE `id` = cid);

            SET parent_id_array = CONCAT(pid,',',parent_id_array);

            SET cid = pid;

        UNTIL cid = 0 END REPEAT;

        SET my_parent = (SELECT SUBSTRING_INDEX((SELECT SUBSTRING_INDEX(parent_id_array, ',', target)), ',', -1));

        IF my_parent = '' 

            THEN SET my_parent = -1; 

        END IF;

        SELECT parent_id_array , target , my_parent;

    END;";

$run_proc = "CALL getParentArray($cid, $depth, @out_a, @out_b);";


// drop the proc if exists
$drop_statement = $modx->prepare($drop_proc);

$drop_statement->execute();


// create a new one
$create_statement = $modx->prepare($create_proc);

$create_statement->execute();


// run it!
$run_statement = $modx->prepare($run_proc);

$run_statement->execute();


while($result = $run_statement->fetch(PDO::FETCH_ASSOC)){
    // now this works
    echo '<br> my result = ' . print_r($result);echo '<br>';

}