having trouble search through mysql database

I have two questions regarding my script and searching. I have this script:

$searchTerms = explode(' ', $varSearch);
$searchTermBits = array();
foreach($se开发者_如何学编程archTerms as $term){
    $term = trim($term);
    if(!empty($term)){
        $searchTermBits[] = "column1 LIKE '%".$term."%'";
    }
}

$sql = mysql_query("SELECT * FROM table WHERE ".implode(' OR ', $searchTermBits)."");

I have a column1 with a data name "rock cheer climbing here"

If I type in "rock climb" this data shows. Thats perfect, but if I just type "Rocks", it doesn't show. Why is that?

Also, How would I add another "column2" for the keyword to search into?

Thank you!

---

Searching that string for "rocks" doesn't work, because the string "rocks" doesn't exist in the data. Looking at it, it makes sense to you, because you know that the plural of "rock" is "rocks", but the database doesn't know that.

One option you could try is removing the S from search terms, but you run into other issues with that - for example, the plural of "berry" is "berries", and if you remove the S, you'll be searching for "berrie" which doesn't get you any further.

You can add more search terms by adding more lines like

$searchTermBits[] = "column1 LIKE '%".$term."%'";

and replacing ".$term." with what you want to search for. For example,

$searchTermBits[] = "column1 LIKE '%climb%'";

One other thing to note... as written, your code is susceptible to SQL injection. Take this for example... What if the site visitor types in the search term '; DROP TABLE tablename; You've just had your data wiped out.

What you should do is modify your searchTermBits[] line to look like:

$searchTermBits[] = "column1 LIKE '%" . mysql_real_escape_string($term) . "%'";

That will prevent any nastiness from harming your data.

---

Assuming the data you gave is accurate, it shouldn't match because you're using "Rocks" and the word in the string is "rock". By default mysql doesn't do case sensitive matching, so it's probably not the case.

Also, to avoid sql injection, you absolutely should be using mysql_real_escape_string to escape your content.

Adding a second column would be pretty easy as well. Just add two entries to your array for every search term, one for column1 and one for column2.

---

1. Your column1 data rock cheer climbing here your search criteria %Rocks% it doesn't fit at all as rocks is not in your column1 data

2. you can add column2 as you do for column1 then put it all together by using an AND operator (column1 LIKE "%rock%" OR column1 LIKE "%climb%") AND (column2 LIKE "%rope%" OR column2 LIKE "%powder%")

TIPS: If your table/schema are using xx_xx_ci collation (then this is mean case insensitive,mysql doesn't care case sensitive) but if other then you need to make sure that the search term must be case sensitive(mysql do case sensitive).