Problems with PHP/MySQL and LIKE

ffenliv

i've got the following query:

$query = sprintf("SELECT part_field1, field2, field3, field4 
                                      FROM table 
                                      WHERE field2 
                                      LIKE '%s%' 
                                      ORDER BY field1",
	mysql_real_escape_string($field2));

unfortunately my knowledge of both php and mysql is limited at this time.

the gist of what i need done is this: I need to search for a word or part of a word (or phrase, i suppose) inside a text field in the MySQL database.

For reasons beyond my understanding ... it will find the rows that it should when i enter 2 or more words .. but i need it to find it if even part of a word in the field is searched for.

I hope I haven't been too vague here .

jer

ragefu

Your SQL is correct. LIKE will only match the the string you provide. If you search for "java" in "javachip", you should get a match.

Also, databases do not have Fields, they have Columns.

ffenliv

Sorry for my incorrect terms .. it made sense in my head 🙂

I used that very query (sorry for the rhyme) to query something like 'Wes' expecting it to find 'Western' or 'Western Digital' but it does not.

The text field has as many as 14 or 15 words in some cases, but it never, ever returns any rows in which partial matches were made.

However, as I mentioned before searching for Western Digital (2 out of 12 or 13 words) will find the row, but 'Wes' or even 'Western' or 'Western D' does not.

ragefu

try

LIKE BINARY '%Wes%'

Roger_Ramjet

I've an idea what your problem is, but could you please echo out the $query before you run it so that we can see EXACTLY what you are trying to run. And then copy/paste the echoed string here.

ffenliv

ok, herès how the echod string looked (i cant copy paste, its on another machine that for the next 2 days doesnt have internet access.

SELECT column1, column2, column3, column3 FROM table1 WHERE column2 LIKE wester% ORDER BY column1

there are single quotes around wester%, but this keybaord is set for french accesnts on those keys, and the owner would rather that I didnt change them, and i didnt feel like looking through the character map for them.

i changed the column names and the table name, but everything else is identical

I hope i was clear enough earlier, but just in case i wasnt

the column that ièm trying to select from is a TEXT column, and I need or was hoping that it could take the user inputted value and search thropugh the entire text of that column, not just try to match it from the start of the column.

Roger_Ramjet

Your problem is with the way you are using mysql_real_escape_string and sprintf
- between them they are loosing the % wildcard at the beginning and screwing up your quotes, which is why it works with multiple words but not with one.

Without seeing the echoed query string it's a bit hard to figure out what is all going on.

Far simpler to point you to the best-practice code from the manual

Add the following function to your common functions include (I hope you've got one - you should have)


function quote_smart($value)
{
   // Stripslashes
   if (get_magic_quotes_gpc()) {
       $value = stripslashes($value);
   }
   // Quote if not integer
   if (!is_numeric($value)) {
       $value = "'" . mysql_real_escape_string($value) . "'";
   }
   return $value;
}

You then use it like this


$query = sprintf("SELECT part_field1, field2, field3, field4 
                                      FROM table 
                                      WHERE field2 
                                      LIKE %s 
                                      ORDER BY field1",
	                 quote_smart('%' . $field2 . '%'));

You will now have a query string that is protected from sql injection attacks, properly quoted and with the wildcards % in place to match anywhere in the text.