SQL and and or problem

[Cabbit]

WHERE 
story.`title` LIKE '%$search%'
OR
story.`author` LIKE '%$search%'
OR
story.`poster` LIKE '%$search%'
OR
story.`body` LIKE '%$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?

 

[notnek]

i'm sure theres a shorter way, but i just use:

WHERE title LIKE '%$search%' OR title LIKE '%$search' OR title LIKE '$search%' OR title = '$title'

and then

WHERE tags LIKE '%$search%' OR tags LIKE '%$search' OR tags LIKE '$search%' OR tags = '$title'

it gets the job done.

 

[nomade]

and-or

WHERE
`title` LIKE '%$search%'
XOR
`author` LIKE '%$search%'
XOR
`poster` LIKE '%$search%'
XOR
`body` LIKE '%$search%'

 

[savar]

and-or

WHERE
`title` LIKE '%$search%'
XOR
`author` LIKE '%$search%'
XOR
`poster` LIKE '%$search%'
XOR
`body` LIKE '%$search%'

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.

 

[Cabbit]

$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.

 

[Cabbit]

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.

 

[Cabbit]

$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.

 

[Cabbit]

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