SQL Queries Inside Loop

Question

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);
        ?>

ScaisEdge · Accepted Answer · 2018-08-26 12:00:30Z

1

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

answered Aug 26, 2018 at 12:00

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bbbbburton · Accepted Answer · 2018-08-26 12:03:19Z

1

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

answered Aug 26, 2018 at 12:03

bbbbburton

637 bronze badges

Comments

Mahdi Aslami Khavari · Accepted Answer · 2018-08-26 12:03:38Z

0

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.

answered Aug 26, 2018 at 12:03

Mahdi Aslami Khavari

1,99119 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL Queries Inside Loop

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related