need help w/ better search results

wil831

if $query="day" how do i select only columns where it'll match that exact $query? i don't want it to select a column like "today is monday" but "day five" or "one day at a time" or "last day" is ok..

this is what i'm trying to change right now...

mysql_query("SELECT col FROM table WHERE col LIKE %$query%", $connect);

juschillinnow

$query = "select *, IF(STRCMP(col,'day'), col, '') as mysort from table order by mysort";

try that

batman

$sql = "SELECT col FROM table WHERE col LIKE %" .$query. "%";
$query = mysql_query($sql, $connect);

wil831

Originally posted by batman

$sql = "SELECT col FROM table WHERE col LIKE %" .$query. "%";
$query = mysql_query($sql, $connect);

[/B]

i don't think that is what i want since $query could be in the beginning or end of the field... that will only search the middle of the fields...

i was thinking of using REGEXP, but not sure about the code...

juschillinnow

are you sure what I posted won't work for you?

wil831

juschillinnow, it may work, but i'm not sure how to implement that in my code because $sql varies depending on whether a field in a form is entered or not... here's part of the code ($artist and $title are text input from a form):


$sql="SELECT * FROM bpmdb_song WHERE hide='0'";
if (!empty($artist)) $sql.=" AND artist LIKE '%".addslashes($artist)."%'";
if (!empty($title)) $sql.=" AND title LIKE '%".addslashes($title)."%'";

batman

Did you try it? Don't say something won't work unless you try it at least. Only mistake I saw in it was searching for like query, but then there is $query below it as well. just change query to query2. See if it works, then if not, then post that it won't work.

wil831

it does work, just that when $artist is something like "nas" it'll output "anastasia" as well, which i don't want...

batman

I just re-read your first question, and now understand what you meant. Sorry 'bout that.

HalfaBee

you probably want a regexp something like this "[^{a-zA-Z]day[^{a-zA-Z]|^{day[^{a-zA-Z]|day$"}}}}

This should not let the word day have any letters around it.
I am not quite up to speed with this, but it does work.
Maybe a regex guru will com along and show us how it done.

HalfaBee