php sequence numbers by id group

Question

I want to create a sequence numbering by ID group which comes from a mysql query.

The mysql table looks like this:

--------------------------
id | user_id | company_id 
--------------------------
1  |   61    |     1 
2  |   71    |     1 
3  |   81    |     1 
4  |   91    |     2 
5  |   10    |     2
6  |   11    |     2

And I would like to output soething like this:

Company: 1 , User: 61, position: 1
Company: 1 , User: 71, position: 2
Company: 1 , User: 81, position: 3
Company: 2 , User: 91, position: 1
Company: 2 , User: 10, position: 2
Company: 2 , User: 11, position: 3

I come up with this code but its not working as I wanted to.

    $sql = 'SELECT `id`, `user_id`, `company_id` FROM `user_company` ORDER BY `company_id`';
    $result = $conn->query($sql);
    
    if($result->num_rows){
        $id = '';
        while($obj = $result->fetch_object()){
            if($id != $obj->id){
                $seq = 1;
                $id = $obj->id;
            }else{
                $seq++;
            }
            
            echo 'Company: '.$obj->company_id.', User: '.$obj->user_id.', position: '.$seq.'<br/>';
        }
    }

How can I do this?

What **exactly** is not working with the given code? What have you tried to resolve the problem? — Nico Haase, Aug 25 '22 at 12:53
@akina please provide a good dupe target demonstrating this resolving technique. — mickmackusa, Aug 25 '22 at 13:00
@NicoHaase Tried to define `$seq` outside the loop, also tried multiply it after the echo, tried comparing company_it in the IF — Laci K, Aug 25 '22 at 13:01
You want to reset your sequence when the _company_ id changes, yet you are using `$obj->id` — CBroe, Aug 25 '22 at 13:05
Hi Laci, even with your comment rely to Nico, this question is still lacking the necessary [mcve]. Namely, you've told us what doesn't work, but you haven't explained _how_ it falls short of your desired output. Please edit your question to show the output that your current code produces so that it's clear to us where, exactly, you need help. — TylerH, Aug 25 '22 at 13:53

score 1 · Answer 1 · answered Aug 25 '22 at 13:24

You could use row_number to get the desired result:

select company_id as company, 
       user_id  as user_id ,
       row_num  as row_num 
from 
    ( select *,row_number() over(partition by company_id order by id asc) as row_num
      from user_company
     ) as tbl 
order by company_id asc,row_num asc

Or if you want the desired result direct from the query try:

select concat('Company: ',company_id,' , User: ',user_id, ', position: ',row_num ) as my_row 
from 
    ( select *,row_number() over(partition by company_id order by id asc) as row_num
      from user_company
     ) as tbl 
order by company_id asc,row_num asc

Which will give:

        my_row
Company: 1 , User: 61, position: 1
Company: 1 , User: 71, position: 2
Company: 1 , User: 81, position: 3
Company: 2 , User: 91, position: 1
Company: 2 , User: 10, position: 2
Company: 2 , User: 11, position: 3

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5a2151db9dc2099f0ae0cdb96c3b7125

Canonical: [ROW_NUMBER() in MySQL](https://stackoverflow.com/questions/1895110/row-number-in-mysql) — mickmackusa, Aug 25 '22 at 14:07

jmvcollaborator · Answer 2 · 2022-08-25T13:54:14.703

Side note-MySQL introduced the ROW_NUMBER() function since version 8.0.

Another approach, the idea is to leverage the ROW_NUMBER() which assigns a sequential number to each row in the result set. The first number begins with one.

So in the inner query it assigns a rowno to each record within the query ROW_NUMBER() OVER(PARTITION by company_id order by company_id) as rowno

Lastly the group by allows to group companies and users to get a sequential iteration for each group

SELECT company_id,user_id,rowno as position
FROM
( 
SELECT company_id,user_id,
ROW_NUMBER() OVER(PARTITION by company_id order by company_id) as rowno
FROM playground.Positions 
) as tbl
group by company_id,user_id;

Fiddle

php sequence numbers by id group

2 Answers2