[PHP-DB] adding a space in mysql
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
RE: [PHP-DB] adding a space in mysql
I haven't set up any text-based searches before, but when you say: snip If the user types 'Wash' I don't want any matches for 'Washington', 'Washing', etc. snip Aren't you, in essence, saying that you want to search for Wash? Meaning that your query could just say WHERE name = '$var' (where $var is set to Wash)? According to the MySql manual, the equal comparison operator works on strings and numbers. But maybe text searches work differently than I thought and it will return strings wherever the letter combination wash appears. If that's the case I'm sure somebody will correct me and I'll learn something in the process. -Original Message- From: ron wallace [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 31, 2002 4:32 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] adding a space in mysql 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] adding a space in mysql
Richard, You are correct. For Ron to achieve his goal, the SQL needs to be changed to: WHERE name = '$var' If you use the LIKE operator and have a trailing %, that is telling mySQL to match the first characters exactly and then match ANY trailing characters. If you use the equals operator, then you are guaranteed an exact match. If you are concerned about matching any case, then set both strings to UPPER case then do the compare. Jim Hunter ---Original Message--- From: Hutchins, Richard Date: Wednesday, July 31, 2002 13:48:17 To: 'ron wallace'; [EMAIL PROTECTED] Subject: RE: [PHP-DB] adding a space in mysql I haven't set up any text-based searches before, but when you say: snip If the user types 'Wash' I don't want any matches for 'Washington', 'Washing', etc. snip Aren't you, in essence, saying that you want to search for Wash? Meaning that your query could just say WHERE name = '$var' (where $var is set to Wash)? According to the MySql manual, the equal comparison operator works on strings and numbers. But maybe text searches work differently than I thought and it will return strings wherever the letter combination wash appears. If that's the case I'm sure somebody will correct me and I'll learn something in the process. -Original Message- From: ron wallace [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 31, 2002 4:32 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] adding a space in mysql 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php .
[PHP-DB] adding a space in mysql
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'.
__
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
