Executing a stored procedure with cursor in PHP

Question

I have a stored procedure that I am trying to call from my php. Here is the stored procedure:

BEGIN
DECLARE done INT DEFAULT FALSE;
declare phone_temp VARCHAR(20) default '';
declare phone_cur cursor for SELECT DISTINCT sentNum FROM Queue;
declare continue handler for not found set done = true;

#create temp table
create temporary table if not exists temp_return AS SELECT * FROM Queue LIMIT 0;
#empty if exists
delete from temp_return;

open phone_cur;

phone_loop: LOOP
    fetch phone_cur into phone_temp;
    if done then
        leave phone_loop;
    end if;

   insert into temp_return SELECT * FROM Queue WHERE num2=phone_temp LIMIT 2;
   insert into temp_return SELECT * FROM Queue WHERE num1=phone_temp LIMIT 1;
end loop phone_loop;
close phone_cur;

select * from temp_return;

drop table if exists temp_return;
END

Directly in mysql workbench, calling it works. In php, it does not work. Here is my php:

function grabFromSmsQueue(){
    global $myStmt, $conn;
    if(isset($myStmt)){
        $myStmt -> execute();
    }
    else{
        $query = "CALL myStoredProc();";
        $myStmt = $conn->stmt_init();
        $myStmt -> prepare($query);
        $myStmt -> execute();

    }
    $result = $myStmt -> get_result();
    //print_r ($result);
    $info = [];
    if(isset($result)){
        while($data = $result->fetch_assoc()){
            $info[] = $data;
        }
    }
    return $info;
}

Connecting like this, I get the following error

The localhost page isn’t working
localhost didn’t send any data.
ERR_EMPTY_RESPONSE

I traced my problem back to an issue with $data = $result->fetch_assoc(), because when I comment that out and put in the print_r I get something actually returned, which is mysqli_result Object ( [current_field] => 0 [field_count] => 9 [lengths] => [num_rows] => 0 [type] => 1 ). I have drawn the conclusion that it is not working because [num_rows] => 0.

Now, going back to my stored procedure, I took out all mentions of a cursor and replaced it with a hard-coded value, and it worked in both workbench and php. I have already verified that the user connecting through php has permission, that the connection is open, and that the same code can execute other stored procedures (ones that do not include cursors). Does this mean that I can not use cursors in stored procedures that are being called by php? Are there alternatives to cursors? Am I missing something in my php syntax to deal with cursors?

I have rarely seen a cursor implementation that wasn't a sign that the programmer had no idea about sets, relations, and the power of sql. They screw up a finely tuned performance beast and turn it into a kloodgey, weak, slow mess. — Drew, Jun 23 '16 at 04:41
@Drew if you are referring to my use of cursors to execute 2 seemingly identical queries that could have been accomplished easily in 1 line, I guess I should clarify that I removed my complicated queries and replaced them with these simple ones to showcase my issue. It is to my understanding that a cursor lets you step through the returned values from a query, and use those inside a loop one at a time. Am I mistaken? — starvator, Jun 23 '16 at 11:17
You are not mistaken. Using cursors is always the worst choice between "should I use cursors or should I not" if not is a possibility. — Drew, Jun 23 '16 at 11:20
@Drew would you have any suggestion for an implementation that does not require the use of cursors then? All I am trying to do is select the top 2 of each group (a group being entries with the same num1) — starvator, Jun 23 '16 at 12:54
Sure. If you create the sqlfiddle with schema and data loaded, and describe it well, I would happily show a way. — Drew, Jun 23 '16 at 13:01
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115410/discussion-between-starvator-and-drew). — starvator, Jun 23 '16 at 13:09
Thanks a bunch! here is the fiddle: http://sqlfiddle.com/#!9/140c14 I am trying to select the top 2 entries per 'sentNum', grouped by the 'sentByTime' — starvator, Jun 23 '16 at 13:13

score 1 · Accepted Answer · edited May 23 '17 at 11:58

Based on discussions in chat for 3 groupings, and this provided SQLFiddle for test data (not much data there).

Due to testing data with a sliding window of where now() is in relation to that data, the following variable was used to "freeze" now(). Simply to facilitate testing and verification of output.

So, ditch that ultimately and change the 4 references in the code that use it (note that Group 3 uses it twice).

The now() variable:

select @theNow:=now();
-- REM OUT the following line. It is used only for testing (as now will chg, your data won't)
select @theNow:='2016-06-23 14:00:00';

The Query:

select id,sentNum,message,sentTime,startAtTime,sentByTime,msgType,theGrp from
(   select id,sentNum,message,sentTime,startAtTime,sentByTime,msgType,theGrp,
    if(sentNum!=@lastSentNum,greatest(@sentNumChg:=1,0),least(@sentNumChg:=0,1)) as dummy1,
    if(theGrp!=@lastGrp,greatest(@grpChg:=1,0),least(@grpChg:=0,1)) as dummy2,
    if(@sentNumChg=1 or @grpChg=1,@seqNum:=1,@seqNum:=@seqNum+1) as seqNum,
    @lastSentNum:=sentNum as setLast01,
    @lastGrp:=theGrp as setLast02
    from
    (   -- GROUP 1: sentByTime<=now(), INVITE
        select `id`, `sentNum`, `message`, `sentTime`, `startAtTime`, `sentByTime`, `msgType`, 1 as theGrp
        from SmsQueue
        where sentByTime<=@theNow and msgType='invite'
        UNION ALL
        -- GROUP 2 startAtTime<=now(), BROADCAST
        select `id`, `sentNum`, `message`, `sentTime`, `startAtTime`, `sentByTime`, `msgType`, 2 as theGrp
        from SmsQueue
        where startAtTime<=@theNow and msgType='broadcast'
        UNION ALL
        -- GROUP 3: sentByTime>now() && startAtTime<=now(), INVITE
        select `id`, `sentNum`, `message`, `sentTime`, `startAtTime`, `sentByTime`, `msgType`, 3 as theGrp
        from SmsQueue
        where sentByTime>@theNow and startAtTime<=@theNow and msgType='invite'
    ) d1
    cross join (select @sentNumChg:=0,@grpChg:=0,@lastSentNum:='',@lastGrp:=0,@seqNum:=0) as xParams
    order by sentNum,theGrp,sentByTime,id -- id is the tie-break
) d2
where (theGrp=1 and seqNum<3) or (theGrp=2 and seqNum=1) or (theGrp=3 and seqNum=1)
order by sentNum,theGrp;

Output (my client tool is text challenged at the moment):

See my general comments at the top of this answer of mine for advanced variable usage.

Drew, you are the king, this is spectacular! Thank you so much! Life saver! — starvator, Jun 23 '16 at 20:03
I am just a junior-class court jester. Other kings around here. — Drew, Jun 23 '16 at 20:04

Executing a stored procedure with cursor in PHP

1 Answers1

Linked