using drupal in mysql without subquery

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  u
> WHERE u.uid IN (SELECT uid from  n where
type='blog')
>
------------------------------------------------------------
------
> Above query is show number of users who whas written
Blog. 
>
>   

sorry, if this ends up being a duplicate message.
not sure what happened when i tried to submit it to the list
earlier
today, but it did not appear to added to the thread.



been a while since i've done this type of thing.
the approach i have used in the past involves a multi-step
process:

1) run subquery as a separate query first
2) loop through results of separate query and place these
values into an
array
3) use 'implode' to convert the array to a string, with each
value
separated by a comma ','.
4) use the string containing the values as a replacement for
the subquery

-------------------------------------------------

$inner_query="SELECT uid from  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  u WHERE
u.uid IN
".$blog_uids_where_str;

$blog_user_count=db_result(db_query($outer_query));

-------------------------------------------------




btw, step 2 is drupal-specific. i would prefer to put these
values into
an array immediately, as opposed to looping through
individual results.
perhaps i've missed something along the way, but is there a
way in
drupal to pull out the entire result set as an array.
would be useful in a situation like this, where you're only
grabbing
data from one column.
would be nice to have it all in an array to implode
immediately, instead
of having to loop through it.
for those familiar with php 'pear' db extension, think the
'getCol()' 
function.

-- 
[ Drupal support list | http://lists.drupal.org/
]

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  u
>> WHERE u.uid IN (SELECT uid from  n where
type='blog')
>>
------------------------------------------------------------
------
>> Above query is show number of users who whas
written Blog. 
>>
>>   
>>     
>
> -------------------------------------------------
>
> $inner_query="SELECT uid from  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  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  u 
              WHERE u.uid IN ".$blog_uids_where_str;

to

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



-- larz

-- 
[ Drupal support list | http://lists.drupal.org/
]