Appending query to MySQL fulltext query

Question

I have a fulltext query like this and it gets results i expect

SELECT  id,'', 'blogs' as mytable,title, content, 
    MATCH(title) AGAINST('keywords*' IN BOOLEAN MODE) * 8 +
    MATCH(content) AGAINST('keywords*' IN BOOLEAN MODE) * 4
        as score FROM blogs WHERE archived = 'N' AND MATCH(title, content) 
            AGAINST('keywords*' IN BOOLEAN MODE)
             ORDER BY score DESC, id DESC

If i search for a specific keyword I get a result I want with an ID of 4. The problem is I need an additional column so i can search that ID to get the rows position of that ID.

If i run this query on it's own i get the correct result which is 8

SELECT position FROM
(
   SELECT
   id, @rownum:=@rownum+1 position, archived
   FROM blogs, (SELECT @rownum:=0) r WHERE archived ='N'
   ORDER BY id DESC
)
AS position
WHERE id = 4

What I want to do is combine these queries into one query so the ID of the first query is passed into the WHERE of the second query so my additional column called 'position' would have a value of 8

I have tried to combine the queries but it's definitely not right

Answer 1

Something like this?

SELECT position FROM
(
   SELECT
   id, @rownum:=@rownum+1 position, archived
   FROM blogs, (SELECT @rownum:=0) r WHERE archived ='N'
   ORDER BY id DESC
)
AS position
WHERE id IN (SELECT id FROM(SELECT  id,'', 'blogs' as mytable,title, content, 
    MATCH(title) AGAINST('keywords*' IN BOOLEAN MODE) * 8 +
    MATCH(content) AGAINST('keywords*' IN BOOLEAN MODE) * 4
        as score FROM blogs WHERE archived = 'N' AND MATCH(title, content) 
            AGAINST('keywords*' IN BOOLEAN MODE)
             ORDER BY score DESC, id DESC)x)