How to fetch 2 rows at a time from MySql database

Question

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.

Answer 1

No need to modify the way you fetch data. Here follows a plaintext, short and simple solution, resolved using frontend code only:

$rownum = 0;
while($row = $result->fetch()) {
    $rownum++;
    echo $row['username'] . ($rownum %2 == 0) ? "\n" : '     ');
}

Answer 2

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 );

Answer 3

The eassiest way would be load all your users in an array in PHP and use the following pseudo code withing PHP:

Assume your array of users has an index from 0 to the ammount of users. The value you should reference in each cell (x = 0-rows , y = 0-cols) CellValue ( x+1+y)

Good luck!

Answer 4

You don't need to fetch two rows in one time. You need to correctly create your view script.

$counter=0;
echo '<table><tr>';
while($row = $result->fetch()) {
    $counter++;
    echo '<td>'.$row['user'].'</td>';
    if ($counter%2 == 0) {
        echo '</tr><tr>';
    }
}

if ($counter%2 == 1) {
    echo '<td>&nbsp;</td>'
}

echo '</tr></table>'