Site:Developer stuff/Changing next and previous links
From Feast upon the Word (http://feastupontheword.org). Copyright, Feast upon the Word.
Note: this code is really ugly but it seemed easier to do this than working on something nicer.
Objective: Change "Next" and "Previous" links to show the user where they will be taken when they click them.
If you compare the earlier version of the code to get the middle content to this one you see that the only difference is that in the previous version the middle content had an instr search parameter that included a carriage return/line feed. Somehow MySQL was treating this differently depending on whether a select statement was done or an update/create statement. It wasn't really necessary to include the line feed anyway in the instr search. Once I took that out, things worked fine.
Final update statement used.[edit]
UPDATE feastupontheword.cur b INNER JOIN test.commentary_pages_ordered a ON a.cur_id=b.cur_id SET b.cur_text = concat('{| | [[' , SUBSTRING( cur_text , 21, instr( cur_text , '|Previous]]' ) - 21) , '|Previous (' , SUBSTRING( cur_text , 21, instr( cur_text , '|Previous]]' ) - 21) , ')]] || || [[' , SUBSTRING( cur_text, char_length(cur_text)-65+instr( right(cur_text, 70), '|| [['), 56 - instr( right(cur_text, 70), '|| [[')) , '|Next (' , SUBSTRING( cur_text, char_length(cur_text)-65+instr( right(cur_text, 70), '|| [['), 56 - instr( right(cur_text, 70), '|| [[')) , ')]] |}' , SUBSTRING(cur_text , instr(cur_text , '|}' )+2 , char_length(cur_text )-123 + instr(right(cur_text , 120 ), '{| width="20%"' )-instr(cur_text , '|}' )) , ' {| | [[' , SUBSTRING( cur_text , 21, instr( cur_text , '|Previous]]' ) - 21) , '|Previous (' , SUBSTRING( cur_text , 21, instr( cur_text , '|Previous]]' ) - 21) , ')]] || || [[' , SUBSTRING( cur_text, char_length(cur_text)-65+instr( right(cur_text, 70), '|| [['), 56 - instr( right(cur_text, 70), '|| [[')) , '|Next (' , SUBSTRING( cur_text, char_length(cur_text)-65+instr( right(cur_text, 70), '|| [['), 56 - instr( right(cur_text, 70), '|| [[')) , ')]] |}'), cur_timestamp=20061002065514, cur_touched=20061002065514, inverse_timestamp=79938997934485
Building blocks[edit]
Name of previous page
SUBSTRING( cur_text , 21, instr( cur_text , '|Previous]]' ) - 21)
Name of next page
SUBSTRING( cur_text, char_length(cur_text)-65+instr( right(cur_text, 70), '|| [['), 56 - instr( right(cur_text, 70), '|| [['))
Middle content
SUBSTRING(cur_text , instr(cur_text , '|}' )+2 , char_length(cur_text )-153 +instr(right(cur_text , 150 ), '{| width="20%"' )-instr(cur_text , '|}' ))