Wednesday 2 July 2008

PHP: Number of rows from query

The idea comes from the developers of Oracle ADF, I have seen it work on JDeveloper and I thought that I can use it in PHP. The trick here is to place the entire query inside a SELECT count(*) from ($query) statement.

Once you know what to do implementation is as easy as witting the code below. The only comment is that the function references an already initialized external mysqli class variable that connects to the database, which if you feel like it may also be passed as a function parameter.

    /**
      * returns the number of records  of the query passed as parameter 
      */
    function numRecordsOfQuery( $a_query) 
    {
        global $mysqli;

        $count_query = " 
                   SELECT count(*) as TotalLines 
                     FROM ($a_query) MyQuery";
        $result = $mysqli->query($count_query);
        // echo $count_query;
        $row = $result->fetch_array( MYSQLI_ASSOC);
        $numRecords = $row['TotalLines'];
        $result->close();
        
        return $numRecords;
    }

Note The MyQuery references is mandatory. MySQL will return an error if the enclosed query is not referenced.

No comments :