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.
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 {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));
-------------------------------------------------
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.