Hi everybody,
I'm currently moving AkoComment to 451. The component is ready, the content Mambot is ready, but the new Searchbot does not work fine. Here's the sql statement:
PHP Code:
$database->setQuery( "SELECT CONCAT(c.title,' - ',a.title) AS title,"
. "\n a.comment AS text,"
. "\n a.date AS created,"
. "\n 'Comment' AS section,"
. "\n CONCAT('index.php?option=com_content&task=view&id=',a.contentid,'&Itemid=',m.id,'#akocomment',a.id) AS href,"
. "\n '2' AS browsernav"
. "\n FROM #__akocomment AS a"
. "\n LEFT JOIN #__menu AS m ON m.componentid = a.contentid"
. "\n LEFT JOIN #__content AS c ON c.id = a.contentid"
. "\n WHERE a.comment LIKE '%$text%'"
. "\n AND a.published='1'"
. "\n ORDER BY a.date"
);
The problem is located somewhere in the JOIN tags (I'm no sql ace, so please forgive me even larger errors).
All comments in AkoComment 2.0 will have a target for direct linking from within the Searchbot. Therefore I use the CONCAT command in the link. The link is checked by Mambo when it will be returned by the bot. If Mambo detects a missing Itemid, it adds the correct Itemid to the end. But this does not work for me, as I need the target at the end of the link. Therefore I have to find out the correct Itemid myself (which I try with the first JOIN).
Sadly this causes troubles and does not return results for all found items.
The second JOIN grabs the content item title. This is needed because a comment in AkoComment could have no own title. But as the title is used as the links description I have to be sure and therefore add the content title in the front.
Any ideas? Please help fast so I could release the new version fast...
Help needed with mysql JOIN statement
Threaded Mode
