CODEWITHSUNDEEP
phpmysql
Kakotas7
Kakotas7

Reputation: 65

Mysql select all but first and last row

I want the database to show all the rows, except for the first and last ones, since I have CSS code for them.

I tried this:

SELECT * FROM db 
WHERE 
keywords LIKE '%example%' LIMIT 9999 OFFSET 1
 AND 
keywords LIKE '%example%' DESC LIMIT 9999 OFFSET 1

Since the row number may increase I can't write an exact number.

Upvotes: 3

Views: 2789

Answers (5)

B. Desai
B. Desai

Reputation: 16436

You can do it without using LIMIT AND OFFSET

SELECT * FROM table_name WHERE id != (SELECT MAX(id) FROM table_name) AND id != (SELECT MIN(id) FROM table_name)

SELECT * FROM db 
WHERE 
keywords LIKE '%example%'
AND
id != (SELECT MAX(id) FROM db) 
AND 
id != (SELECT MIN(id) FROM db)

here id will be your auto increment key

Upvotes: 0

Cyril Graze
Cyril Graze

Reputation: 3890

There really is no reason to complicate your query by trying to snip off these values at the SQL level, you could do:

$results = $db->query( /* regular query with all results */ );

array_pop($results);
array_shift($results);

// Now use $results, which now contains only the "middle" content

If you really want it at the DB level, you can use:

SELECT * FROM db 
WHERE keywords LIKE '%example%' 
AND id <> (SELECT MAX(ID) FROM TABLE)
AND id <> (SELECT MIN(ID) FROM TABLE)

Upvotes: 4

user10089632
user10089632

Reputation: 5550

You can try this for instance:

SELECT * FROM TABLE WHERE ID != (SELECT MAX(ID) FROM TABLE) LIMIT 1,9844674507370

But like I said in the comment : It is strongly advisable that you handle this with PHP code, to avoid making 2 or more requests

Upvotes: 0

JYoThI
JYoThI

Reputation: 12085

1st : Simple you can handle this thing in php like below . avoid two query 

 $last_record =count($records)-1;
 $i=0;
   foreach($records as $key=>$row)
   {
      if($i==0){
           //apply the css
       }

      if($i== $last_record ){
           //apply the css
       }
   }

query :

SELECT * FROM db WHERE keywords LIKE '%example%'

Upvotes: -1

vjy tiwari
vjy tiwari

Reputation: 861

You can use UNION like as

SELECT * FROM db WHERE keywords LIKE '%example%' order by keywords ASC limit 1 UNION SELECT * FROM db WHERE keywords LIKE '%example%' order by keywords DESC limit 1;

Upvotes: 0

Related Questions