Page 1 of 1

Get DB-date, ignore Ucase and Lcase

Posted: Wed Mar 05, 2003 8:14 am
by Caliber
I've got this query:

їcode]$query2 = pg_exec($conn, "
SELECT
*
FROM
mtrade_forum
WHERE
threadcontent LIKE '%$searchstring%'
OR
threadtopic LIKE '%$searchstring%'
ORDER BY
createddate DESC
");
ї/code]

The problem is that it only shows results that contains words that match EXACTLY what is in $searchstring. If I search for "Test" I only get records that contain "Test".
I want it to ignore UpperCase and LowerCase, so If I search for "Test" I'll get "test", "TEST", "tEsT" and so on.

How do I do that?
Thanx in advance.

Posted: Wed Mar 05, 2003 8:15 am
by Caliber
I'm using postgreSQL btw

Posted: Wed Mar 05, 2003 3:27 pm
by McGruff
Haven't used postgreSQL so I don't know if this will help. Might give you some ideas of what to look for.

In mysql, the BINARY attribute on a column specifies a case-sensitive match - leave it off for case insensitive.

Also (from the mysql manual):

"The BINARY attribute is sticky. This means that if a column marked BINARY is used in an expression, the whole expression is compared as a BINARY value. "

Posted: Wed Mar 05, 2003 4:26 pm
by Rob the R
You could also use the strtoupper PHP function in combination with the upper PostgreSQL function to convert all the cases to upper-case when searching:

Code: Select all

$uppersearchstring = strtoupper($searchstring) ;
$query2 = pg_exec($conn, " 
                           SELECT 
                              * 
                           FROM 
                              mtrade_forum 
                           WHERE 
                              upper(threadcontent) LIKE '%$uppersearchstring%' 
                           OR 
                              upper(threadtopic) LIKE '%$uppersearchstring%' 
                           ORDER BY 
                              createddate DESC 
                        ");
This may slow things down if you have a large database to search through, since it would have to convert every record to upper-case to see if it matched.

Posted: Fri Mar 07, 2003 8:13 am
by Caliber
Thanx a lot for the help guys, it works now