Constructing an "exact" query.

TWSmith

Greetings.

I've once again run into some trouble while working on my keyword search.

At this time I am trying to implement a function for "exact searches". In short, the ability to enclose a query with quotations so that it will look for that exact word or words within the mysql text field that the search is being directed to. (A text field called "ml" in this case. That's why any attempts at WHERE field=$search or any variations there of have been unsuccessful.)

Reason I need to create a separate function to do this is because by default, the application looks to see if there's more than one word and if there is, explode words to array, check to see what boolean the user has selected and search accordingly else do a normal search.

Now, the easy part was constructing the PHP code to search the string coming from the search form, look for any quotes and if there are any then strip the quotes and slashes then create the query. If none are there, look to see if there's more than one word, etc. (See above paragraph.)

The problem is creating the query itself.

Here's the code as it is now:

$text = '';
$the_array = explode(' ', $search);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$text .= " LIKE '%$t%' ";
$i = 1;
} else {
if ($boolean == 1 ){
$text .= " AND ml LIKE '%$t%' ";
} else {
$text .= " OR ml LIKE '%$t%' ";
}
}
}

//misc. code not relevant to the problem at hand

$result = mysql_query("SELECT * FROM tapes WHERE ml
$text ORDER BY 'tape_id' LIMIT $start,$limit");

The part I want to implement is:

if(strstr($search,"\"")) { 
$search = str_replace("\"", "", stripslashes($search));
$text .= " LIKE '$search' ";
} else { 
$text = '';
$the_array = explode(' ', $search);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$text .= " LIKE '%$t%' ";
$i = 1;
} else {
if ($boolean == 1 ){
$text .= " AND ml LIKE '%$t%' ";
} else {
$text .= " OR ml LIKE '%$t%' ";
}
}
}
}

//build query and all the other stuff

The $text .= " LIKE '$strippedquery' "; is obviously the part I need to come up with the fix for.

Everything I've tried has resulted in the query not finding anything, even though the terms I am searching for obviously exist, having tried to find them without the quotes. For instance, searching for "ric" gives zero results when it should be finding all records that contain the word ric, while searching for ric finds ric, ricky, erich, etc.

I hope I'm not confusing anyone that happens to be reading this and I'm getting my problem across clearly.

Any ideas? Thoughts?

Thanks for reading.

--Tony

Vex

Should it not be...

$text .= " LIKE '%$search%' ";

Or is the value you're searching for an exact match for ALL the data in the column?

You're searching for ric so is there a row with an ml column with only ric in it? (and no leading or trailing space, etc??

TWSmith

Originally posted by Vex
Should it not be...

$text .= " LIKE '%$search%' ";

No, that's what I'm trying to avoid.

Or is the value you're searching for an exact match for ALL the data in the column?

You're searching for ric so is there a row with an ml column with only ric in it? (and no leading or trailing space, etc??

Well, let me put it this way. The table I am searching has around 200 rows and growing daily. Each row has several columns. The tape_id auto incremented column, the video title, the date (of the event, or when a show was aired), etc. The final column is a MySQL text column called "ml" that basically acts as a description for that item. It contains the names of all of the people involved with that item, and sometimes TV taping dates.

The search only looks through the ml column of each row, although it will output the info from all of the columns when displaying the results. When a person searches, they can do a one word standard search or a multiple word boolean search using AND/OR connectors as you see in the code in my original post. The 'exact phrase' search function I'm trying to add would allow a person to look up an exact name, be it one or more words, by enclosing it in quotations. Thus, by search for "ric" they would only see results for any shows that would have a guy named "ric" on them. Searching for ric as it is now, without quotes, would output results with guys named ric, rick, ricky, erich, etc.

I hope this makes a little more sense than my previous post did.

Thanks.

--Tony

Vex

Okay...

Rows in database...

1: fred
2: fred john peter paul
3: john
4: peter smith
5: paul
6: john jones, paul smith, peter smith
7: peter smithson

Queries:

SELECT * FROM table WHERE name LIKE 'fred'
- Will match row 1

SELECT * FROM table WHERE name LIKE '%fred%'
- Will match row 1 and row 2

SELECT * FROM table WHERE name LIKE 'peter'
- Will match no rows

SELECT * FROM table WHERE name LIKE '%peter%'
- Will match rows 2, 4, 6 and 7

SELECT * FROM table WHERE name LIKE 'peter smith'
- Will match row 4

SELECT * FROM table WHERE name LIKE '%peter smith%'
- Will match row 4, 6, and 7

The problem you're having is your trying to do an exact match like queries 1, 3, and 5 which will only work if the column contains that value and that value alone.
You need the % either side of the value to allow for there being other data in the row.

The only way to do it is to ensure that there is some form of seperator between the values in the field, or to use regular expressions

You could for example ensure that there is a , before and after every word in the field:-

Rows in database...

1: ,fred,
2: ,fred,john,peter,paul,
3: ,john,
4: ,peter smith,
5: ,paul,
6: ,john jones,paul smith,peter smith,
7: ,peter smithson,

Then use something like

SELECT * FROM table WHERE name LIKE '%,peter smith,%'
- Which would bring back row 6 and 4 only

The better solution would be to use regular expressions to ensure that the it matches a whole word.

See here for details http://www.mysql.com/doc/en/Regexp.html

In fact this....

SELECT * FROM table WHERE name LIKE '[[:<:]]peter smith[[:>:]]'

Might well do the job!

TWSmith

Originally posted by Vex
In fact this....

SELECT * FROM table WHERE name LIKE '[[:<:]]peter smith[[:>:]]'

Might well do the job!

With a couple of slight modifications, that worked nicely. Thank you very much.

Now I have another slight problem to address, but it doesn't fit this particular forum since the database isn't involved.

--Tony