Mysql: Get rows with unique column per page

for begin execuse my English, but I need help with one sql:

I have table with offers for example: ID, id_user, offer, timestamp

And I need get a rows with ordering, which will be one id_user on page (for example in every 10 rows will be only one unique id_user, and second order with timestamp)

---

Example:

1, Pepa, Auto, 1.1.2011
2, Pepa, Motorka, 1.1.2011
3, Karel, Traktor, 2.1.2011
4, Lukas, Jeep, 2.1.2011
5, Pepa, Autokara, 3.1.2011
6, Jindra, Traktor, 5.1.2011

=> ORDERING开发者_运维问答 (2 rows on page)

**1. Page**
1, Pepa, Auto, 1.1.2011
3, Karel, Traktor, 2.1.2011


**2. Page**
2, Pepa, Motorka, 1.1.2011
4, Lukas, Jeep, 2.1.2011


**3. Page**
5, Pepa, Autokara, 3.1.2011
6, Jindra, Traktor, 5.1.2011

In simple "unique user's offers on one page"

Thanks for your help!

---

Have you tried "group by"?

delimiter //

connect pepa

drop table if exists offers;
create table offers (
id SERIAL,
id_user VARCHAR(20) NOT NULL,
offer VARCHAR(20) NOT NULL,
timestamp TIMESTAMP DEFAULT NOW()
);

insert into offers
(id_user,offer)
values
('Pepa', 'Auto'),
('Pepa', 'Motorka'),
('Karel', 'Traktor'),
('Lukas', 'Jeep'),
('Pepa', 'Autokara'),
('Jindra', 'Traktor');

select * from offers group by id_user order by timestamp;

//

This yields:

id  id_user offer   timestamp
4   Lukas   Jeep    2011-06-05 21:14:10
6   Jindra  Traktor 2011-06-05 21:14:10
1   Pepa    Auto    2011-06-05 21:14:10
3   Karel   Traktor 2011-06-05 21:14:10

Note that none of the id_users are repeated. If you include a conditional statement ("where"), you can probably create a unique page based on the id_user of the person logged in.

Hope this helps. :)

Cheers!

(Be aware that sorting by the timestamp may be a bit weird here. And don't forget that you can always post process with a language of your choice [PHP, etc.].)

---

This PHP code will produce the same output you have listed in your question. It may not be the most efficient thing in the world, but it gets the job done.

You may be able to write a funky MySQL query to do this, but I don't know how well it would scale over thousands of records, etc. And you're producing pages at that. :)

<?php

// Collect stuff from the database
$dbc=mysqli_connect('127.0.0.1','user','passwd','pepa') or 
 die('Could not connect!');
$getOffers='select * from offers';
$rs=mysqli_query($dbc,$getOffers);
while($thisRow=mysqli_fetch_assoc($rs))
 $offers[]=$thisRow;
mysqli_close($dbc);

// Create the pages
// (this is probably a bit over the top, but you get the idea)
foreach($offers as $oI => $thisOffer)
 $offers[$oI]['used']=false;  // <-- tell us if we've used the record or not
$thisUser='Pepa';  // <-- the user who should appear at the top of each page
$numRecsPerPage=2; // <-- the number of records per page
$cPg=-1; foreach($offers as $oI => $thisOffer) {
    if($thisOffer['id_user']==$thisUser) {
        $cPg++;
        $offers[$oI]['used']=true;
        $page[$cPg][]=$thisOffer;
        $recsUsed=1; foreach($offers as $pI => $procOffer) {
            if(!$offers[$pI]['used'] && $offers[$pI]['id_user']!=$thisUser) {
                $offers[$pI]['used']=true;
                $page[$cPg][]=$procOffer;
                $recsUsed++;
            }
            if ($recsUsed>=$numRecsPerPage) break;
        }
    }
}  

// Print the pages
foreach($page as $thisPage) { 
    foreach($thisPage as $thisRow)
     echo   $thisRow['id']."\t".$thisRow['id_user']."\t".
            $thisRow['offer']."\t".$thisRow['timestamp']."\n";
    echo "\n";
}

?>

Output:

1   Pepa    Auto    2011-06-05 21:14:10
3   Karel   Traktor 2011-06-05 21:14:10

2   Pepa    Motorka 2011-06-05 21:14:10
4   Lukas   Jeep    2011-06-05 21:14:10

5   Pepa    Autokara    2011-06-05 21:14:10
6   Jindra  Traktor 2011-06-05 21:14:10

Sorry for adding another answer - I would otherwise add a comment, but I think the code is more helpful here.