Help needed with mysql JOIN statement

[Admin]

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...

[Izydor]

Well, it's hard to say what is wrong but maybe try this :

$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, #__menu AS m, #__content AS c"
. "\n WHERE a.comment LIKE '%$text%'"
. "\n AND a.published='1'"
. "\n AND a.contentid = m.componentid"
. "\n AND a.contentid = c.id"
. "\n ORDER BY a.date"
);

Do U receive any kind of errors or is it only missing rows in query results ?

Izydor

[m2k]

Perhaps an sql statement something like...

PHP Code:

 $database->setQuery( "SELECT CONCAT(c.title,' - ',a.title) AS title,"
."\n `comment` AS text,"
."\n `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 mos_akocomment as a"
."\n LEFT JOIN mos_content as c ON c.id = a.contentid"
."\n LEFT JOIN mos_menu as m ON m.componentid = c.sectionid"
."\n where m.`type` like '%content%' AND a.published='1'"
."\n order by a.`date`"
); 


Pay attention to the backticks too. They would not be needed if some of the fieldnames were not reserved words for mysql.

OFFTOPIC: but thanks, I just realized something I need to check for in 404 SEF.... named anchors. Otherwise this will potentially create a lot of unneeded entries in the DB.

[Admin]

Hi,

first of all thanks for your answers. Sadly both didn't work. The first solution does not show all search results. I guess this is because of the missing JOIN commands. It grabs only the rows, which deliver results for ALL expressions in the WHERE command, instead of showing all search results and add the needed extra informations from the other tables.

The second sql statement is complete nonsense. The where statement does not even contain the comparison of comments with search results anymore. And besides this you missed the table prefixes for the 'a' table.

Don't we have an sql expert in this community? I guess an expert would just laugh about my small problem and solve it in a minute.

[walb]

Syntax is OK.
The first join will rarely resolve, since a menu componentid mostly points to a sectionid or a categoryid.
So you have to query the section and category tables as well to find the link with menu items, which may result in multiple menu rows as different menu's may point to the same sections/categories.

On the other hand content row holds catid and sectionid, it may avoid that extra query.
What componentid holds could be determined by the item type.

[Treepwood]

Hi all,

the SEF 404 is working great, but unfortunately since I updated to the Beta7 the generated URLs to the individual threads ate not working anymore. I am getting the following error message:

FILE NOT FOUND: /forum/kurs-16---ss-2005/view/15/
404: Not found
Sorry, but that content could not be found

Here's one of my forums: http://www.china-community.de/forum/...05/showcat.php

And that's one thread within it: http://www.china-community.de/forum/...-2005/view/17/

Why isn't it working?

Please help me...

[JxT]

This thread is a bit old, but also haven't seen any progress on a searchbot for AkoComment 2.0.

Since this is the case, and since I really wanted to be able to include AkoComments in searches on my new Joomla 1.0.2 web site, I worked up this small hack.

Feel free to use the following code, or disregard if you have already solved the problem, or if it is already incorporated in other mambots. Code is available as is, no guarantees of suitability, stability, etc.

/*
* +Insert the following patch code into this Joomla php script to enable
* +searches to include AkoComments. It is best to write your own bot
* +to prevent updates overwriting, but at least you can test it quickly.
* +Hack written by JxT 10/13/2005

* Joomla php Script:
* @version $Id: content.searchbot.php 104 2005-09-16 10:29:04Z eddieajau $
* Location: ".\mambots\search\content.searchbot.php"
*/

// Patch code
// +insert begin
// search AkoComments
$query = "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= ',c.id) AS href,"
. "\n '2' AS browsernav"
. "\n FROM #__akocomment AS a"
. "\n LEFT JOIN #__content AS c ON c.id = a.contentid"
. "\n WHERE LOWER(a.comment) LIKE '%$text%'"
. "\n OR LOWER(a.title) LIKE '%$text%'"
. "\n AND a.published='1'"
. "\n ORDER BY a.date"
;
$database->setQuery( $query );
$list4 = $database->loadObjectList();
// +insert end

// Also, the last line of the script will need to be changed to
// include the $list4 array that you just added:
return array_merge( $list, $list2, $list3 , $list4 );

That's it - you should now be able to search for words in your comments titles or text.

Thanks for AkoComment, Arthur; it really helps out the blogging feature in Joomla.

Cheers,
JxT