CODEWITHSUNDEEP
phpperformanceoopoptimizationiteration
JohnnyQ
JohnnyQ

Reputation: 5119

Which is the best approach for displaying multiple mysql rows in php? (fetch then display or fetch ALL then display)

as the question states.

I have implemented a function wherein it fetches multiple rows from a database (*1) and then (*2) instantiate each row as an Object. After instantiation, the Objects are then stored into an array and then return the result to caller function and then iterates through the result array and displays each Object and add html formatting for each.

Here is the snippet of the code:

function find_all() {
    //(*1) Fetch 30 comments from DB
    $sql  = 'SELECT * FROM comments';
    $sql .= ' ORDER BY datetime DESC LIMIT 30';
    return find_by_sql($sql);
}

function find_by_sql($sql='') {
    global $database;
    $result_set = $database->query($sql);
    $object_array = array();

    while($row = $database->fetch_array($result_set)) {
        //(*2) Instantiate each row to a Comment object
        //     and then stores each comment to an object array
        $object_array[] = Comment::instantiate($row); 
    }
    return $object_array;
}


//(*3) Format and display each result.
$comments = find_all();
foreach ( $comments as $comment ) {
    // Not sure if syntax is correct.. anyhow..
    echo "<li>$comment->get_text()</li>";
}

However, I like the above approach since it's cleaner, easier to read, maintainable, and more OOP. But the problem is it takes a longer time to display than just simply iterating through each result than display each result once it's fetched, like so:

while ($row = mysql_fetch_array($sql)) {
    echo "<li>$row['text']</li>";
}

I know the reason behind why it is slow. What I want to know is there a better way to solve the problem above?

While caching might be a good idea, it won't help because I need an updated list every time the list is fetched.

Upvotes: 0

Views: 205

Answers (1)

CoursesWeb
CoursesWeb

Reputation: 4237

I think it can be a little faster if the script gets only the part you are interested in the result set, because fetch_array() returns 2 arrays with the same result set: associative, and numeric.

By adding MYSQLI_ASSOC (if you use mysqli): mysqli_fetch_array($result, MYSQLI_ASSOC), or try with mysql_fetch_assoc(), the script receives only the associative array. You can test in pmpMyAdmin to see the diferences.

Upvotes: 1

Related Questions