First off, I know the title is generic and not fitting. I just couldn't think of a title that could describe my problem.
I have a table Recipients in MySQL structured like this:
id | email | status
1 foo@bar S
2 bar@baz S
3 abc@def R
4 sta@cko B
I need to convert the data into the following XML, depending on the status field. For example:
<Recipients>
<RecipientsSent>
<!-- Have the 'S' status -->
<recipient>foo@bar</recipient>
<recipient>bar@baz</recipient>
</RecipientsSent>
<RecipientsRegexError>
<recipient>abc@def</recipient>
</RecipientsRegexError>
<RecipientsBlocked>
<recipient>sta@cko</recipient>
</RecipientsBlocked>
</Recipients>
I have this PHP code to implement this ($recipients contains an associative array of the db table):
<Recipients>
<RecipientsSent>
<?php
foreach ($recipients as $recipient):
if ($recipient['status'] == 'S'):
echo "<recipient>" . $recipient['email'] . "</recipient>";
endif;
endforeach;
?>
</RecipientsSent>
<RecipientsRegexError>
<?php
foreach ($recipients as $recipient):
if ($recipient['status'] == 'R'):
echo "<recipient>" . $recipient['email'] . "</recipient>";
endif;
endforeach;
?>
</RecipientsRegexError>
<?php /** same loop for the B status */ ?>
</Recipients>
So, this means that if I have 1000 entries in the table and 4 different status' that can be checked, it means that there will be 4 loops, each one executing 1000 times.
How can this be done in a more efficient manner? I thought about fetching four different sets from the database, meaning 4 different queries, would that be more efficient? I'm thinking it could be done with one loop but but I can't come up with a solution.
Any way this could be done with only one loop?
