Hi!

I'm trying to make a searchbot to integrate a single MySql table on my Joomla searchs.
I've tried editing existing searchbots because there's not good documentation about this.

Can you tell me if it is possible to do?

Thanks in advance!!

Let me see if I understand.

You have a Joomla based website and in addition to that, you have a table in your Joomla database that you want to search in addition to all of the other searches.

If this is the case, then you'll need to create a new SearchBot for the task. Which I assume you already know. But you have no idea how to get started...

If this is just one table, it wouldn't be hard to do. However, most of my SearchBots are far too complex for you to use as an example and the documentation for these things is awful but there is one tutorial on the Joomla site that can help but it is a little outdated...
Joomla Developer Manual - onSearch Mambot Tutorial

What they don't include there is that you also need an XML configuration file and that there are a number of ways to modify the SearchBot.

If this half of a tutorial isn't enough for you to write your own, I'll see if I can help!

If you already know what MySQL query is needed to get the results you need, you can post that here and I can look into writing a quick SearchBot for you.

If you don't know the query needed, then it would be very difficult for me to help you without access to your database.

If this isn't anything close to what you are looking for, please provide more information and I'll try to help.

vujsa

Hi again vujsa!

Well, I've have been there before in that link that you passed to me, and yes, I've also realised before that I must to provide an XML file to let Joomla recognize and import the extension. My bot now is installed and active from the backend without problems but simply doesn't search nothing, so, it seems that the main function is registered but the problem is with the query.

You aren't wrong in your affirmations: I need to use Joomla to search in simple data tables wich I update contantly. I want to develop a bot that made each register in that tables "searcheable" but i don't know if also I have to develop a component first to have a "view" model for my registers in Joomla.
As you may know at this point, my querys will by as simple as "select * from name_table where important_field LIKE '%$text%'". In this case $text represent the search of the user in the search field of Joomla.

Looking again at the developers manual of Joomla I found that it seems to be mandatory that my query has to return this fields, wich I'm not doing a this time. Embarrasing I never before read that with proper atention, huh?.

- title
- created
- section
- href
- browsernav (doesn't know very well, but seems to be set up to '2' value.

The problem seems to be that I doesn't have some of this fields. Some fields can be easily added at my tables but some others i don't have a clue. By example, I don't know what URL provide to the HREF. I have only fields on my MySQL table and don't know how to construct links without an specific component references. Did you know if this is the minimal information that I must to construct with my querys? Do I need to develop a component too to have a view model inside Joomla's framework with URLs to concatenate HREFs in my query?. It sounds logic, but looks not so easy for a newbie like me.

Anyway, I will try to play and understand a little bit more before I bother you more with my questions and my code, but in the meantime if you can put some light in my mental darkness, I will love it in advance. If I get stuck, i will try to send the code to clarify the situation and facilitate your invaluable help.

Kind regards and obviuosly, many thanks.
Martin

Hello Martin,

Well, here is a little information for you to consider...

The script should convert the search term $text into the proper MySQL query needed to find any information contained in the table or tables specified. The query returns specific information to the search function with the aliases you pointed out (title, created, section, href, browsernav).

It isn't absolutely required to have a value for each of this aliases but you should specify no value like so:
'' AS created

The expected result of a search is that an items information is displayed and then if the user wants to can click the link to view the resource. Having said that, you will need some method of displaying the contents of a database item. This could be a very simple script outside of Joomla that simply takes the item id from the link and reads the information from the database for that item into the display page. There are some security concerns when you write such a script because it no longer shares the built in security features of Joomla. But, writing a component for Joomla isn't an easy process either!

If you aren't looking for too much, then you can use the most basic component tutorial found here: http://help.joomla.org/content/view/773/125/

Additionally, there is a much more detailed tutorial found here:
Creating a quick and easy Mambo component

If you keep it as simple as possible, not only will you accomplish your goal, but you just might find that you can do a lot more.

Now, if you want a working example to look at that isn't filled with too many bells and whistles, then you can find go to the downloads section and get the first SearchBot I wrote. http://www.handyphp.com/component/option,com_docman/task,doc_details/gid,1/
This version has some bugs in it and the section at the bottom where it converts any Bulletin Board Code to text wouldn't apply to you but the script does perform the task as you need it.

Here is the original code:

Code:

<?php /********************************************************************************** * HAndy PHP Joomlaboard Searchbot * @version 1.0 * @package Joomlaboard * @copyright (c) 2006 Handy PHP * @license GNU GPL (http://www.gnu.org/licenses/gpl.txt) * @author http://www.handyphp.com **********************************************************************************/ // no direct access defined( '_VALID_MOS' ) or die( 'Restricted access' ); $_MAMBOTS->registerFunction( 'onSearch', 'botSearchJoomlaBoard' ); /** * Contacts Search method * * The sql must return the following fields that are used in a common display * routine: href, title, section, created, text, browsernav * @param string Target search string * @param string mathcing option, exact|any|all * @param string ordering option, newest|oldest|popular|alpha|category */ function botSearchJoomlaBoard( $text, $phrase='', $ordering='' ) { global $database, $my, $_MAMBOTS; // check if param query has previously been processed if ( !isset($_MAMBOTS->_search_mambot_params['joomlaboard']) ) { // load mambot params info $query = "SELECT params" . "\n FROM #__mambots" . "\n WHERE element = 'joomlaboard.searchbot'" . "\n AND folder = 'search'" ; $database->setQuery( $query ); $database->loadObject($mambot); // save query to class variable $_MAMBOTS->_search_mambot_params['joomlaboard'] = $mambot; } // pull query data from class variable $mambot = $_MAMBOTS->_search_mambot_params['joomlaboard']; $botParams = new mosParameters( $mambot->params ); $section_name = $botParams->def( 'search_result_section_name', 'Forum' ); $limit = $botParams->def( 'search_limit', 50 ); $allow_bbc = $botParams->def( 'allow_bbc', 1 ); $text = trim( $text ); if ($text == '') { return array(); } switch ( $ordering ) { case 'alpha': $order = 'b.subject ASC'; break; case 'category': $order = 'c.name ASC, b.subject ASC'; break; case 'popular': case 'newest': $order = 'b.time DESC'; break; case 'oldest': $order = 'b.time ASC'; break; default: $order = 'b.subject DESC'; break; } $query = "SELECT b.subject AS title," . "\n a.message AS text," . "\n FROM_UNIXTIME(b.time) AS created," . "\n CONCAT('$section_name','/', c.name ) AS section," . "\n '2' AS browsernav," . "\n CONCAT('index.php?option=com_joomlaboard&func=view&catid=',b.catid,'&id=',b.thread) AS href" . "\n FROM #__sb_messages_text AS a" . "\n INNER JOIN #__sb_messages AS b ON b.id = a.mesid" . "\n INNER JOIN #__sb_categories AS c ON c.id = b.catid" . "\n WHERE ( b.subject LIKE '%$text%'" . "\n OR a.message LIKE '%$text%'" . "\n OR b.name LIKE '%$text%'" . "\n OR c.name LIKE '%$text%'" . "\n OR c.description LIKE '%$text%' )" . "\n AND c.published = 1" . "\n AND c.pub_access <= $my->gid" . "\n ORDER BY $order" ; $database->setQuery( $query, 0, $limit ); $rows = $database->loadObjectList(); // This section of code removes BBC from the search results! if($allow_bbc == 0){ // - Do Nothing! $BBC = '[:«»space:]'; $replace = ' '; } else{ $BBC = array( // - Convert to PLAIN TEXT '@(\[url)([^\]]*?)(\])(.*?)(\[/url\])@si', '@(\[img=)([^\]]*?)(\])(.*?)(\[/url\])@si', '@(\[code[^\]]*?\])(.*?)(\[/code\])@si', '@(\[code:[0-9][^\]]*?\])(.*?)(\[/code:[0-9]\])@si', '@(\[quote[^\]]*?\])(.*?)(\[/quote\])@si', '@(\[b[^\]]*?\])(.*?)(\[/b\])@si', '@(\[u[^\]]*?\])(.*?)(\[/u\])@si', '@(\[i[^\]]*?\])(.*?)(\[/i\])@si', '@(\[size[^\]]*?\])(.*?)(\[/size\])@si', '@(\[color[^\]]*?\])(.*?)(\[/color\])@si', '@(\[ol[^\]]*?\])(.*?)(\[/ol\])@si', '@(\[ul[^\]]*?\])(.*?)(\[/ul\])@si', '@(\[li[^\]]*?\])(.*?)(\[/li\])@si' ); $replace = array( '${4}', // URL '[USER POSTED IMAGE]', //IMG '${2}', // CODE '${2}', // CODE '${2}', // QUOTE '${2}', // BOLD '${2}', // UNDERLINE '${2}', // ITALIC '${2}', // SIZE '${2}', // COLOR '', // ORDERED LIST '', // UNORDERED LIST '${2}' // LIST ITEM ); } $rows_count = count($rows); for($xi = 0; $xi <= $rows_count-1; $xi++){ $rows[$xi]->text = preg_replace($BBC, $replace, $rows[$xi]->text); } // Finally, send the results to Joomla! return $rows; } ?>

Here is the code without the things that you don't need:

Code:

<?php /********************************************************************************** * HAndy PHP Joomlaboard Searchbot * @version 1.0 * @package Joomlaboard * @copyright (c) 2006 Handy PHP * @license GNU GPL (http://www.gnu.org/licenses/gpl.txt) * @author http://www.handyphp.com **********************************************************************************/ // no direct access defined( '_VALID_MOS' ) or die( 'Restricted access' ); $_MAMBOTS->registerFunction( 'onSearch', 'botSearchJoomlaBoard' ); /** * Contacts Search method * * The sql must return the following fields that are used in a common display * routine: href, title, section, created, text, browsernav * @param string Target search string * @param string mathcing option, exact|any|all * @param string ordering option, newest|oldest|popular|alpha|category */ function botSearchJoomlaBoard( $text, $phrase='', $ordering='' ) { global $database, $my, $_MAMBOTS; // check if param query has previously been processed if ( !isset($_MAMBOTS->_search_mambot_params['joomlaboard']) ) { // load mambot params info $query = "SELECT params" . "\n FROM #__mambots" . "\n WHERE element = 'joomlaboard.searchbot'" . "\n AND folder = 'search'" ; $database->setQuery( $query ); $database->loadObject($mambot); // save query to class variable $_MAMBOTS->_search_mambot_params['joomlaboard'] = $mambot; } // pull query data from class variable $mambot = $_MAMBOTS->_search_mambot_params['joomlaboard']; $botParams = new mosParameters( $mambot->params ); $section_name = $botParams->def( 'search_result_section_name', 'Forum' ); $limit = $botParams->def( 'search_limit', 50 ); $text = trim( $text ); if ($text == '') { return array(); } switch ( $ordering ) { case 'alpha': $order = 'b.subject ASC'; break; case 'category': $order = 'c.name ASC, b.subject ASC'; break; case 'popular': case 'newest': $order = 'b.time DESC'; break; case 'oldest': $order = 'b.time ASC'; break; default: $order = 'b.subject DESC'; break; } $query = "SELECT b.subject AS title," . "\n a.message AS text," . "\n FROM_UNIXTIME(b.time) AS created," . "\n CONCAT('$section_name','/', c.name ) AS section," . "\n '2' AS browsernav," . "\n CONCAT('index.php?option=com_joomlaboard&func=view&catid=',b.catid,'&id=',b.thread) AS href" . "\n FROM #__sb_messages_text AS a" . "\n INNER JOIN #__sb_messages AS b ON b.id = a.mesid" . "\n INNER JOIN #__sb_categories AS c ON c.id = b.catid" . "\n WHERE ( b.subject LIKE '%$text%'" . "\n OR a.message LIKE '%$text%'" . "\n OR b.name LIKE '%$text%'" . "\n OR c.name LIKE '%$text%'" . "\n OR c.description LIKE '%$text%' )" . "\n AND c.published = 1" . "\n AND c.pub_access <= $my->gid" . "\n ORDER BY $order" ; $database->setQuery( $query, 0, $limit ); $rows = $database->loadObjectList(); // Finally, send the results to Joomla! return $rows; } ?>

So, using the last script, you should get a better idea of how your SearchBot should look. Remember, you can always add more later! If you think that you have a working SearchBot but there are now results returned when there should be, then you have to debug... Go to Admin Control Panel -> Global Configuration -> Debug Site: Yes Then perform the search again... At the top of the page will be any errors or warning that your system return for the page you are viewing. These may be for other problems on your website, not just the SearchBot. If the search function is specified and/or your SearchBot file, then you know there is a problem. At the bottom of the page is a listing of every database query used to generate the page you are currently viewing. Search result pages tend to have a lot of these queries since it searches so many places in the database. Find the query that was meant to search the table you wanted and copy the query! We want to see if that query actually performs the search you intended in the database. Go to your phpMyAdmin or whatever program you use to manually access your database and open the database that Joomla lives in. Run the query you copied and see what if any results are returned. If no results are returned but the search term should have returned at least on, then your query doesn't work. It may not have errors in it as far as MySQL is concerned but the logic you used may not find a result. If your query doesn't work and you don't have any warning or error messages, then you need a better query. In phpMyAdmin, you can use the search feature to perform a search using whatever fields and values you want to match. phpMyAdmin always shows the SQL query it used to get the results you see. If there are results as you expected and desired, then you need to change the query in your script to match the query phpMyAdmin used. A lot of information, I know. A word about your url... It can point anywhere! It doesn't have to point to a Joomla component on your site. Just make sure you use the full url instead of a relative url. I think that that is enough information for you to consider for now. vujsa

Wow vujsa, you give me more information that any person or document on the web. Hope googlebots read this!!!
I'm printing all and studying it, but with this new approach it seems that I was closer than I've thinked.
I'll let you know how it goes!

Well vujsa...IT WORKS! It was that thing with the aliases.

Obviously now I need to develop the display/href thing, but that is another story.

I leave to you the code here, in case of someone comes looking for a very starter version of a searchbot.

In this example the table where I search is named "jos_search". It has 2 fields, "id" numeric y "texto", a VARCHAR.

And the PHP part of the mambot is like this:

Code:

<?php /** *A very simple de searchbot *Script developed by Martin Revert 02/2008 *http://www.gnu.org/copyleft/gpl.html GNU/GPL * */ /** ensure this file is being included by a parent file */ defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' ); /** Define the funtion type and the name of the function to be called by Joomla search**/ $_MAMBOTS->registerFunction( 'onSearch', 'botSearch' ); /** * Search method over the Joomla database. * @param array Named 'text' element is the search term */ function botSearch( $text ) { global $database; $text = trim( $text ); if ($text == '') { return array(); } /**Now, we make the query to de DB. We can return 5 aliases wich can be interpreted by the Joomla search native module * title, created, section, href, browsernav. But you don't need to use it all, so you can null what you don't need. **/ $database->setQuery( "SELECT texto as title, '' as created, '' as section, '' as href, '' as browsernav" . " FROM #__search" . " WHERE texto LIKE '%$text%'" . " ORDER BY id" ); return $database->loadObjectList(); } ?>

Many thanks for your support! Post edited by: revertm, at: 2008/02/12 17:11<br><br>Post edited by: revertm, at: 2008/02/12 17:12