Running a stored procedure with a 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 this 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; } 

Matching this way, I get the following error

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

I traced my problem to the problem with $data = $result->fetch_assoc() , because when I comment this and put print_r , I get something really returned, which is mysqli_result Object ( [current_field] => 0 [field_count] => 9 [lengths] => [num_rows] => 0 [type] => 1 ) . I concluded that it does not work because [num_rows] => 0 .

Now, returning to my stored procedure, I took out all the mentions about the cursor and replaced it with a hard-coded value, and it worked both in workbench and in php. I have already confirmed that the user connecting via php has permission that the connection is open, and that the same code can execute other stored procedures (those that do not contain cursors). Does this mean that I cannot use cursors in stored procedures called by php? Are there alternatives to cursors? Am I missing something in my php syntax to handle cursors?

Answer 1

Based on the chat discussions for 3 groups, and this provided SQLFiddle for the test data (there is not much data there).

Due to testing data with a sliding window where now() refers to this data, the following variable was used to freeze now() . Just to facilitate testing and verifying output.

So, let's end up changing 4 links in the code used (note that group 3 uses it twice).

Variable now() :

 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'; 

Request:

 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 (at the moment, my client tool is a text call):

See my general comments at the top of this my answer for advanced use of variables.