PHP select second row

[henk3]

Simply cant make this work. How do I select a specific row in a table and then get the info of the record just after it. I do not know the study_code for the second row but I have the study_code for the row right before it.

Im trying to use LIMIT and DESC

…row
…row
…row
… I can select this row with study_code
… But I want the study_code of THIS row
…row
…row

   $sql = "SELECT * FROM studies WHERE study_code = ? ORDER BY LENGTH(study_code) 
    DESC, study_code DESC LIMIT 1,2";

[r937]

okay, i’m going to criticize this, please don’t take it personally

when you say WHERE study_code = ? presumably you’re passing in a string value

so all the rows returned will have this value for the study_code

so ORDER BY LENGTH(study_code) DESC, study_code DESC will have no effect, since all the lengths will be the same, and all the values will be the same

what am i missing?

[henk3]

Yes sorry

Study_code = ? will have a unique value assigned and it will only return one record.
I want to retrieve the next records study_code.

Right now im trying the following but get the below error

 select study_code, next_word
 from (
     select id, 
        study_code,
       lead(study_code) over (order by id) as next_word
  from studies
  )
  where study_code = 'Study_DRW3S1';

error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(order by id) as next_word
from studies
)
where study_code = ‘Study_DRW3S’ at line 5

[r937]

it’s barfing on (ORDER BY ID) and i’m afraid i’m not familiar enough with MySQL window functions to recognize how this violates the syntax

what about moving the WHERE condition into the subquery? which, by the way, will need a table alias

[m_hutley]

I have a feeling the entire thing is a case of “Let the receiving language handle it”.

Unless your ID’s are exceedingly large, it would be more compact to simply return id and study_code and let the other end of the request handle the ‘what comes next’.

[SpacePhoenix]

What version of MySQL is being used? From a quick Google it looks like MySQL didn’t get support for “Window Functions” until version 8