Need help with code to search database

[gerble1000]

hi guys im new here and new to php

 

i have this search code in my website that returns the matching search searches.

my problem is the script is shit!

if the title i am looking for is "Captain America"

and i search "captain america" it returns with no issue,

but if my title is "Captain.America"

then i get no results.

i figured i need to tell the script to take these characters in the title "_-." and look at them as spaces.

here is the code i got

 

 

 

$search = $this->getState('filter.search');


if (!empty($search)) {

if (stripos($search, 'id:') === 0) { 
$query->where('t.fid = '.(int) substr($search, 3)); 
} else {

$search = $db->Quote('%'.$db->getEscaped($search, true).'%'); 

$query->where('( t.name LIKE '.$search.' )');
}

}


return $query;

could somebody explain as to what i should do please

been racking my brain for hours on this

 

i was coming up with ideas like this

$search = $this->getState('filter.search');
$search2 = str_replace(' ', '.', $search);

if (!empty($search)) {

if (stripos($search, 'id:') === 0) { 
$query->where('t.fid = '.(int) substr($search, 3)); 
} else {

$search = $db->Quote('%'.$db->getEscaped($search, true).'%'); 
$search2 = $db->Quote('%'.$db->getEscaped($search2, true).'%'); 
$query->where('( t.name LIKE '.$search.$search2.' )');
}

}


return $query;

but this just gave errors

[objnoob]

change those characters into spaces before saving any titles in the database.

[gerble1000]

to late. there are thousands already in the database

[Love2c0de]

Good evening,

 

Could you not run some SQL command to convert it to what you need?

 

Regards,

 

L2c.

[Barand]

The command you are looking for would be an UPDATE query using MySQL's REPLACE() function. Don't forget to backup the table before running mass updates.

[gerble1000]

is there not a way to do this without altering the tables.

surely its possible to just alter this code to do the same job ?

[objnoob]

There is -- But, it's not the most efficient way to handle this! Every search is having to do a 3 REPLACE()'s on every title to replace those 3 characters into spaces to find any matches.

$query->where("( REPLACE(REPLACE(REPLACE(t.name,'.',' '),'_',' '),'-',' ') LIKE {$search} )");

[gerble1000]

i get this error

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING

[objnoob]

Sorry. I was experiencing tunnel vision.

$query->where("( REPLACE(REPLACE(REPLACE(t.name,'.',' '),'_',' '),'-',' ') LIKE {$search} )");

[gerble1000]

Genius

works perfectly.

could you please explain why i should not use this code.

is it because it puts more pressure on the db

[objnoob]

Yes, each search you do needs to run replace on every title to swap these characters into spaces.

 

If you would save the title with the characters _ . - already replaced with spaces.... you'd be set!

[gerble1000]

ok well thank you for sorting this for me.

how do i mark this thread as answered

[vinny42]

Don't do this. Not only is it a waste of resources, it also prevents the database from using indexes, so the query becomes as slow as it can possibly get.

 

A simple alternative is to create a separate table that holds just the searchable data. Then you can prepare that data once, store it in the new table, and search the new table using a simple "=" or even a LIKE or FULLTEXT. A small trigger can keep the searchtable uptodate when the original table is inserted/deleted or updated upon.

An added bonus of this method is that you can also separately store each word of the text and find texts that do not have an exact match, by counting the number of words that do match.

 

And although some people on this forum will hate me for it, I'm still going to say that in other databases you'd solve this problem using a functional index; an index built on an expression such as the triple-replace mentioned earlier, saving you the trouble of creating the separate table.