How to avoid doing a query each time loop runs? (optimize code)

Question

$query = $db->query("SELECT * 
                     FROM users 
                     ORDER BY username ASC");
while($user = $query->fetch_object()) {
$if_inside = $db->query("SELECT member_id 
                         FROM project_members 
                         WHERE member_id='$user->id' 
                         UNION 
                         SELECT admin_id 
                         FROM project_admins 
                         WHERE admin_id='$user->id'")->num_rows;
if($if_inside < 1) { // if not already in project 
?> 
<option value="<?php echo $user->username; ?>"> 
               <?php echo $user->username; ?> </option>
 <?php
 } 
 }
 ?>

How would I optimize the code above? If I have 100 users in my database then it would query 100 times, and it is already slow (i have 2 users)

Is there a better way to check if an user is already in the project?

Why don't you fetch all the users from the project then run them against the id of said user to see if they are in the project? Or you could do some sort of JOIN — Darren
– Darren, Commented Jul 29, 2014 at 1:02
I have to get all users from both project_members and project_admins, thats why I am using an UNION. How would I go about matching two values received from the query TO the user id? — Jack
– Jack, Commented Jul 29, 2014 at 1:04

spasticparanoia · Accepted Answer · 2014-07-29 01:15:57Z

2

I think this is what you're looking for:

$sql = $db->query("Select distinct username from users where username not in (select member_id from project_members union select admin_id from project_admins) order by username ASC");
$queryResult = $sql->fetch_all();

foreach($queryResult as $username) {
    echo "<option value='" . $username . "'>" .  $username . "</option>";
}

Do a single hit against the database for all usernames not in either project_members or project_admins and then foreach over the results to display.

answered Jul 29, 2014 at 1:15

spasticparanoia

423 bronze badges

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

Collectives™ on Stack Overflow

How to avoid doing a query each time loop runs? (optimize code)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related