I'm not sure I understand your question correctly.
You want to show categories which match the search query, correct?
I would implement a new block, add it via layout.xml to the catalogsearch_result_index
and/or catalogsearch_advanced_result
.
The block only needs to get all the categories which match the query, here you need to define whether you want like or fulltext search, or something more complex.
Then you need a template, iterate over all the categories and output them.
Try this
SELECT `e`.*, `at_prefix`.`value` AS `prefix`, `at_firstname`.`value` AS `firstname`, `at_middlename`.`value` AS `middlename`, `at_lastname`.`value` AS `lastname`, `at_suffix`.`value` AS `suffix`, CONCAT(IF(at_prefix.value IS NOT NULL AND at_prefix.value != '', CONCAT(LTRIM(RTRIM(at_prefix.value)), ' '), ''), LTRIM(RTRIM(at_firstname.value)), ' ', IF(at_middlename.value IS NOT NULL AND at_middlename.value != '', CONCAT(LTRIM(RTRIM(at_middlename.value)), ' '), ''), LTRIM(RTRIM(at_lastname.value)), IF(at_suffix.value IS NOT NULL AND at_suffix.value != '', CONCAT(' ', LTRIM(RTRIM(at_suffix.value))), '')) AS `name`, `at_default_billing`.`value` AS `default_billing`, `at_billing_street`.`value` AS `billing_street`, `at_billing_postcode`.`value` AS `billing_postcode`, `at_billing_city`.`value` AS `billing_city`, `at_billing_telephone`.`value` AS `billing_telephone`, `at_billing_fax`.`value` AS `billing_fax`, `at_billing_region`.`value` AS `billing_region`, `at_billing_country_code`.`value` AS `billing_country_code`, `at_default_shipping`.`value` AS `default_shipping`, `at_shipping_street`.`value` AS `shipping_street`, `at_shipping_postcode`.`value` AS `shipping_postcode`, `at_shipping_city`.`value` AS `shipping_city`, `at_shipping_telephone`.`value` AS `shipping_telephone`, `at_shipping_fax`.`value` AS `shipping_fax`, `at_shipping_region`.`value` AS `shipping_region`, `at_shipping_country_code`.`value` AS `shipping_country_code`, `at_taxvat`.`value` AS `taxvat` FROM `customer_entity` AS `e` LEFT JOIN `customer_entity_varchar` AS `at_prefix` ON (`at_prefix`.`entity_id` = `e`.`entity_id`) AND (`at_prefix`.`attribute_id` = '826') LEFT JOIN `customer_entity_varchar` AS `at_firstname` ON (`at_firstname`.`entity_id` = `e`.`entity_id`) AND (`at_firstname`.`attribute_id` = '1') LEFT JOIN `customer_entity_varchar` AS `at_middlename` ON (`at_middlename`.`entity_id` = `e`.`entity_id`) AND (`at_middlename`.`attribute_id` = '827') LEFT JOIN `customer_entity_varchar` AS `at_lastname` ON (`at_lastname`.`entity_id` = `e`.`entity_id`) AND (`at_lastname`.`attribute_id` = '2') LEFT JOIN `customer_entity_varchar` AS `at_suffix` ON (`at_suffix`.`entity_id` = `e`.`entity_id`) AND (`at_suffix`.`attribute_id` = '828') LEFT JOIN `customer_entity_int` AS `at_default_billing` ON (`at_default_billing`.`entity_id` = `e`.`entity_id`) AND (`at_default_billing`.`attribute_id` = '7') LEFT JOIN `customer_address_entity_text` AS `at_billing_street` ON (`at_billing_street`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_street`.`attribute_id` = '16') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_postcode` ON (`at_billing_postcode`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_postcode`.`attribute_id` = '14') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_city` ON (`at_billing_city`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_city`.`attribute_id` = '15') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_telephone` ON (`at_billing_telephone`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_telephone`.`attribute_id` = '17') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_fax` ON (`at_billing_fax`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_fax`.`attribute_id` = '18') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_region` ON (`at_billing_region`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_region`.`attribute_id` = '12') LEFT JOIN `customer_address_entity_varchar` AS `at_billing_country_code` ON (`at_billing_country_code`.`entity_id` = `at_default_billing`.`value`) AND (`at_billing_country_code`.`attribute_id` = '11') LEFT JOIN `customer_entity_int` AS `at_default_shipping` ON (`at_default_shipping`.`entity_id` = `e`.`entity_id`) AND (`at_default_shipping`.`attribute_id` = '8') LEFT JOIN `customer_address_entity_text` AS `at_shipping_street` ON (`at_shipping_street`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_street`.`attribute_id` = '16') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_postcode` ON (`at_shipping_postcode`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_postcode`.`attribute_id` = '14') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_city` ON (`at_shipping_city`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_city`.`attribute_id` = '15') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_telephone` ON (`at_shipping_telephone`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_telephone`.`attribute_id` = '17') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_fax` ON (`at_shipping_fax`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_fax`.`attribute_id` = '18') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_region` ON (`at_shipping_region`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_region`.`attribute_id` = '12') LEFT JOIN `customer_address_entity_varchar` AS `at_shipping_country_code` ON (`at_shipping_country_code`.`entity_id` = `at_default_shipping`.`value`) AND (`at_shipping_country_code`.`attribute_id` = '11') LEFT JOIN `customer_entity_varchar` AS `at_taxvat` ON (`at_taxvat`.`entity_id` = `e`.`entity_id`) AND (`at_taxvat`.`attribute_id` = '830') WHERE (`e`.`entity_type_id` = '1')
Basically this query you will get using customer collection with join of address data. if this doesn't work you can create one sample file put below code in that and run it in browser you will get the query which you can run directly in database.
<?php
require_once 'app/Mage.php';
umask(0);
$app = Mage::app('default');
$collection = Mage::getResourceModel('customer/customer_collection')
->addNameToSelect()
->addAttributeToSelect('email')
->addAttributeToSelect('created_at')
->addAttributeToSelect('group_id')
->joinAttribute('billing_street', 'customer_address/street', 'default_billing', null, 'left')
->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
->joinAttribute('billing_fax', 'customer_address/fax', 'default_billing', null, 'left')
->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
->joinAttribute('billing_country_code', 'customer_address/country_id', 'default_billing', null, 'left')
->joinAttribute('shipping_street', 'customer_address/street', 'default_shipping', null, 'left')
->joinAttribute('shipping_postcode', 'customer_address/postcode', 'default_shipping', null, 'left')
->joinAttribute('shipping_city', 'customer_address/city', 'default_shipping', null, 'left')
->joinAttribute('shipping_telephone', 'customer_address/telephone', 'default_shipping', null, 'left')
->joinAttribute('shipping_fax', 'customer_address/fax', 'default_shipping', null, 'left')
->joinAttribute('shipping_region', 'customer_address/region', 'default_shipping', null, 'left')
->joinAttribute('shipping_country_code', 'customer_address/country_id', 'default_shipping', null, 'left')
->joinAttribute('taxvat', 'customer/taxvat', 'entity_id', null, 'left');
echo $collection->getSelect();
Best Answer
Using @mpaepper answer I was able to get a query that seems to be pulling the correct information back. I was able to test it on two different magento databases and each looked right. Anyway here it is.
I probably didn't need to join eav_entity_type but I will probably be using this query again on different versions of magento so I think this will help keep the query reusable.