A Select Problem to Solve

[wrightee]

Hi again; I'm hoping someone wiser than me in MySQL will shed some light on how to do this:

tbl_comments might look like this:

id,user,post_id,dated
1,1,500,2006-01-01
2,1,700,2006-01-02
3,3,500,2006-01-03
4,2,600,2006-01-03

I want to know how many 'first posts' each user has made, to produce a result, say, like this:

user, first_posts
1, 2
3, 0
2, 1

.. in other words, user 1 has made the first comment on a post twice, user 3 never got their first and user 2 got in first once.

The real table is around 50K rows, so not really suited to script processing.

Any brain cycles you can donate will be much appreciated!

[syber]

This should work

 SELECT user, COUNT(*) 
 FROM tbl_comments  
 WHERE user IN (SELECT user 
     FROM tbl_comments AS t2 
     WHERE dated = (SELECT MIN(dated)  
      FROM tbl_comments AS t3 
      WHERE post_id = t2.post_id)) 
 GROUP BY user 
  

[wrightee]

Thanks for the reply. It didn't work unfortunately (ran endlessly on MySQL 4.1, poss due to a bug with subselects) but I appreciate your time. Should give me enough clues to figure out a direction :)

[95connell]

here is a subselect function that might work for you

<?
#
#subselect function for mysql
#

#
# integer subselect (string query , integer active_link)
#

#
#subselect sends the sub query to the specified database then builds a delimited list.
#next the entire query (with the list substitued for the sub-select is sent to the database and the
# resulting query identifier is returned
#

#
#function call example:
#
# $custname = subselect($qs,$link);
#

function subselect($entirequery,$link){

#
#force lower case on word "select"
#

$entirequery = eregi_replace("select","select",$entirequery);

#
#remove line feeds and cairage returns
#

$entirequery = eregi_replace(10,' ',$entirequery);
$entirequery = eregi_replace(13,' ',$entirequery);

#
#build array split on string "select"
#

$entirequery = explode("select ",$entirequery);

#
#count num of elements on array
#
$count_entirequery = count($entirequery);

if ($count_entirequery!= 2):

$last_select = ($count_entirequery - 1);
$ii=2;
while ($ii<$count_entirequery):

#
#get sub-select string begining at 2nd word
#
$query1 = split(" ",$entirequery[$last_select],2);

#
#find column name in sub select
#
if ($query1[0] == "distinct"):
$query1 = split(" ",$query1[1],2);
# echo "<br><br><b>1: $query1[0]<br><br>";
# echo "<br><br><b>2: $query1[1]<br><br>";
$colname = $query1[0];
$query1 = ("select distinct ".$query1[0]." ".$query1[1]);
else:
$colname = $query1[0];
$query1 = ("select ".$query1[0]." ".$query1[1]);
endif;

#
#trim right paren
#
$query1_len = strlen($query1);
$query1 = substr($query1,0,($query_len - 1));

#
#run sub select
#
#echo "<br>SUB: $query1 <br>";

if(eregi('\(',$query1) and eregi('\)',$query1)):

else:
$query1 = eregi_replace('\)','',$query1);
$query1 = eregi_replace('\(','',$query1);
endif;

#echo "QUERY: $query1<br>";

$subselect_qry = mysql_query($query1,$link);

#
#count number of items returned from sub-select
#

$subselect_rows = @mysql_num_rows($subselect_qry);

#
#get 1st item
#
$subselect_res = @mysql_result($subselect_qry,0,$colname);

if(!is_numeric($subselect_qry)):
$subselect_res = "'" . $subselect_res . "'";
endif;

if ($subselect_rows!= 1): #if more than one item build list
$i=1;

while ($i<$subselect_rows):
$res = mysql_result($subselect_qry,$i,$colname);

if(!is_numeric($res)):
$res="'" . $res . "'";
endif;

$subselect_res = ($subselect_res.",".$res);

$i++;
endwhile;
endif;

$last_select = ($last_select - 1);
$entirequery[$last_select] = ($entirequery[$last_select].$subselect_res.")");
$ii++;
endwhile;

#
#run select with list substitued for sub-select
#

$query2 = ("select ".$entirequery[$last_select]);

else:

$query2 = ("select ".$entirequery[1]) ;

endif;

#
#pass query identifier
#

#echo $query2;

return $query_identifier = mysql_query($query2,$link);

}
?>