CODEWITHSUNDEEP
phpmysqlsqlmysql-manager
Sergey Scopin
Sergey Scopin

Reputation: 2245

Get second recordset from stored procedure

I have a stored procedure in MySQL. It has 2 select queries there. Here is part of it.

@query="select SQL_CALC_FOUND_ROWS...LIMIT 1, 20"
    PREPARE query FROM @query;
    EXECUTE query;  
    DEALLOCATE PREPARE query;
    select FOUND_ROWS() as numberOfRows;

In sql manager for my sql I have two resultsets. One is query, other is number of its rows. I get first resultSet with ez sql.

$getReadingsByStreet=$this->db->get_results("call `mbusGetClientsByStreet`('".$_SESSION['street']."',".$_SESSION['page'].",".$_SESSION['bild'].",".$_SESSION['korp'].",".$_SESSION['flat'].",@x);");
            if ($getReadingsByStreet)
            {
                foreach ($getReadingsByStreet as $row) 
                {
                    $_SESSION['pageAmount']=$row->numberOfRows;
                    $res.="<tr><td><a class='serialLink' href='./'>".$row->serial_num."</a></td><td>".$row->value."</td><td>".$row->street." ".$row->bild." ".$row->korp." ".$row->flat."</td></tr>";
                }
        }

Unfortunately I can't get numberOfRows. $_SESSION doesn't change it's value. How can I get number of rows in php? It has LIMIT, so I need SQL_CALC_FOUND_ROWS to get it.

Upvotes: 1

Views: 309

Answers (1)

ninjabber
ninjabber

Reputation: 371

Is numberOfRows.$_SESSION always 20?

follow up

ok, now I understand your problem as it's better described in the comments :))

what you miss is SQL_CALC_FOUND_ROWS

use it like that:

SELECT SQL_CALC_FOUND_ROWS * FROM tab_a limit 50;
SELECT FOUND_ROWS();

if you omit it , it will always return 50, but if you include it than the actual count as it would be without the limit

Upvotes: 1

Related Questions