show result with count query

[deepson2]

Hello,

 

I have following two tables

 

report_story

 

id  story_id  story_name  userid   

1    12        abc        9       

1    23        xdf        7       

1    45        dfdf        9       

1    12        abc        15   

 

user_details

 

id  userid    name

1      9    meera

2      13    Geeta

3      15    rahul

 

now i want to show my result somthing like this

 

Story name : abc

repoted users: meera,rahul

 

 

I don't want to show unnecessarily two rows here.

 

So i was trying the following queries which are not working

 

 

select *,count(*)as cnt from user_details a JOIN report_story r ON a.id = r.userid GROUP BY r.id ORDER BY r.id DESC LIMIT $start, $limit_value");

if(mysql_num_rows($sqlpage) > 0){ ?>
<tr>
  <td width="100%" align="left"><h1 class="pageTitle">Story Report</h1>
  <p>Total <b><?=$total_records;?></b>listing</p>
  </td>
</tr>
<tr>
<td width="100%">

  	 <?  while($row = $op->select($sqlpage)){

      ?>
        

     <tr bgcolor="#FEF9E6">
         <tr bgcolor="#FEF9E6"> <td width="25%" class="loginText">Title </td>
		       <td width="75%" class="Text"><a href="<?=$row['story_name'];?>" title="<?=stripslashes($row['story_name']);?>" ><?=stripslashes($row['story_name']);?></a></td>
       </tr>

      </tr>
      
      <tr bgcolor="#FDFAEE"> <?
      echo "SELECT a.username, r. * FROM user_details a JOIN story_report r ON a.id = r.id WHERE r.id='".$row['id']."'";
     	        $sqlrpt = $op->runsql("SELECT a.username, r. * FROM user_details a JOIN story_report r ON a.id = r.id WHERE r.id='".$row['id']."'");

    if(mysql_num_rows($sqlrpt) > 0){
    while($row1 = $op->select($sqlrpt)){
   $reportedby.= $row1['username'].", ";
    }
    }	?>
      
      <tr bgcolor="#FEF9E6">
  	  <td width="25%" class="loginText">Reported by</td> <? $reportedby =substr($reportedby, 0, -2); ?>
  	  <td width="75%" class="blogText" valign="top"><?=$reportedby; $reportedby="";?></td>
  </tr>
      <tr>
      <td colspan="3" width="100%"> </td>
      </tr></table>
       </div>
      <?
       } }?>
</td>
</tr>

 

Can anyone please see this and tell me how can i achieve the exact result?

 

Thanks in advance.

 

[deepson2]

Any help??

[rajivgonsalves]

try this

 

select story_name, GROUP_CONCAT(a.name) from user_details a JOIN report_story r ON a.userid = r.userid group by story_name ORDER BY r.userid

[deepson2]

Thnaks a lot for your reply

 

perfect query rajiv,

 

now i want count as well for "GROUP_CONCAT(a.name)" so i can show  like 2 people have recommended this story. so how can i put that in to it?

[rajivgonsalves]

that depends on your data in the database what is the structure for recommendations

[deepson2]

Got the count as well!!

see the query-

 

 $sqlpage = $op->runsql("select *,count(*) as cnt  story_name, GROUP_CONCAT(a.name) from user_details a JOIN report_story r ON a.userid = r.userid group by story_name ORDER BY r.userid DESC LIMIT $start, $limit_value");

 

GROUP_CONCAT is really new Aggregate function  for me.

 

Once again thanks a lot rajiv.

 

Marry Christmas!

[rajivgonsalves]

Your welcome and Merry Christmas

 

Glad to be of help