Re: [support] using drupal in mysql without subquery

4 Oct 2006


      larry white wrote:
...
Drupal Indonesia wrote:
...
In Drupal installation that using MySQL without subquery support, what is the 
right sql statement for this query?
------------------------------------------------------------------
SELECT count(u.name)
FROM {users} u
WHERE u.uid IN (SELECT uid from {node} n where type='blog')
------------------------------------------------------------------
Above query is show number of users who whas written Blog.
-------------------------------------------------
$inner_query="SELECT uid from {node} n where type='blog'";
$blog_uids_res=db_query($inner_query);
while ($blog_uids=db_fetch_object($blog_uids_res)) {
  $blog_uids_array[]=$blog_uids->uid;
}
$blog_uids_where_str=implode(',',$blog_uids_array);
$outer_query="SELECT count(u.name) FROM {users} u WHERE u.uid IN
".$blog_uids_where_str;
$blog_user_count=db_result(db_query($outer_query));
doh.  slight correction.
the where condition for 'IN' must be inside parentheses.
a couple of ways to do this (one or the other, not both):

1). change:

$blog_uids_where_str=implode(',',$blog_uids_array);

to

$blog_uids_where_str='('.implode(',',$blog_uids_array).')';


or


2). change:

$outer_query="SELECT count(u.name) 
              FROM {users} u 
              WHERE u.uid IN ".$blog_uids_where_str;

to

$outer_query="SELECT count(u.name) 
              FROM {users} u 
              WHERE u.uid IN (".$blog_uids_where_str.")";


-- larz