SP returning only 1 row need some help

[jemz]

I have problem in my SP it only return 1 record,supposedly it’s 7 records,. I don’t know if my SP is having problem.or in my Php that calling my SP.

please help me

Thank you in advance.

here is code

BEGIN

     DECLARE done INT DEFAULT FALSE;
     DECLARE theid varchar(20);
      DECLARE cur1 CURSOR FOR SELECT id from userd where uid = logid;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

     OPEN cur1;
     read_loop:LOOP
        FETCH cur1 into theid;
        
        IF done THEN
          LEAVE read_loop;
        END IF;
        
          select cols.,
                 cols,
                 cols,
                 cols
               from userd ud  inner join devloc dl
                on ud.devid = dl.dev_id
                left outer join infotbl info
                on  dl.dev_id =info.infodev_id
               where dl.dev_id = theid AND ud.uid = logid
                order by dl.dtecrted desc LIMIT 0,1;
       
      END LOOP;  
      CLOSE cur1;
            
END


In Php

$connection = mysqli_connect("localhost", "root", "", "mydb");

//INVOKE the SP
$result = mysqli_query($connection,
    'CALL getPost('.$loginid.')') or die("Query fail: ");

//loop the result set
while ($row = mysqli_fetch_array($result)){

    $A[]= $row['position'];
    $B[]=$row['allowance'];
    $C[]=$row['incharge'];
    $D[]=$row['approvedby'];


}

$return_data=array(
    'position'=>$A,
    'allowance'=>$B,
    'incharge'=>$C,
    'approvedby'=>$D,

);


echo json_encode($return_data);

[SpacePhoenix]

When the query is tried direct against the database how many records are returned?

[DaveMaxwell]

@jemz

LIMIT 0,1;

Might have something to do with that

Is there more to this cursor that you didn’t put? If not, then why aren’t you doing it with just a straight SQL statement. It’s more efficient and less CPU expensive (cursors are notorious for being hard on CPU cycle). A straight select will get you the same thing - though that check of dev_id being equal to devid AND id from the same table? Looks odd to me, but that’s the equivalent to what you’re doing…

SELECT col1
     , col2
     , col3
     , col4
  FROM userd ud  
 INNER JOIN devloc dl ON ud.devid = dl.dev_id AND dl.dev_id = ud.id
  LEFT OUTER JOIN infotbl info ON dl.dev_id =info.infodev_id
 WHERE ud.uid = logid
 ORDER BY dl.dtecrted DESC;

[jemz]

I get 7 tabs result in each tab I get 1 record

[jemz]

Hi @DaveMaxwell,

I hope I can explain this well to you., this part here

DECLARE cur1 CURSOR FOR SELECT devid from userd where uid = logid;

then I pass the result to “theid”

theid = contains this values “002,004,005,006,007,008,009”.

then I need to loop so that i can get those individual theid
records in devloc

select cols.,
cols,
cols,
cols
from userd ud inner join devloc dl
on ud.devid = dl.dev_id
left outer join infotbl info
on dl.dev_id =info.infodev_id
where dl.dev_id = theid AND ud.uid = logid
order by dl.dtecrted desc LIMIT 0,1;

I need to put Limit 0,1 so that i can only get the last records of each individual “theid” in devloc table

for example

001 having 1001 records in devloc
002 having 200 records in devloc
003 having 50 records in devloc
004 having 1000 records in devloc
005 having 600 records in devloc
006 having 700 records in devloc

I need to get only the last record of each of them that’s why I put Limit 0,1

I don’t know if there are other ways I can get each invidual records,without using cursor.

Thank you in advance.

[SpacePhoenix]

What’s the:

• Table structure of the tables concerned?
• The desired/required output?