CODEWITHSUNDEEP
phpsql
Fares Nasser
Fares Nasser

Reputation: 13

SQL Queries Inside Loop

I have a question ,performing db queries inside of a foreach loop.

Cause Performance Problems? 

            <?php 
            $conn=$database->openConnection();

            $stmt=$conn->query('SELECT id,uuid,banned_by_uuid,until FROM bans WHERE active = 1');
            $s = microtime(true);
            foreach($stmt as $row){

                $playerName=current($conn->query('SELECT Playername FROM SYSTEM WHERE UUID="'.$row['uuid'].'"')->fetch());
                $playerNameBannedBy=current($conn->query('SELECT Playername FROM SYSTEM WHERE UUID="'.$row['banned_by_uuid'].'"')->fetch());
                $playerNamePrint=$playerName == null ? 'Undefined' : htmlspecialchars($playerName);
                $playerBannerPrint=$playerNameBannedBy == null ? 'Undefined' : htmlspecialchars($playerNameBannedBy);

                echo '<tr><td>'.$row['id'].'</td><td>'.$playerNamePrint.'</td><td>'.$playerBannerPrint.'</td><td><i class="material-icons">access_time</i> '.ago($row['until']).'</td><td><button class="btn btn-icon white" onClick="getReason('.$row['id'].')" data-toggle="tooltip" data-placement="top" data-original-title="Click here to see Ban Reason"><i class="material-icons">info</i></button></td></tr>';
            }
            $e = microtime(true);
        ?>

Upvotes: 0

Views: 38

Answers (3)

Mahdi Aslami Khavari
Mahdi Aslami Khavari

Reputation: 1985

you can use this query

SELECT (CASE WHEN s.uuid = b.uuid THEN 1 ELSE 0 END) AS Separator, s.*
FROM system AS s JOIN bans AS b 
                ON s.uuid = b.uuid OR s.uuid = banned_by_uuid
WHERE b.active = 1

and you can understand the different between your two result use separator column.

Upvotes: 0

bbbbburton
bbbbburton

Reputation: 63

Yes, if you have many records inside of the bans table, executing 2 more queries per returned record, can cause issues for sure.

Try using joins like so:

SELECT id,uuid,banned_by_uuid,until, banned_players.Playername banner_player_name, banned_by_players.Playername banned_by_player_name
FROM bans
INNER JOIN SYSTEM banned_players ON banned_players.uuid = bans.uuid
INNER JOIN SYSTEM banned_by_players ON banned_by_players.uuid = bans.banned_by_uuid
WHERE active = 1

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

you could get all the result with single query 

    SELECT b.id,b.uuid,b.banned_by_uuid,b.until, sa.Playername, sb.Playername banned
    FROM bans b
    INNER JOIN SYSTEM sa on sa.UUID = b.uuid 
    INNER JOIN SYSTEM sb on sb.UUID = b.banned_by_uuid 
    WHERE b.active = 1

Upvotes: 1

Related Questions