[PHP-DB] Re: adding a space in mysql
Ron Wallace wrote:
> thanks, helpers, but I think I have a simple solution:
> I will insert a space at the front of the string,
> where the db must accept and retain it.
>
> BTW, here is the SQL I am working with:
> //
> elseif ($desc==desc) {
> $sql="SELECT bandid, bandname, bandesc, bin_id,
> genre
> FROM bands, genre WHERE
> (bands.genreid=genre.genreid) AND ";
>
> while (list($key, $word)=each($wordsarray))
> //$wordsarray from textbox input
> {
> $sql=$sql."(bandname LIKE '$word %' OR
> bandname LIKE '% $word %' OR
> bandname LIKE '% $word' OR
> bandesc LIKE '$word%' OR
> bandesc LIKE '% $word %' OR
> bandesc LIKE '% $word' OR
> genre LIKE '$word%' OR
> genre LIKE '% $word') AND ";
> }
> $sql=substr($sql, 0, (strlen($sql)-5));
> $sql=$sql." ORDER BY genre.genre ASC, bandname ASC";
>}
> ///
> If I do "bandname='$var' I would not hit the band 'Red
> Letter Day' if the user jusr types 'red'.
Howerver if bandname was 'My name is Fred'
you will get a hit, because it is LIKE '$word%'
where Fred ends with 'red';
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: adding a space in mysql
I think the other replies to this are missing the point. You want to search a text field for the word Wash, and want to put the space in so you can search with the space, eliminating words which begin with wash. But then, if you think about it, that means you'd have to search for commas, and full stops. I don't think this is such a good way to do it. Try using regular expressions. I have never used regex on a db before, but I have seen a few people mention it. Have a go and if you find a solution, post it. Adam --- Original Message --- How can I add a space to the end of a varchar string in mysql? I can't seem to do it directly, and I tried str_pad and other things, but no luck so far. The db doesnt hold the value of the space...its getting trimmed somewhere(?) The reason I want to do this is so I can search for exact matches on one word strings without stemming: ...WHERE name LIKE '$var %'. If the user types 'Wash' I don't want any matches for 'Washington', 'Washing', etc. Thank You All
[PHP-DB] Re: adding a space in mysql
Ron
I do believe that placing a space in '%$var %' will do the same as a
varchar and sprip it off the end. Not positive. However I believe
something like this will do the trick:
SELECT something
FROM a_file
WHERE replace(CONCAT_WS('','.',a_file.field,'.'),' ','.') LIKE '%.$var.%'
HTH... Dan
Ron Wallace wrote:
> How can I add a space to the end of a varchar string
> in mysql? I can't seem to do it directly, and I tried
> str_pad and other things, but no luck so far. The db
> doesnt hold the value of the space...its getting
> trimmed somewhere(?)
>
> The reason I want to do this is so I can search for
> exact matches on one word strings without stemming:
> ...WHERE name LIKE '$var %'.
>
> If the user types 'Wash' I don't want any matches for
> 'Washington', 'Washing', etc.
>
> Thank You All
>
> __
> Do You Yahoo!?
> Yahoo! Health - Feel better, live better
> http://health.yahoo.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
