is that possible to count how many rows between two rows mysql?

Question

I have a mysql table like below. I want to count how many rows between berry and strawberry, in my case is 2 rows between these two fruits. How can I achieve that?

id   fruit
1     apple
2     berry
3     banana
4     pineapple
5     strawberry

Answer 1

Try this query,

  $sql = "SELECT COUNT(*) as total_count FROM table_name JOIN ( SELECT MAX(id) as 
  maxid,MIN(id) as minid FROM `table_name` WHERE fruit = 'berry' OR 
  fruit= 'strawberry') as temp ON table_name.id > temp.minid AND 
  table_name.id<temp.maxid";