Search From Different Tables

Discussion in 'PHP' started by yoursanjay, May 11, 2009.

0
  1. #1
    I have 2 fields, Keywords & Type. I have to search from 2 different tables knb_faq & knb_solution as per the keywords & type.
    Here is my mysql query :

    
$keyword=$_GET['keyword'];
$type=$_GET['type'];
$srch=mysql_query("select knb_faq.*,knb_solution.* from knb_faq,knb_solution where (knb_faq.solution LIKE '%$keyword%' or knb_faq.keywords LIKE '%$keyword%' or knb_faq.subject LIKE '%$keyword%'and knb_faq.type='$type') or
 (knb_solution.solution LIKE '%$keyword%' or knb_solution.keywords LIKE '%$keyword%' or knb_faq.solution LIKE '%$keyword%' and knb_solution.type='$type')") or die (mysql_error());
    Code (markup):
    There is no error msg but my problem is that for this query I am not getting result perfectly. The search is occured but it is not searching from both the tables according to Keyword & Type.
    Is there any wrong in the sql query? If so, Please tell me the right syntax.
     
    yoursanjay, May 11, 2009 IP
  2. EricBruggema

    EricBruggema Well-Known Member

    Messages:
    1,740
    Likes Received:
    28
    Best Answers:
    13
    Trophy Points:
    175
    #2
    First try to format your queries to readable formats.

    Example:
    
$query = "SELECT knb_faq.*,
                 knb_solution.* 
          FROM knb_faq,
               knb_solution 
          WHERE (knb_faq.solution LIKE '%" . $keyword . "%' OR 
                 knb_faq.keywords LIKE '%" . $keyword . "%' OR 
                 knb_faq.subject  LIKE '%" . $keyword . "%' AND 
                 knb_faq.type = '" . $type . "') OR
                (knb_solution.solution LIKE '%" . $keyword . "%' OR
                 knb_solution.keywords LIKE '%" . $keyword . "%' OR
                 knb_faq.solution      LIKE '%" . $keyword . "%' AND
                 knb_solution.type = '" . $type . "')";
    Code (markup):
    but maby this works for your problem

    ((knb_faq.solution LIKE '%" . $keyword . "%' OR 
  knb_faq.keywords LIKE '%" . $keyword . "%' OR 
  knb_faq.subject  LIKE '%" . $keyword . "%') AND 
  knb_faq.type = '" . $type . "') OR
    Code (markup):
    INSTEAD OF

    (knb_faq.solution LIKE '%" . $keyword . "%' OR 
 knb_faq.keywords LIKE '%" . $keyword . "%' OR 
 knb_faq.subject  LIKE '%" . $keyword . "%' AND 
 knb_faq.type = '" . $type . "') OR
    Code (markup):
     
    EricBruggema, May 11, 2009 IP
  3. yoursanjay

    yoursanjay Member

    Messages:
    42
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #3
    I have tried using your code but no improve, giving the same result. Proper search is not going on, result is comming out only from solution table & showing the all data of solution table. It is not searching from faq table. I need to search from both the tables as per the 'keyword'.
     
    yoursanjay, May 11, 2009 IP
  4. EricBruggema

    EricBruggema Well-Known Member

    Messages:
    1,740
    Likes Received:
    28
    Best Answers:
    13
    Trophy Points:
    175
    #4
    or try LINKING tables with LEFT / RIGHT / INNER join, check mysql for the documentation.
     
    EricBruggema, May 11, 2009 IP
  5. somanweb

    somanweb Peon

    Messages:
    21
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Hi,

    you can try this code

    $query = "SELECT f.*, s.* FROM knb_faq AS f , knb_solution AS s "
    . "\n WHERE ( f.solution LIKE '%" . $keyword ."%' OR"
    . "\n f.keywords LIKE '%". $keyword . "%' OR"
    . "\n f.subject LIKE '%". $keyword ."% OR"
    . "\n s.isolution LIKE '%". $keyword ."% OR"
    . "\n s.keywords LIKE '%". $keyword ."% OR"
    . "\n ) AND f.type ='". $type ."' AND s.solution = '".$type."';"


    Before use this code, verify the anyone of above fields have this search keyword and two table type field have the $type value.

    Soman
     
    somanweb, May 11, 2009 IP