CODEWITHSUNDEEP
phpsqlcoding-style
ntc
ntc

Reputation: 257

Should I go to the database once and get all the data or twice and get what I need each time

I want some advice on structuring some (basic) php code.

I need to display data from a table in different places. The rows that need to be displayed in each section can be identified by a flag.

I'm not sure if the best way to do this is to go to the database once and seperate the data into 2 variables as I loop through it or if I should go to the database twice, using sql to call only the data I need each time.

For those who want to see it in code:

Method A:

// Create and execute a MySQL query
function tasks_not_done(){
        // Open a PDO dtabase connection
        $link = new PDO(DB_INFO, DB_USER, DB_PASS);

        $sql = " SELECT title, created_date
                FROM todos
                WHERE list_id = ? AND checked = '0'
                ORDER BY created_date DESC";
        $stmt = $link->prepare($sql);
        $stmt->execute(array($_REQUEST['list_id']));

        // loop throught all the rows
        while($row = $stmt->fetch()) {
            $date = strtotime($row['created_date']);
            $date = date('d/m/y' , $date);
            echo '<div class="task">' . "\n";
            echo '<span class="taskcdtate">' .$date . '</span>'. '<span class="tasktitle"> ' . $row['title'] . ' </span>' . "\n";
            echo '</div>';

            }
       $stmt->closeCursor();
}

Method B

function tasks_all(){
       // Open a PDO dtabase connection
       $link = new PDO(DB_INFO, DB_USER, DB_PASS);

       $sql = " SELECT title, created_date, checked
                FROM todos
                WHERE list_id = ?
                ORDER BY created_date DESC";
        $stmt = $link->prepare($sql);
        $stmt->execute(array($_REQUEST['list_id']));

        // loop throught all the rows
        $tasks['not_done'] = "";
        $tasks['done'] = "";

        while($row = $stmt->fetch("FETCH_ASSOC")) {
            $date = strtotime($row['created_date']);
            $date = date('d/m/y' , $date);

            if($row['checked'] =='0'){
                $tasks['not_done'] .= '<div class="task">' . "\n";
                $tasks['not_done'] .='<span class="taskcdtate">' .$date . '</span>'. '<span class="tasktitle"> ' . $row['title'] . ' </span>' . "\n";
                $tasks['not_done'] .='</div>';
                } elseif ($row['checked'] =='1') {
                $tasks['done'] .= '<div class="task">' . "\n" .
                $tasks['done'] .='<span class="taskcdtate">' .$date . '</span>'. '<span class="tasktitle"> ' . $row['title'] . ' </span>' . "\n";
                $tasks['done'] .='</div>';
                }

            }
       $stmt->closeCursor();
       return $tasks;
}

thanks

Upvotes: 1

Views: 1551

Answers (5)

Martin
Martin

Reputation: 1622

Why not do it more elegantly:

$sql = " SELECT title, created_date
        FROM todos
        WHERE list_id = ? AND checked = ?
        ORDER BY created_date DESC";
$stmt = $link->prepare($sql);

$stmt->execute(array($_REQUEST['list_id'], '0'));
# do stuff

# and later...
$stmt->execute(array($_REQUEST['list_id'], '1'));

This avoids code duplication and (depending on the DB backend) should be faster too.

Upvotes: 0

seth
seth

Reputation: 83

Method A is my choice.

I think Furnes has some of the best answers so far, but here are a few other things to consider...

  1. From what I can tell... your code isn't that complex in the first place - should you really be worrying about performance with this snippet of code?

  2. Leveraging others' code usually leads to better performance, flexibility and extensibility in the long run. Less of your own code is a good thing. If you choose to delegate the work to the database, there's plenty of optimization that you can literally drop in there, unlike if you rely on your own code. For example, adding indexes to the table you're querying might make more difference than choosing which programming route to take.

  3. If you still want to know the absolutely most efficient way ... why not time/profile the code? See which method is faster using concrete metrics using your own dataset rather than playing the theoretical game.

Upvotes: 1

Furnes
Furnes

Reputation: 382

There is no right answer to this kind of question, but I can give you some pointers.

My general rule is: get the data you need when you need it. But there are a lot of things that could affect this.

  1. Will you always use data from both queries ? if yes, you should get all in one query. If Never, meaning they exclude each other, the answer is no.
  2. If you always need query 1, and sometimes query, you have to make a few more considerations. Does query 1 take much more time if you include query 2? How often do you need query 2, almost always or almost never.
  3. What happens if someone else updates data at the same time. Let's say you get both at once, and someone else updates data that affects query 2. This means you're using outdated data. If you fetch the data when you need it, you might get correct updated data.

Upvotes: 4

zawhtut
zawhtut

Reputation: 8551

Why not doing this ?

$sql = " SELECT title, created_date
            FROM todos
            WHERE list_id = ? AND checked = '$filterValue'
            ORDER BY created_date DESC";

Upvotes: -1

user111013
user111013

Reputation:

I can't see the second query here, but you could likely use a JOIN to bring in the additional data.

eg if I have a table of Customers, and a table of Loyalty Cards, but not all customers may have a loyalty card, I could do this: 

SELECT c.CustomerID, c.CustomerName, l.LoyaltyCardType, l.LoyaltyCardBalance
FROM Customers c 
     LEFT JOIN LoyaltyCardInformation l 
     ON c.CustomerID = l.CustomerID

For MySQL the join syntax is here: http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 0

Related Questions