从 sql 中区分 foreach 循环中的重复项 [英] Distinguish duplicates in a foreach loop from sql

问题描述

我正在创建一个工作申请管理系统.在后台，管理员需要能够看到所有已申请工作并等待回复的人.这是我的 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).

理想情况下，我希望列表按 job_id 排序，所以我不想按 user_id 排序.

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

我希望这是有道理的.

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

推荐答案

替代方案 - 让数据库为您完成工作:

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

然后您的结果集将有可用的字段appl_count"，如果大于 1，则附加该类.这消除了在应用代码中进行任何针锋相对的会计处理的需要.

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.

这篇关于从 sql 中区分 foreach 循环中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！