Gossamer Forum
Quote Reply
Next / previous category
I just write a global to show previous(or last) and next(or first) category at the category page.
Because this is something essential i just ask myself if there is a function inside lsql to do this?

If not i will ask:

select 1 from with FatherID, order by asc, > than category.id
A B (C) > D* E
if not there select the first one.
A B C D (E) -> A*

select 1 from with FatherID, order by desc, > than category.id
E D (C) > B* A
if not there select the last one.
E D C B (A) -> E*

means that i need two or three selects, is there a better way?
Quote Reply
Re: [Robert] Next / previous category In reply to
Now i found out that the last time i have done something like this i have select * and throw everything in an array. Then i took one before and one after my ID.

Now i found:

SELECT row FROM

(SELECT @rownum:=@rownum+1 row, a.*
FROM bCategory a, (SELECT @rownum:=0) r

ORDER BY sort,Pfad,Name) as something
WHERE ID = 3300

But where i put this:
WHERE FatherID = 33 AND Number_of_Links > 0


Then in a second step i can choose:

SELECT * FROM bCategory ORDER BY sort,Pfad,Name LIMIT result from_above, 3

to have previous, ID, next.

But as mentioned, i need a more complex first query, becaue now we select from all cats.
Quote Reply
Re: [Robert] Next / previous category In reply to
Now i found:

SELECT (@row_number:=@row_number + 1) AS num, ID, Name
FROM bCategory, (SELECT @row_number:=0) AS t
WHERE FatherID = 33 and Number_of_Links > 0
ORDER BY sort, Pfad, Name

Gives back all subcats from cat 33 with NoL > 0 like

1 ID Name
2 ID Name

...

But now i would like to know which row_number is my ID 3300 to select from Limit (row_number,1)
Quote Reply
Re: [Robert] Next / previous category In reply to
Got it:

SELECT row FROM
(SELECT @row_number:=@row_number + 1 AS row, ID, Name
FROM bCategory as a, (SELECT @row_number:=0) AS b
WHERE FatherID = 33 and Number_of_Links > 0
ORDER BY sort, Pfad, Name) as c
WHERE ID = 3300;

$result= The position of my cat in that list.

The next cat
SELECT * FROM bCategory
WHERE FatherID = 33 AND Number_of_Links > 0
ORDER BY sort,Pfad,Name LIMIT $result,1

The previous cat
SELECT * FROM bCategory
WHERE FatherID = 33 AND Number_of_Links > 0
ORDER BY sort,Pfad,Name LIMIT $result-2,1
Quote Reply
Re: [Robert] Next / previous category In reply to
But how to tell lsql to do this query?


SELECT row FROM
(SELECT @row_number:=@row_number + 1 AS row, ID, Name
FROM bCategory as a, (SELECT @row_number:=0) AS b
WHERE FatherID = 33 and Number_of_Links > 0
ORDER BY sort, Pfad, Name) as c
WHERE ID = 3300;
Quote Reply
Re: [Robert] Next / previous category In reply to
Sorry, been away camping for a week :)

Try:

Code:
my $sth = $DB->table("Category")->do_query(qq|SELECT row FROM
(SELECT @row_number:=@row_number + 1 AS row, ID, Name
FROM bCategory as a, (SELECT @row_number:=0) AS b
WHERE FatherID = 33 and Number_of_Links > 0
ORDER BY sort, Pfad, Name) as c
WHERE ID = 3300|) || die $GT::SQL::error;
while (my $hit = $sth->fetchrow_hashref) {
# values in here
}

Obviously you could also pass in the value of the ID, something like:

Code:
WHERE ID = $_[0]

..and similar with the FatherID part.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Next / previous category In reply to
You are the best. :)

I have solved it till now with a list.
At every cat-page i have the list with all sister-cats anyway, the used one is shown bold and i save the one before and the one after and show them at the bottom to go previous and next.

But to have the sql direct without a list is much more better. :)
Thank you.