SQL and and or problem

Discussion in 'Web Design and Development' started by Cabbit, Oct 5, 2008.

  1. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #1
    PHP:
    WHERE 
    story
    .`titleLIKE '%$search%'
    OR
    story.`authorLIKE '%$search%'
    OR
    story.`posterLIKE '%$search%'
    OR
    story.`bodyLIKE '%$search%'
    This is the end of my sql script for a search, now it only does the last one and not all of them so the results are only what it finds in the body and nothing else. How do i make the results work thoughout?
     
  2. notnek macrumors 6502

    notnek

    Joined:
    Oct 25, 2007
    #2
    i'm sure theres a shorter way, but i just use:

    PHP:
    WHERE title LIKE '%$search%' OR title LIKE '%$search' OR title LIKE '$search%' OR title '$title'
    and then

    PHP:
    WHERE tags LIKE '%$search%' OR tags LIKE '%$search' OR tags LIKE '$search%' OR tags '$title'
    it gets the job done.
     
  3. nomade macrumors member

    nomade

    Joined:
    Dec 2, 2006
    #3
    and-or

    WHERE
    `title` LIKE '%$search%'
    XOR
    `author` LIKE '%$search%'
    XOR
    `poster` LIKE '%$search%'
    XOR
    `body` LIKE '%$search%'
     
  4. savar macrumors 68000

    savar

    Joined:
    Jun 6, 2003
    Location:
    District of Columbia
    #4
    Nope. You used XOR which stands for "eXclusive OR", which means, "this OR that, but NOT both".

    The original poster had the right query (ignoring the SQL injection)... maybe he can post some more information about why he doesn't think it works.

    Edit: I'm guessing the OP's code is not complete, he just posted a snippet, and the problem is the order of operations. AND has higher precedence than OR.
     
  5. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #5
    PHP:
    $sql "SELECT rate.`total_value`, rate.`total_votes`, story.`id`, story.`title`, story.`author`, story.`poster`, story.`views`, story.`flag`, COUNT(comment.`story_id`) AS `commentcount` FROM `storys` story LEFT JOIN `storys_comments` comment ON story.`id`= comment.`story_id` LEFT JOIN `stories_ratings` AS `rate` ON story.`id` = rate.`id` 
    WHERE 
    story.`title` LIKE '%
    $search%'
    OR
    story.`author` LIKE '%
    $search%'
    OR
    story.`poster` LIKE '%
    $search%'
    OR
    story.`body` LIKE '%
    $search%'
    GROUP BY story.id ORDER BY "
    .$filter_by." LIMIT $offset$rowsPerPage";
    this is the full query. The reason i feel it does not work is because when $search is something i know that is only in the title or author or poster it comes back an error but if its content in the body i get the expected results.
     
  6. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #6
    ah i think i have got it the search is case sensitive i need to make it case insensitive. Ok so how do i re work the query to check for a lower case version of a word it is looking for.


    Say i am looking for The, and in the database the value is the. The result currently would be null so i need to include logic that will make it search for the lowercase version of a word too.
     
  7. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #7
    PHP:
    $sql "SELECT rate.`total_value`, rate.`total_votes`, story.`id`, story.`title`, story.`author`, story.`poster`, story.`views`, story.`flag`, COUNT(comment.`story_id`) AS `commentcount` FROM `storys` story LEFT JOIN `storys_comments` comment ON story.`id`= comment.`story_id` LEFT JOIN `stories_ratings` AS `rate` ON story.`id` = rate.`id` 
    WHERE
    story.`body` LIKE'%
    $search%'
    OR
    LOWER(story.`body`) LIKE '%
    $search%'
    OR
    UPPER(story.`body`) LIKE '%
    $search%'
    OR
    story.`title` LIKE'%
    $search%'
    OR
    LOWER(story.`title`) LIKE '%
    $search%'
    OR
    UPPER(story.`title`) LIKE '%
    $search%'
    OR
    story.`author` LIKE'%
    $search%'
    OR
    LOWER(story.`author`) LIKE '%
    $search%'
    OR
    UPPER(story.`author`) LIKE '%
    $search%'
    OR
    story.`poster` LIKE'%
    $search%'
    OR
    LOWER(story.`poster`) LIKE '%
    $search%'
    OR
    UPPER(story.`poster`) LIKE '%
    $search%'
    GROUP BY story.id ORDER BY "
    .$filter_by." LIMIT $offset$rowsPerPage";
    Okies dokies i have made it kinda case insensitive, if i type JerryLouise, JERRYLOUISE, or jerrylouise i get the same result, but if i have mixed upper and lower such as JERRYlouise it returns a null result.
     
  8. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #8
    okies another wee one on top of this but a little off topic.
    Ok so i have a search page for a story page, now it searches for words the user enters, now how can i make it so that say its calling `body` from the database as $row[`body`] and the value in $search is "the". I would like it to go though $row[`body`] and replace all instances of "the" with <span class="highlighted">the</span> so that the search results look a wee bit more pro and tell the user what the search system found for them.


    EDIT: WORKED THIS BIT OUT EASY
     

Share This Page