I have a table user
user(id, username, email, profile_pic) // Id is primary key
Now I want to fetch result, two rows at a time on each iterations in PHP code because i need to display two rows from database. I want to display my result as given in below way.
Example
user1 user2
user3 user4
user5 user6
Any help will be appreciated.
Thanks.
分享 The idea is to join the user table to
itself and to do that according to your
rule. So here is the code. Try it out.
--- DATA ---
create table user(id int, username varchar(100), email varchar(100), profile_pic varchar(100));
insert into user
(id , username , email , profile_pic )
values
(10, 'user1', 'x1@x.com', '111');
insert into user
(id , username , email , profile_pic )
values
(20, 'user2', 'x2@x.com', '222');
insert into user
(id , username , email , profile_pic )
values
(30, 'user3', 'x3@x.com', '333');
insert into user
(id , username , email , profile_pic )
values
(40, 'user4', 'x4@x.com', '444');
insert into user
(id , username , email , profile_pic )
values
(50, 'user5', 'x5@x.com', '555');
insert into user
(id , username , email , profile_pic )
values
(60, 'user6', 'x6@x.com', '666');
insert into user
(id , username , email , profile_pic )
values
(70, 'user7', 'x7@x.com', '777');
--- QUERY 1 ---
SELECT v1.ID as id1, v1.email as email1, v2.ID as id2, v2.email as email2
FROM
(
SELECT
@i:=@i+1 AS num,
u.*
FROM
(SELECT u1.* FROM USER u1 ORDER BY u1.id ASC) u,
(SELECT @i:=0) AS tbl
ORDER BY num ASC
) v1
JOIN
(
SELECT
@j:=@j+1 AS num,
u.*
FROM
(SELECT u1.* FROM USER u1 ORDER BY u1.id ASC) u,
(SELECT @j:=0) AS tbl
ORDER BY num ASC
) v2 ON ( v1.num = v2.num - 1 AND mod(v1.num, 2) = 1);
If you want to also get the last row (the one with ID = 7) do this.
--- QUERY 2 ---
SELECT v1.ID as id1, v1.email as email1, v2.ID as id2, v2.email as email2
FROM
(SELECT v0.* FROM
(
SELECT
@i:=@i+1 AS num,
u.*
FROM
(SELECT u1.* FROM USER u1 ORDER BY u1.id ASC) u,
(SELECT @i:=0) AS tbl
ORDER BY num ASC
) v0 WHERE MOD(v0.num, 2) = 1
) v1
LEFT JOIN
(
SELECT
@j:=@j+1 AS num,
u.*
FROM
(SELECT u1.* FROM USER u1 ORDER BY u1.id ASC) u,
(SELECT @j:=0) AS tbl
ORDER BY num ASC
) v2 ON ( v1.num = v2.num - 1 );
