Should I be using multiple SQL queries or multiple loops in my client code?

Question

I'm building a small internal phone number extension list.

In the database I have two tables, a people table, and a numbers table. The relationship is a one-to-many respectively.

I want to display the results in a single HTML table with one person per row but if they have multiple numbers it shows those in a single column with a rowspan on the person row to compensate.

Now, to get the results from the database to work with, I can either do:

(pseudocode)

SELECT id, name
FROM people

foreach result as row {
  SELECT number
  FROM numbers
  WHERE numbers.person_id = row['id']
}

This would mean that I'm doing one query to get all users, but then if I have 100 users, I'm performing 100 additional queries to get the numbers for each user.

Instead I could do it like this:

(pseudocode)

SELECT number, person_id
FROM numbers

SELECT id, name
FROM people

foreach people as person {
  echo name
  foreach numbers as number {
    if (number.id = person.id) {
      echo number
    }
  }
}

So, essentially it is doing the exact same thing except instead I do two queries to get all the results into arrays and then loop through the arrays to format my tables.

Which method should I be using or is there a better way to do this?

Emil Vikström · Accepted Answer · 2012-10-18 20:10:23Z

4

The common way is to do a regular JOIN:

SELECT id, name, number
FROM people, numbers
WHERE people.id = numbers.person_id;

You can either add an ORDER BY to get the numbers in order, or you could create an array with a single pass over the resultset, and then loop through that array.

You can also consider a GROUP_CONCAT to concatenate all the numbers for the same person:

SELECT id, name, GROUP_CONCAT(number SEPARATOR ', ')
FROM people, numbers
WHERE people.id = numbers.person_id
GROUP BY people.id;

Since you are even asking this question: I cannot stress the fact that you should pick up an introductory book on database design. It helped me wonders to learn the theories behind relational databases.

edited Oct 18, 2012 at 20:10

answered Oct 18, 2012 at 20:04

Emil Vikström

92.3k17 gold badges144 silver badges178 bronze badges

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

8 Comments

Eric H Over a year ago

I believe your query is what the asker is already doing in his nested loop example, however, your GROUP_CONCAT idea is a great one that I will probably end using.

Emil Vikström Over a year ago

morrty still uses two queries in the second example. Not too nice to reimplement joining imo.

Eric H Over a year ago

@EmilVikström Yep, sorry. Did not see the two queries at the top and I assumed he was joining! This reinforces my vote for your answer haha.

Ryan Mortier Over a year ago

Ah, actually, I don't think this will work on second thought, unless the query can be modified. Since I was trying to keep the OP simple, I didn't mention that the numbers table has another column with type (mobile, work, extension, etc). Can the current query be modified to allow this?

Ryan Mortier Over a year ago

@EmilVikström

SELECT locations.name AS location, contacts.name, contacts.title, 	GROUP_CONCAT(CONCAT(' {', types.name, '} ', numbers.number) SEPARATOR ' | ') AS numbers FROM contacts 	INNER JOIN numbers ON contacts.id = numbers.contacts_id 	INNER JOIN locations ON contacts.locations_id = locations.id 	INNER JOIN TYPES ON numbers.types_id = types.id GROUP BY contacts.id

- This seems to work.

|

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-10-18 20:05:51Z

4

You probably want to execute just one query. Something like

select people.id, people.name, group_concat(numbers.number)
from people 
inner join numbers on numbers.id = people.id
group by people.id, people.name
order by people.name

Then you can loop over the result set with simple php code.

answered Oct 18, 2012 at 20:05

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

2 Comments

Ryan Mortier Over a year ago

I think this might work, however, it throws all the numbers into a single field separated by a comma. Is there another separator it can use other than comma?

Mike Sherrill 'Cat Recall' Over a year ago

Yes. GROUP_CONCAT syntax. You can even use HTML markup as the separator: group_concat(numbers.number separator '<br/>').

Marlin Pierce · Accepted Answer · 2012-10-18 20:10:05Z

It depends, and you may have to time it to find out. Doing multiple queries is a lot of network turns if your database is on a different computer than your web server, so often this takes longer. However, if your database server is on the same computer as your web server, this might not be an issue.

Also consider the time it will take to look up the number in the array. As an array you are doing a linear order O(N) search. If you can put it in a hash, the lookup will be much faster, and the two query approach may be faster, but not if you spend a lot of time looking up the answer in your array.

Using a join to get it into one query, may be fastest, as the numbers will be associated with the people, depending on your container structure you are storing the data into to be accessed in your foreach loop.

Jacob Buter · Accepted Answer · 2012-10-18 20:05:20Z

0

Use a stored procedure or function to retrive the data, don't wite the sql in your programm

answered Oct 18, 2012 at 20:05

Jacob Buter

261 bronze badge

Comments

Olaf Dietsche · Accepted Answer · 2012-10-19 09:22:06Z

0

You should do neither. You can do one query (join) over the tables:

select name, number
from people p, numbers n
where p.id = n.person_id
order by name, number;

and then just one loop:

$link = mysqli_connect(...);
$query = 'select name, number from people p, numbers n where p.id = n.person_id order by name, number';

if ($result = mysqli_query($link, $query)) {
    $person = '';
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
        if ($row['name'] !== $person) {
            $person = $row['name'];
            echo $person;
        }

        echo $row['number'];
    }
}

edited Oct 19, 2012 at 9:22

answered Oct 18, 2012 at 20:06

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Collectives™ on Stack Overflow

Should I be using multiple SQL queries or multiple loops in my client code?

5 Answers 5

8 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

8 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related