Modify free text search *Solved

All about webserver configurations, PHP and databases.

Moderator: Thorsten

Post Reply
simonhudin
Posts: 5
Joined: Mon Sep 28, 2015 8:28 am

Modify free text search *Solved

Post by simonhudin »

Hi,

I am modifying phpMyFaq for a multi-brand version where multiple brands share a large portion of the FAQ but where certain FAQ's have to be hidden depending on certain parameters.
I've added 3 custom tables to the phpMyFaq DB for Category -> Brand & FAQ -> Brand and all is set for Categories and Tags. However, I need to join in these 3 tables for the free text search and I have been looking through the code for ~2 days now without being able to back track where the free text queries are actually being built.

I know the search string values are set in the search() function in PMF/Search
And that the SQL is executed by query() in PMF/DB/Mysqli.php

However, the SQL variables / conditions in search() in PMF/Search is set in an Object.
Anyone who knows where the search string is assembled?

Thanks beforehand.
Simon
Last edited by simonhudin on Tue Oct 06, 2015 10:07 am, edited 1 time in total.
Thorsten
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Modify free text search

Post by Thorsten »

Hi,

cool project!

What do you mean by assembled?

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
simonhudin
Posts: 5
Joined: Mon Sep 28, 2015 8:28 am

Re: Modify free text search

Post by simonhudin »

Hi,

Might have expressed myself a bit weird.

In PMF\Search.php, the function search() creates an object that contains the search query in various keys.
It's not a SQL query string. So somewhere, I assume there is a function that takes the data in the keys and creates the complete SQL query string and then runs it through PMF\DB\Mysqli.php function query().
That function I was hoping I could hijack and add the table joins.

Or rewrite it to accept an array in ->setJoinedTable($fcrTable).
E.g

Code: Select all

->setJoinedTable(array($fcrTable,"custom_brand","custom_catagory","custom_data"))

Code: Select all

->setJoinedColumns(array(
                    $fdTable . '.id = ' . $fcrTable . '.record_id',
                    $fdTable . '.lang = ' . $fcrTable . '.record_lang',
                    $fdTable . '.id = custom_data.data',
                    'custom_brand.id = custom_data.brand'
					))
Etcetera.

Thanks!
Simon
Thorsten
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Modify free text search

Post by Thorsten »

Hi Simon,

that's in PMF_Search::search()

bye
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
simonhudin
Posts: 5
Joined: Mon Sep 28, 2015 8:28 am

Re: Modify free text search

Post by simonhudin »

Managed to iron out the workflow :D

In PMF\Search\Database.php

Working draft

Code: Select all

    public function getJoinedTable()
    {
			//CUSTOM SEARCH JOINEDTABLE
			if(isset($_SESSION['bmlfaq'])){
				$currentBrand = $_SESSION["bmlfaq"];
			}
			elseif(isset($_COOKIE["bmlfaq"])){
				$currentBrand = $_COOKIE["bmlfaq"];
			}
			else{
				$currentBrand = null;
			}
		
        if (empty($this->joinedTable)) {
            return '';
        } 
		elseif(isset($currentBrand)){
			return ' LEFT JOIN custom_data cda ON cda.data = fd.id LEFT JOIN custom_brand cbr ON cbr.id = cda.brand LEFT JOIN ' . $this->joinedTable . ' ON ';
		}
		else {
            return ' LEFT JOIN ' . $this->joinedTable . ' ON ';
        }
    }

Code: Select all

public function getConditions()
    {
        $conditions = '';
        
        if (count($this->conditions)) {
            foreach ($this->conditions as $column => $value) {
                if (is_array($value)) {
                    $conditions .= ' AND ' . $column . ' IN (' . implode(', ', $value) . ')';
                } else {
                    $conditions .= ' AND ' . $column . ' = ' . $value;
                }
            }
			
			//CUSTOM SEARCH CONDITION
			if(isset($_SESSION['bmlfaq'])){
				$currentBrand = $_SESSION["bmlfaq"];
			}
			elseif(isset($_COOKIE["bmlfaq"])){
				$currentBrand = $_COOKIE["bmlfaq"];
			}
			else{
				$currentBrand = null;
			}
			if(isset($currentBrand)){
				$conditions .= ' AND cbr.name = ' . "'" . strval($currentBrand) . "'";
			}
        }
        
        return $conditions;
    }
Cheers.

Will hint about the wishlist to my boss :D

Regards,
Simon
Last edited by simonhudin on Mon Oct 05, 2015 3:59 pm, edited 1 time in total.
simonhudin
Posts: 5
Joined: Mon Sep 28, 2015 8:28 am

Re: Modify free text search

Post by simonhudin »

Worth mentioning that I noticed some inconstancy with the queries passed through PMF\Search\Database.php

When you do a full text search, the query says

Code: Select all

FROM faqdata
while when opening a FAQ entry, it says

Code: Select all

FROM faqdata AS fd
Had to rebuild

Code: Select all

public function search($searchterm, $allLanguages = true)
in PMF\Search.php to use table abbreviation FD.
Search function is a bit more in line with the rest of the SQL queries and rebuild works with PMF_Db::getTablePrefix().

Code: Select all

    public function search($searchterm, $allLanguages = true)
    {

		
        $fdTable   = PMF_Db::getTablePrefix() . 'faqdata AS fd';
        $fcrTable  = PMF_Db::getTablePrefix() . 'faqcategoryrelations';
        $condition = array('fd.active' => "'yes'");
        $search    = PMF_Search_Factory::create($this->_config, array('database' => PMF_Db::getType()));

        if (!is_null($this->getCategoryId()) && 0 < $this->getCategoryId()) {
            if ($this->getCategory() instanceof PMF_Category) {
                $children = $this->getCategory()->getChildNodes($this->getCategoryId());
                $selectedCategory = array(
                    $fcrTable . '.category_id' => array_merge((array)$this->getCategoryId(), $children)
                );
            } else {
                $selectedCategory = array(
                    $fcrTable . '.category_id' => $this->getCategoryId()
                );
            }
            $condition = array_merge($selectedCategory, $condition);
        }

        if ((!$allLanguages) && (!is_numeric($searchterm))) {
            $selectedLanguage = array('fd.lang' => "'" . $this->_config->getLanguage()->getLanguage() . "'");
            $condition        = array_merge($selectedLanguage, $condition);
        }

        $search->setTable($fdTable)
               ->setResultColumns(array(
                    'fd.id AS id',
                    'fd.lang AS lang',
                    'fd.solution_id AS solution_id',
                    $fcrTable . '.category_id AS category_id',
                    'fd.thema AS question',
                    'fd.content AS answer'))
               ->setJoinedTable($fcrTable)
               ->setJoinedColumns(array(
                    'fd.id = ' . $fcrTable . '.record_id',
                    'fd.lang = ' . $fcrTable . '.record_lang'
					))
               ->setConditions($condition);
        
        if (is_numeric($searchterm)) {
            $search->setMatchingColumns(array('fd.solution_id'));
        } else {
            $search->setMatchingColumns(array('fd.thema', 'fd.content', 'fd.keywords'));
        }
		
		$result = $search->search($searchterm);

        if (!$this->_config->getDb()->numRows($result)) {
            return array();
        } else {
            return $this->_config->getDb()->fetchAll($result);
        }
    }
Thorsten
Posts: 15560
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Modify free text search *Solved

Post by Thorsten »

Hi,

thanks for the hint, just fixed the inconsistency. Will be included in 2.8.25.

Cheers
Thorsten
phpMyFAQ Maintainer and Lead Developer
amazon.de Wishlist
Post Reply