Seach engine for database data

[stanley87]

I am developing a search engine that match keyword search for game title and category game that is limited for three keyword search.i am facing some problem that need to swap among the keywords more than using one keyword search.Any guys can help me or give me some guide on the way of searching the keywords.

function search_game_test(){
    global $CONF, $Q, $OUT, $DB, $DBSH, $CODE, $LANG;

     $portalId = $Q->req['portalId'];
     $keyword  = $Q->req['keyword'];

        $where  = array();
        $data   = array();
        $portal_db = $DB->getOne("SELECT dbName FROM Portal_DB WHERE portalId = $portalId");

        array_push($where, array("PC.portalId = $portalId"));
        array_push($where, array("PC.status = 'A'"));
        array_push($where, array("PC.contentId = G.gameId"));

        $trimmedKeyword = preg_replace('/ (?=[ .,])/', '', trim(preg_replace('/[\\~`!@$%^&*()+{}[\]<>?\/|="]/', '', trim($keyword))));
        $explodeKeyword = explode(' ', $trimmedKeyword);
        $keyword= ucfirst ($explodeKeyword);

        //Check keyword in category list
        $categoryCode  = '';

    for ($i = 0; $i < count($keyword); $i++)
    {
        if($i = 0){
            $categoryCode  = $CODE['GameCate2'][$keyword[0]];
            if ($keyword != ''){
                    if($categoryCode!=''){
                       array_push($where, array("(G.titleFull LIKE '%$keyword[0]%' OR G.gameCate = '$categoryCode')"));
                    }
                    else{
                         array_push($where, array("G.titleFull LIKE '%$keyword[0]%'"));
                    }
                    $group = 'G.gameId';
                    $keyword = $keyword[0] ;
                }

        }
        else if ($i = 1){
            $categoryCode  = $CODE['GameCate2'][$keyword[0].' '.$keyword[1]];
            if ($keyword != ''){
                    if($categoryCode!=''){
                       array_push($where, array("(G.titleFull LIKE (('%$keyword[0]%'.' '.'%$keyword[1]%') OR ('%$keyword[0]%' OR '%$keyword[1]%')) OR G.gameCate = '$categoryCode')"));
                    }
                    else{
                         array_push($where, array("G.titleFull LIKE (('%$keyword[0]%'.' '.'%$keyword[1]%') OR ('%$keyword[0]%' OR '%$keyword[1]%'))"));
                    }
                    $group = 'G.gameId';
                    $keyword = $keyword[0].' '.$keyword[1] ;
                }
        }else{

            $categoryCode  = $CODE['GameCate2'][$keyword[0].' '.$keyword[1].' '.$keyword[2]];
            if ($keyword != ''){
                    if($categoryCode!=''){
                       array_push($where, array("(G.titleFull LIKE ('%$keyword[0]%'.' '.'%$keyword[1]%'.' '.'%$keyword[2]%')OR ('%$keyword[0]%' OR '%$keyword[1]%' OR '%$keyword[2]%')) OR G.gameCate = '$categoryCode')"));
                    }
                    else{
                         array_push($where, array("G.titleFull LIKE (G.titleFull LIKE ('%$keyword[0]%'.' '.'%$keyword[1]%'.' '.'%$keyword[2]%') OR ('%$keyword[0]%' OR '%$keyword[1]%' OR '%$keyword[2]%'))"));
                    }
                    $group = 'G.gameId';
                    $keyword = $keyword[0].' '.$keyword[1].' '.$keyword[2] ;
                }       
        } 
};

        $total = $DBSH->select(array(
            'field' => array('G.gameId','G.titleFull','G.descFull','G.gameCate','G.publisherId','PC.price','PC.liveDate','P.publisherName','P.publisherId'),
            'table' => 'Game G, PortalContent PC, Publisher P',
            'where' => $where,
            'order' => 'titleFull',
        'group' => 'G.gameId',
        ));

        //IF SEARCH GAME KEYWORD IS NUMERIC ei: 1944 for Air Strike 1944 gametitle
        if( is_numeric($keyword) && $total == 0 ){
            $where[count($where)-1] = array("G.titleFull LIKE '%$keyword%'");
            $total = $DBSH->select(array(
                'field' => array('G.gameId','G.titleFull','G.descFull','G.gameCate','G.publisherId','PC.price','PC.liveDate','P.publisherName','P.publisherId'),
                'table' => 'Game G, PortalContent PC, Publisher P',
                'where' => $where,
                'group' => 'G.gameId',
                'order' => $order,
                'limit' => "$offset,$limit",
            ));             
        }           


        if ($total > 0){
                if($categoryCode!=''){
                   $total_count_pg  = $DB->getOne("SELECT COUNT(*) FROM Game G, PortalContent PC WHERE PC.portalId = '$portalId' AND (G.metaKeyword LIKE '%$keyword%' OR G.gameCate = '$categoryCode') AND PC.contentId = G.gameId AND PC.status='A'");
                }elseif(is_numeric($keyword)){
                     $total_count_pg  = $DB->getOne("SELECT COUNT(*) FROM Game G, PortalContent PC WHERE PC.portalId = '$portalId' AND PC.smskeyword LIKE '%$keyword%' AND PC.contentId = G.gameId AND PC.status='A'");
                }else{
                     $total_count_pg  = $DB->getOne("SELECT COUNT(*) FROM Game G, PortalContent PC WHERE PC.portalId = '$portalId' AND G.titleFull LIKE '%$keyword%' AND PC.contentId = G.gameId AND PC.status='A'");
                }

            while($row = $DBSH->fetchRow(DB_FETCHMODE_ASSOC)){
                $row['gameName'] = stripslashes($row['titleFull']);
                $row['publisherName'] = stripslashes($row['publisherName']);
                $row['gameDesc'] = text_truncate2(stripslashes($row['descFull']),80);
                $row['gameCate'] = $CODE['GameCate'][$row['gameCate']];
                $row['price'] = $row['price'];      
                $row['rating'] = $DB->getOne("SELECT rating FROM $portal_db.Snapshot_Rating WHERE typeId='101' AND contentId='{$row['gameId']}'");
                if ($row['rating'] == ''){
                    $row['rating'] = 0;
                }       
                $data[] = $row;
            }
        }else{


            if ($keyword != ''){
                array_push($where, array("G.titleFull LIKE '%$keyword%'"));
                array_push($where, array("PC.contentId = G.gameId"));
                $group = 'G.gameId';
            }

            $total = $DBSH->select(array(
                'field' => array('G.gameId','G.titleFull','G.descFull','G.gameCate','G.publisherId','PC.price','PC.liveDate','P.publisherName','P.publisherId'),
                'table' => 'Game G, PortalContent PC, Publisher P',
                'where' => $where,
                'group' => $group,
                'order' => $order,
                //'limit' => "$offset,$limit",

            ));

            while($row = $DBSH->fetchRow(DB_FETCHMODE_ASSOC)){
                $row['gameName'] = stripslashes($row['titleFull']);
                $row['publisherName'] = stripslashes($row['publisherName']);
                $row['gameDesc'] = text_truncate2(stripslashes($row['descFull']),80);
                $row['gameCate'] = $CODE['GameCate'][$row['gameCate']];
                $row['price'] = $row['price'];      
                $row['rating'] = $DB->getOne("SELECT rating FROM $portal_db.Snapshot_Rating WHERE typeId='101' AND contentId='{$row['gameId']}'");
                if ($row['rating'] == ''){
                    $row['rating'] = 0;
                }       
                $data[] = $row;
            }               
        }

            if( count($data) < 1 ){
             $data = array('Error' => 0);
          }else{
       $data = array('Result' => $data);
            }

            $this->provider->response($data);
}

[smantscheff]

It would be easier for us to help you if you would clearly state your problem.

[stanley87]

sorry for giving not clear info,let say i have game title harry porter 2 , and then the user type in "porter harry' , i wanna the result swap among the keywords and match with the "harry porter" and show the result.

[smantscheff]

Where do you look for keywords? In one table column or in several? In one table or in several? Why do you have to swap the keywords? Would the result be different for "porter harry" and for "harry porter"?

[stanley87]

i am searching in several table but in the same database, let say i am searching 3 keywords together ,so i have to split them all and match any combination among three keywords, let say the "harry potter" = 2 keywords searching,but my result would me match the word "harry potter" as 1 keyword , or seperate the "harry" or "potter" as 1 keyword search,after than i also will match any combination between the "harry" and "potter" become either "harry potter" or "potter harry"

[smantscheff]

Sou you want to match one, two or more keywords to various fields in the database.
I recommend a regular expression search over a concatenation of fields.
Example:

select * from table1 join table2 on table1.id=table2.id
where concat(table1.field1, ' ', table1.field2, ' ', table2.field1) rlike "(keyword1|keyword2)"

[Web Dev Rob]

Slightly off topic, but have you considored using MySQL's full text searching instead of LIKE?

[stanley87]

i wanna add some limitation which is the query must not burden the bandwidth using because the searching in doing on the wap site and have the phone limitation on the bandwidth especially at the database side.

[Javvy]

Maybe you can try breaking the search term into array then concat the query statement to search for it.

[smantscheff]

The query is running on the server and not consuming bandwidth. Bandwidth is consumed by the query results, if they are transmitted as a whole.

[stanley87]

Already fixed the problem myself,here is part of useful info.However thanks for guys's opinion.

        $search_keywords=strtolower($search);
        $search_keywords = strip_tags($search_keywords);
        $search_keywords = mysql_real_escape_string($search_keywords);

        //$replace_keywords= ereg_replace("[^A-Za-z0-9 ]", ' ', trim($search_keywords));
        //$replace_keywords = ereg_replace("[^A-Za-z0-9 :-]", "", trim($search_keywords));
        $replace_keywords = preg_replace("/[^a-zA-Z0-9:-\s]/", "", trim($search_keywords));

                //$replace_keywords = stripslashes($replace_keywords);


             $search_exploded = explode(" ",$replace_keywords); //get array keyword
             $search_exploded = array_unique($search_exploded);  //remove duplicate words
             $search_each = array();
             $x=0;
             $construct2='';

                    foreach($search_exploded as $search_each){
                        //echo $search_each ."<br />" ;
                        if(!in_array($search_each, $Filter['invalid_keyword'])&& strlen($search_each) > 2) {

                        $x++;
                         if ($x==1){
                            //$construct .= "titleFull LIKE '%$search_each%' OR descFull LIKE '%$search_each%' OR keywords LIKE '%$search_each%'" ;
                            $construct2 .= "concat(titleFull, ' ', descFull, ' ', keywords) LIKE '%$search_each%'";

                         }
                         else{
                            $construct2 .= " OR concat(titleFull, ' ', descFull, ' ', keywords) LIKE '%$search_each%'";
                            //$construct .= " OR titleFull LIKE '%$search_each%' OR descFull LIKE '%$search_each%' OR keywords LIKE '%$search_each%'" ;      

                         }

                        }
                    }


            $construct = "SELECT * FROM isosalis2.Game WHERE status='A' AND $construct2 ";

[stanley87]

Duplicate post