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 » Mon Sep 28, 2015 8:42 am

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: 14486
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Modify free text search

Post by Thorsten » Tue Sep 29, 2015 5:45 am

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 » Tue Sep 29, 2015 8:52 am

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: 14486
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Modify free text search

Post by Thorsten » Sat Oct 03, 2015 12:50 pm

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 » Mon Oct 05, 2015 2:37 pm

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 » Mon Oct 05, 2015 3:56 pm

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: 14486
Joined: Tue Sep 25, 2001 11:14 am
Location: #phpmyfaq
Contact:

Re: Modify free text search *Solved

Post by Thorsten » Wed Oct 07, 2015 10:07 am

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

Who is online

Users browsing this forum: No registered users and 1 guest