<div dir="ltr">I must be misunderstanding the issue .. I understood the OP's use of mysql_* as simply being for demonstrative purposes to show the difference in the returned results, and the actual question being why does db_query return a result of 0 for COUNT(*) when mysql_query returns a result of 4 for COUNT(*). I'd also like to know what the actual string value of $countsql is, and like Michael said, whether the query is being rewritten by another module.<br>
<br><div class="gmail_quote">On Mon, Sep 22, 2008 at 6:04 PM, Larry Garfield <span dir="ltr"><<a href="mailto:larry@garfieldtech.com">larry@garfieldtech.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
<a href="http://api.drupal.org/api/function/db_fetch_array/5" target="_blank">http://api.drupal.org/api/function/db_fetch_array/5</a><br>
<br>
Drupal's db_fetch_array functions use *_fetch_assoc() internally. In ext/mysql and ext/pgsql, *_fetch_array() returns a doubly indexed array by field AND number. *_fetch_assoc() returns just the associative array. I have frankly never seen a reason to want the doubly indexed version, so it's good that Drupal gives you the version that makes sense. :-)<br>
<br>
You should be opposed to using mysql_* directly, as that is the Wrong Way(tm) to access the database. That bypasses all of the Drupal security handling and is an SQL Injection risk.<br>
<br>
In this case, in fact, you probably don't want a fetch at all. You just want the single count value.<br>
<br>
$count = db_result(db_query($countsql));<br>
<br>
And you're done.<br>
<font color="#888888"><br>
--Larry Garfield<br>
</font><div><div></div><div class="Wj3C7c"><br>
On Mon, 22 Sep 2008 13:06:14 +0200, "Fred Jones" <<a href="mailto:fredthejonester@gmail.com">fredthejonester@gmail.com</a>> wrote:<br>
> Here is some trivial code:<br>
><br>
> $n= db_query($countsql) ;<br>
> echo "<!-- ";print_r(db_fetch_array($n)); echo " -->";<br>
> $countsql = str_replace('{','',$countsql);<br>
> $countsql = str_replace('}','',$countsql);<br>
> $resultXXX = mysql_query($countsql);<br>
> echo "<!-- ";print_r(mysql_fetch_array($resultXXX)); echo " -->";<br>
><br>
> which runs my $countsql through db_query, shows the result and then<br>
> through mysql_query and shows the results. The two other lines just<br>
> remove the { and } characters. I would have expected to see the same<br>
> results both times, but what I see is:<br>
><br>
> <!-- Array<br>
> (<br>
> [COUNT(*)] => 0<br>
> )<br>
> --><!-- Array<br>
> (<br>
> [0] => 4<br>
> [COUNT(*)] => 4<br>
> )<br>
> --><br>
><br>
> The mysql_query version returns the correct result--that is what I see<br>
> if I run the query via phpMyAdmin as well. Seems that Drupal's<br>
> db_query is doing something to it. The operative issue is the use of<br>
> UCASE I am quite certain. I wanted to make my LIKE phrase case<br>
> insensitive, so I added UCASE to both sides of it, but now db_query<br>
> fails.<br>
><br>
> Anyone have any feedback on this? I would not be opposed to just using<br>
> mysql_query but the query in question I actually want to pass to<br>
> pager_query, which in turn of course passes it to db_query.<br>
><br>
> Thanks.<br>
> --<br>
> [ Drupal support list | <a href="http://lists.drupal.org/" target="_blank">http://lists.drupal.org/</a> ]<br>
<br>
--<br>
[ Drupal support list | <a href="http://lists.drupal.org/" target="_blank">http://lists.drupal.org/</a> ]<br>
</div></div></blockquote></div><br></div>