CODEWITHSUNDEEP
phpmysql
Nick James
Nick James

Reputation: 163

How Can I Skip every other row in mysql?

i have a table in mysql and i am fetching the results from this table. but instead of fetching all the rows in this table i only want to fetch every other row. so first get row one then skip the second row, get row 3 and skip row 4 etc.

Is there a way of doing this and if so can someone please show me how.

I've tried this:

function:

function blocked_users_list() {
            global $connection;
            global $_SESSION;
            global $profile_id;
            $query = "SELECT
  baseview.* 
  @odd:=1-@odd AS even
FROM 
  (
   SELECT *
    FROM ptb_block_user
    WHERE
      WHERE ptb_block_user.blocked = '1'
      AND ptb_block_user.blocked_id = ".$_SESSION['user_id']."
  ) AS baseview,
  (
    SELECT @odd:=0
  ) AS filter
WHERE
  even=1                
                        ";
            $blocked_users_list = mysql_query($query, $connection);
            confirm_query($query, $connection);
            return $blocked_users_list;
        }

php:

<?php
        $blocked_users_list = blocked_users_list();
        while ($block = mysql_fetch_array($blocked_users_list)) { 



?>

but it gives this error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /Applications/XAMPP/xamppfiles/htdocs/ptb1/blocked_users.php on line 44

Upvotes: 2

Views: 1866

Answers (4)

Emery King
Emery King

Reputation: 3534

You can use modulus in mysql (one query)

select * from `table` where `id` % 2 = 1

Retrieves all odd IDs.

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65264

Assuming you have a query like

SELECT 
  col1 AS colA,
  col2 AS colB
FROM
  sometable
WHERE
  something=17

that fetches all rows as a baseline. You can then filter every second row by using

SELECT
  baseview.* 
  @odd:=1-@odd AS even
FROM 
  (
    SELECT 
      col1 AS colA,
      col2 AS colB
    FROM
      sometable
    WHERE
      something=17
  ) AS baseview,
  (
    SELECT @odd:=0
  ) AS filter
WHERE
  even=1

Upvotes: 0

Gareth Cornish
Gareth Cornish

Reputation: 4356

Use a variable to record the index, then check with mod 2 ($ind % 2) - this will return either 0 or 1.

<?php
    $users_list = users_list();
    $ind = 0;
    while ($user = mysql_fetch_array($users_list)) { 
        if(($ind++)%2) echo "something"; 
    } 
?>

Upvotes: -1

user557846
user557846

Reputation:

do it in the query:

SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, [column name] 
    FROM ( 
        SELECT @row :=0) r, [table name] 
    ) ranked 
WHERE rownum % [n] = 1

Upvotes: 2

Related Questions