Distinguish duplicates in a foreach loop from sql

Question

Hi so Im creating a job application management system. in the backend the admin needs to be able to see all those who have applied for a job and awaiting a response. here is my sql query:

'SELECT * FROM jp_applications WHERE application_status = "Awaiting Response" ORDER BY job_id'

my problem is that once i loop through and output this in a list of applicants I want to be able to add a class to that list element if it is a duplicate applicant. (one person applying for more than one job).

ideally i want the list to be ordered by the job_id so i dont want to order it by say user_id.

I hope this makes sense.

<ul>
    <li class="duplicate">Joe Bloggs</li>
    <li>Michael Jackson</li>
    <li>Gandalf the Grey</li>
    <li>Mahatma Gandhi</li>
    <li class="duplicate">Joe Bloggs</li>
    <li>Daffy Duck</li>
    <li>Sponegbob Squarepants</li>
    <li>Will Smill</li>
</ul>

Can you give an example of the output you are looking for? – Sébastien Oct 15 '13 at 16:00 — Sébastien, Oct 15 '13 at 16:00

score 3 · Accepted Answer · answered Oct 15 '13 at 16:09

3

Alternative - let the database do the work for you:

SELECT j.*, c.appl_count FROM jp_applications j
    INNER JOIN (SELECT user_id, count(1) as appl_count FROM jp_applications
            WHERE application_status = "Awaiting Response"
            GROUP BY user_id) c on c.user_id = j.user_id
WHERE j.application_status = "Awaiting Response"
ORDER BY j.job_id

Then your resultset will have the field 'appl_count' available, if greater than 1, append the class. This removes the need to do any tit-for-tat accounting in the app code.

answered Oct 15 '13 at 16:09

David Fleeman

2,588
14
17

+1 It seems more elegant than a foreach in PHP. Will it be much costlier than a simple query (without JOIN)? – Sébastien Oct 15 '13 at 16:14
instant success!!! thank you so much. I half knew that it was possible with a query but past WHERE and ORDER BY Im not so hot on queries. thanks again – MikeeeG Oct 15 '13 at 16:44
1

@Sebastien, the short answer is the query will be costlier than before, however, in the total solution (especially as the data set grows larger), the query solution should be more efficient than doing the array accounting in PHP. There is no simple answer to performance optimizations without considering many, many factors. – David Fleeman Oct 15 '13 at 17:17
Hey @DavidF Could you give me some help ? I have a similar problem... http://stackoverflow.com/q/19390581/2054434 – PlayHardGoPro Oct 15 '13 at 20:50
1

@PlayHardGoPro I added a comment to your question linking back to this post. I would really recommend creating a view for the query, and then using the approach from this ticket. Again, you would have to validate that performance of the query is not abysmal. – David Fleeman Oct 15 '13 at 20:55

score 0 · Answer 2 · answered Oct 15 '13 at 16:03

On each loop iteration, check if the user_id key exists in your "already_matched" array. If it doesn't, append your class. Otherwise, do nothing

semi php-ish pseudo code:

$already_matched = array(); 
foreach($results as $result) { 
    if(!array_key_exists($result['user_id'],$already_matched)) { 
         $already_matched[$result['user_id']] = true;
        // append the css class. This is the first match
     }
     else { 
        // This is the second and all subsequent matches
     } 
}

score 0 · Answer 3 · edited Oct 15 '13 at 16:11

You can efficiently detect duplicates using a php array:

$applicants = array();
// $results = query results

foreach($results as $application) {
    $is_duplicate = !empty($applicants[$applicants['user_id']]);

    echo '<span class="' . ($is_duplicate ? 'duplicate' : '') . '">Application here</span>'=

    $applicants[$application['user_id']] = $application;
}

I left out all the database handling code, since it seems like you have that already under control. The point is to create an entry in an array indexed by the user id, then check that on each iteration to see if you've already looped over that user.

Distinguish duplicates in a foreach loop from sql

3 Answers3

Linked