CODEWITHSUNDEEP
phpmysqlsql
MikeeeG
MikeeeG

Reputation: 341

Distinguish duplicates in a foreach loop from sql

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>

Upvotes: 0

Views: 151

Answers (3)

Sam Dufel
Sam Dufel

Reputation: 17608

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.

Upvotes: 0

David Fleeman
David Fleeman

Reputation: 2648

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.

Upvotes: 3

Cillier
Cillier

Reputation: 1021

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
     } 
}

Upvotes: 0

Related Questions