Site:Developer stuff/Changing next and previous links

From Feast upon the Word (http://feastupontheword.org). Copyright, Feast upon the Word.
Jump to: navigation, search

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 , '|}' ))