CONCAT count same title - x (with subquery)

[the-robman]

Hi bednee, I tried to make an counter for tt_news titles with the same name, in the "lookInDb" part of the cooluriconf.xml, like this way:

SELECT
    CONCAT(tt1.title, '-', tt2.uid, IF(tt2.number > 1, CONCAT('-', tt2.number), ''))

FROM
    tt_news as tt1,
    (
        SELECT
            uid,
            (@row_number:=@row_number +1) AS number

        FROM
            tt_news,
            (SELECT @row_number:=0) AS t

        WHERE
            title=(SELECT title FROM tt_news WHERE uid=$1)

    ) AS tt2

WHERE tt1.uid=$1;

<part>
    <parameter>tx_ttnews[tt_news]</parameter>
    <lookindb>
        <to>SELECT CONCAT(tt1.title, '-', tt2.uid, IF(tt2.number > 1, CONCAT('-', tt2.number), '')) FROM tt_news as tt1, (SELECT uid, (@row_number:=@row_number +1) AS number FROM tt_news, (SELECT @row_number:=0) AS t WHERE title=(SELECT title FROM tt_news WHERE uid=$1)) AS tt2 WHERE tt1.uid=$1</to>
        <t3conv>1</t3conv>
    </lookindb>
</part>

If I use this query in phpmyadmin, I get a list which I expected. Your SQL example, from the Extension Manual don't work too:

SELECT CONCAT(tt1.title,IF(tt2.number>1,CONCAT('-',tt2.number),''))
FROM tt_news as tt1,
(SELECT COUNT(*) AS number FROM tt_news WHERE title=(SELECT title FROM tt_news WHERE uid=$1)) AS tt2
WHERE tt1.uid=$1

Please help :) What I doing wrong? Greetings, Rob

[bednee]

Hi, so use the first query if it works. The second doesn't look right as it will give you the same count for all news with the same title. Is it in the manual? If so, it's wrong.

[the-robman]

Hello again and thanks for your fast reply. I write the first query yesterday and tested on my local server. The result (in phpmyadmin) looks fine: /same-newstitle, /same-newstitle-2, /same-newstitle-3, ...

But when I use the query in the cooluri xml file, I get only the "uid" from the news, no newstitle and no count. Example: /12, /152, /44, ... (the numbers are the tt_news uid's)

I think the query is parsed wrong. Maybe is the @ in the query a Problem?