duanhan1392
duanhan1392
2011-07-05 21:30

uksort帮助 - 在单一需求帮助中使用GROUP BY

已采纳

Hello I have an array that looks like this after passing it through uksort()

    Array
(
    [3] => Array
        (
            [job_id] => 4
            [job_title] => Supercar Test Driver
            [company_name] => McLaren
            [logo_small] => small_mclaren001.png
            [logo_large] => large_mclaren002.png
            [employer_id] => 3
        )

    [2] => Array
        (
            [job_id] => 3
            [job_title] => Recruitment Consultant - Driving
            [company_name] => MoovJob.com
            [logo_small] => small_rac001.png
            [logo_large] => large_rac002.png
            [employer_id] => 2
        )

    [1] => Array
        (
            [job_id] => 5
            [job_title] => Postal Worker / Post Person
            [company_name] => Royal Mail
            [logo_small] => small_royalmail001.png
            [logo_large] => large_royalmail002.png
            [employer_id] => 4
        )

    [0] => Array
        (
            [job_id] => 6
            [job_title] => Another Job
            [company_name] => MoovJob.com
            [logo_small] => small_rac001.png
            [logo_large] => large_rac002.png
            [employer_id] => 2
        )

)

However if in my SQL that returns this array I add GROUP BY company_name the final entry in the array dissapears, what I am trying to achieve is to place the job title under the company name, but only show the company name once, something similar to this,

Company Name 1
Job Title 1 Job Title 2 Job Title 3

Company Name 2
Job Title 4

Company Name
Job Title 5 Job Title 6

I am trying to achieve this, by doing the followng in my view, however I can only show one vacancy, what am I doing wrong?

    <?php $oldemp   = "";?>
<?php foreach($jobs as $key => $value) : ?>
   <?php if ($oldemp != $value['company_name']) : ?>
        <?php $oldemp = $value['company_name']; ?>
        <section class="employer">
            <div class="job_holder">
                <img src="<?php echo base_url(); ?>media/uploads/users/<?php echo $value['logo_large']; ?>" width="198" height="148" alt="<?php echo $value['company_name']; ?>"/>
                <dl>
                    <dt><?php echo count($key); ?></dt>
                    <dd>Matches</dd>
                </dl>
    <?php endif;?>
            <span> + <a href="/jobwall/getjob/<?php echo $value['job_id']; ?>"><?php echo $value['job_title']; ?></a></span>
            </div>
        </section>
<?php endforeach; ?>


    function jobcmp($job1, $job2)
    {
        return strcmp($job1['company_name'], $job2['company_name']);
    }
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答

  • dongtao9095 dongtao9095 10年前

    Although it would be possible to display the data using your approach I normally find it much more straightforward to create a second array in a more convenient format:

    $groupedJobs = array();
    foreach ($jobs as $data) {
        if (!isset($groupedJobs[$data['company_name']])) {
            $groupedJobs[$data['company_name']] = array();
        }
        $groupedJobs[$data['company_name']][] = $data;
    }
    

    this creates a new array using the company name as the key and that company's jobs in a nested array as the value, giving you something like this:

    ['MoovJob.com'] => Array
        (
            [0] => Array
                (
                    (job data)
                ),
            [1] => Array 
                (
                    (etc.)
                )
        ),
    ['McLaren'] = Array
        (
            (etc)
        )
    

    it is then much easier to loop through this array and display:

    foreach ($groupedJobs as $companyName => $jobs) {
        echo $companyName;
        foreach ($jobs as $job) {
            echo $job['job_title'].'<br />';
        }
    }
    
    点赞 评论 复制链接分享
  • dongshen7407 dongshen7407 10年前

    It would help if you showed the SQL query. However, the row that disappears is obviously related to the GROUP BY company_name you add.

    When you add a GROUP BY groupfield, all rows with same groupfield "collapse" into one row. The columns that depend on that field, like company_logo can usually safely stay in the SELECT list. All others should be removed or changed to use an aggregate function like SUM() or COUNT().

    So, if you want to show numbers of jobs for every company, use COUNT(job_id) or COUNT(*).

    In your case, you have two options I think.

    • First option is to NOT use GROUP BY, perhaps only ORDER BY and use PHP code to show what you need.

    • Second option is to use GROUP BY and the aggregate GROUP_CONCAT() function.

    You can use it like:

    SELECT
        company_name
      , GROUP_CONCAT( job_id
                      ORDER BY job_id
                      SEPARATOR ', ' )
          AS job_ids
      , GROUP_CONCAT( job_title
                      ORDER BY job_id
                      SEPARATOR ', ' )
          AS job_titles
      , GROUP_CONCAT( CONCAT(job_id, '-', job_title)
                      ORDER BY job_id
                      SEPARATOR ', ' )
          AS job_ids_and_titles
      , company_name
      , logo_small
      , logo_large
      , employer_id
    FROM 
      ...
    GROUP BY company_name
    
    点赞 评论 复制链接分享
  • doujing5937 doujing5937 10年前

    Your array is not sorted correctly, employees with the same id should be one after another, for this array you should use usort method like this

    usort($jobs, function($a, $b) {
     if($a['employee_id'] == $b['employee_id'] return 0;
     return ($a['employee_id'] < $b['employee_id']) ? 1 : -1;
    })
    

    The other way and I think the better one for your purpose is to use array_walk to prepare data for this section loop. Method below will remove duplicates from the table and join all job_titles for the same employees in a one key in primary employee record.

    array_walk($jobs, function($emp, $index) use(&jobs) {
      static $primary;
    
      if(isset($primary[$emp['employee_id']) {
        $jobs[$primary[$emp['employee_id']]]['job_titles'][$emp['job_id'] = $emp['job_title'];
        unset($jobs[$index]);
      } else {
        $primary[$emp['employee_id']] = $index; //remeber index of primary entry
      }
    });
    
    点赞 评论 复制链接分享
  • duanji1056 duanji1056 10年前

    It looks like the array is sorted by job_title DESC when it should be sorted by company_name

    点赞 评论 复制链接分享

相关推荐