db_query and UCASE in Drupal 5
Here is some trivial code:
$n= db_query($countsql) ;
echo "<!-- ";print_r(db_fetch_array($n)); echo " -->";
$countsql = str_replace('{','',$countsql);
$countsql = str_replace('}','',$countsql);
$resultXXX = mysql_query($countsql);
echo "<!-- ";print_r(mysql_fetch_array($resultXXX)); echo " -->";which runs my $countsql through db_query, shows the result and then through mysql_query and shows the results. The two other lines just remove the { and } characters. I would have expected to see the same results both times, but what I see is:
<!-- Array
(
[COUNT(*)] => 0
)
--><!-- Array
(
[0] => 4
[COUNT(*)] => 4
)
-->The mysql_query version returns the correct result--that is what I see if I run the query via phpMyAdmin as well. Seems that Drupal's db_query is doing something to it. The operative issue is the use of UCASE I am quite certain. I wanted to make my LIKE phrase case insensitive, so I added UCASE to both sides of it, but now db_query fails. Using LCASE or LOWER gives the same results.
Anyone have any feedback on this? I would not be opposed to just using mysql_query but the query in question I actually want to pass to pager_query, which in turn of course passes it to db_query.
Thanks.
The other option appears to
The other option appears to be to change the collation of the table(s)
in question. According to this:
http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
certain collations are automatically case insensitive. I had initially
hesitated to make such a change to the schema, but it appears to be an
option if my current approach fails.