SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can you help me with my query?

    I have a table join between a user access table and a table of company information, and I want to do a LIKE query- you can see it at the very bottom of the script below (r.keywords, r.company, r.description).

    1) If I only query r.keywords my result is lightening fast and accurate, but I only get to search one column

    2) If I add the LIKE r.company and r.description to the query, it breaks the query and shows the wrong results. If I remove it and do what I did in #1 above, it works fine.

    Can you help me clean up my LIKE search? Here is my query from #2 above

    Code:
    $query = "SELECT u.id
         , u.username
    	 , p.user_id
    	 , p.report_id
         , r.id
         , r.company 
         , r.description
         , r.market1
         , r.market2
         , r.market3
         , r.market4
         , r.market5
         , r.market6
         , r.location
         , r.date_year
         , r.date_month
         , r.source
         , r.video
         , r.audio
         , r.pp
         , r.execsum
         , r.report_url
         , r.exec_url
    	 , r.keywords	  
      FROM user as u
    INNER
      JOIN user_reports as p
        ON p.user_id = u.username
    INNER
      JOIN emt_report as r
        ON r.id = p.report_id
    WHERE username = '$username' AND 
    r.keywords LIKE '%$P_search%'  // this line works fine, but limits the query to one column
    r.company LIKE '%$P_search%'  // when these lines are added it breaks
    r.description LIKE '%$P_search%' // when these lines are added it breaks
    ORDER BY r.date_year DESC, r.date_month DESC, r.company ASC";

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this:
    Code:
    AND 
    (
    r.keywords LIKE '%$P_search%'  OR
    r.company LIKE '%$P_search%'  OR
    r.description LIKE '%$P_search%'
    )

  3. #3
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Amazing, thank you!

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you did see me response to your previous post where i told you that LIKE does not utilize your FULLTEXT indexes?

  5. #5
    SitePoint Guru ripcurlksm's Avatar
    Join Date
    Aug 2004
    Location
    San Clemente, CA
    Posts
    859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, but I cant get it to work


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •