· 7 years ago · Sep 05, 2018, 10:58 PM
1Mysql: Get rows with unique column per page
21, Pepa, Auto, 1.1.2011
32, Pepa, Motorka, 1.1.2011
43, Karel, Traktor, 2.1.2011
54, Lukas, Jeep, 2.1.2011
65, Pepa, Autokara, 3.1.2011
76, Jindra, Traktor, 5.1.2011
8
9**1. Page**
101, Pepa, Auto, 1.1.2011
113, Karel, Traktor, 2.1.2011
12
13
14**2. Page**
152, Pepa, Motorka, 1.1.2011
164, Lukas, Jeep, 2.1.2011
17
18
19**3. Page**
205, Pepa, Autokara, 3.1.2011
216, Jindra, Traktor, 5.1.2011
22
23delimiter //
24
25connect pepa
26
27drop table if exists offers;
28create table offers (
29id SERIAL,
30id_user VARCHAR(20) NOT NULL,
31offer VARCHAR(20) NOT NULL,
32timestamp TIMESTAMP DEFAULT NOW()
33);
34
35insert into offers
36(id_user,offer)
37values
38('Pepa', 'Auto'),
39('Pepa', 'Motorka'),
40('Karel', 'Traktor'),
41('Lukas', 'Jeep'),
42('Pepa', 'Autokara'),
43('Jindra', 'Traktor');
44
45select * from offers group by id_user order by timestamp;
46
47//
48
49id id_user offer timestamp
504 Lukas Jeep 2011-06-05 21:14:10
516 Jindra Traktor 2011-06-05 21:14:10
521 Pepa Auto 2011-06-05 21:14:10
533 Karel Traktor 2011-06-05 21:14:10
54
55<?php
56
57// Collect stuff from the database
58$dbc=mysqli_connect('127.0.0.1','user','passwd','pepa') or
59 die('Could not connect!');
60$getOffers='select * from offers';
61$rs=mysqli_query($dbc,$getOffers);
62while($thisRow=mysqli_fetch_assoc($rs))
63 $offers[]=$thisRow;
64mysqli_close($dbc);
65
66// Create the pages
67// (this is probably a bit over the top, but you get the idea)
68foreach($offers as $oI => $thisOffer)
69 $offers[$oI]['used']=false; // <-- tell us if we've used the record or not
70$thisUser='Pepa'; // <-- the user who should appear at the top of each page
71$numRecsPerPage=2; // <-- the number of records per page
72$cPg=-1; foreach($offers as $oI => $thisOffer) {
73 if($thisOffer['id_user']==$thisUser) {
74 $cPg++;
75 $offers[$oI]['used']=true;
76 $page[$cPg][]=$thisOffer;
77 $recsUsed=1; foreach($offers as $pI => $procOffer) {
78 if(!$offers[$pI]['used'] && $offers[$pI]['id_user']!=$thisUser) {
79 $offers[$pI]['used']=true;
80 $page[$cPg][]=$procOffer;
81 $recsUsed++;
82 }
83 if ($recsUsed>=$numRecsPerPage) break;
84 }
85 }
86}
87
88// Print the pages
89foreach($page as $thisPage) {
90 foreach($thisPage as $thisRow)
91 echo $thisRow['id']."t".$thisRow['id_user']."t".
92 $thisRow['offer']."t".$thisRow['timestamp']."n";
93 echo "n";
94}
95
96?>
97
981 Pepa Auto 2011-06-05 21:14:10
993 Karel Traktor 2011-06-05 21:14:10
100
1012 Pepa Motorka 2011-06-05 21:14:10
1024 Lukas Jeep 2011-06-05 21:14:10
103
1045 Pepa Autokara 2011-06-05 21:14:10
1056 Jindra Traktor 2011-06-05 21:14:10