Page 1 of 1

problem with my query. please help asap

Posted: Sat Dec 09, 2006 12:40 am
by phpchild
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


hi every one.


        now i have stuggle with my query. can anyone please help me??


    Problem is if any user send invite mail to any new member , we have to count how many invite mails he sent and how many new persons registered.
Then finally it has to show the toppers based on registed count in a descending order.

    for that i used group by clauses to my query. i could count and show the mail sent count and registered count. but i counldn't display the topper list in decending order. please kindy help me.

database table.

Code: Select all

scm_mem_id	sih_invited_email	invite_frend_flag	frnd_signup_flag	
				
137	                 c1@c.com	           1	                       0	
138	                 d1@d.com	           1	                       0	
139	                 y166@y.com	            1	          0	
140	                  q1@q.com	              1	          0	
141	                 z33@z.com	              1	0	
142	                  f1@g.com	                1	0	
172	                  balaguru@g-antssoft.com  0	0	
172	                   Test@test.com	    0	1
output

Code: Select all

Inviter Name    Inviter Mail Id     Number of Invites Sent  Number of Joins  
````````````````````````````````````````````````````````````
 
sujipriya          s@s.com                     3                              0 


 
vidya            vidya@soft.com              1                             10 


 
priya            ss@dsoft.com                   1                             11 


coding
``````

Code: Select all

<? 
		echo  $sql_invite="SELECT scm_mem_id, sih_invited_email, invite_frend_flag, frnd_signup_flag, count( scm_mem_id ) 
FROM scm_invite_history
GROUP BY scm_mem_id 
 ";
			$QueryResult=$db->select_data($sql_invite);
			$total=count($QueryResult);
			
			if ($total > 0)
			{ 
				
				 
				//$QueryResult = $QueryResult["resultElements"]; 
			
				for ($i = 0; $i < count($QueryResult); $i++)
				{ 
					$element     = $QueryResult[$i];
					$mem_id		=$element[0];
					$count=$element[4];
					 $sql_mem="select * from sc_member where scm_mem_id='$mem_id'";
					$QueryResult_mem=$db->select_data($sql_mem);
					$first_name=$QueryResult_mem[0]['scm_firstname'];
					$last_name=$QueryResult_mem[0]['scm_lastname'];
					$name=$first_name.'&nbsp;'.$last_name;
					$mailid=$QueryResult_mem[0]['scm_email'];
					$total=count($element['scm_mem_id']);
					
					 $sql_invite_joined="SELECT *
										FROM `scm_invite_history` 
										WHERE frnd_signup_flag =1 and scm_mem_id='$mem_id'  ";
										
					$QueryResult_joined=$db->select_data($sql_invite_joined);
					$joined=count($QueryResult_joined);
					
					
					
					
					?>
					
				<tr>
				<td><br><br></td>
				</tr>
			
		    <tr bgcolor="#FBE5F1">
			
            <td align="center" class="style"><?=$name;?></td>
			 <td align="center" class="style"><?=$mailid;?></td>
			 <td align="center" class="style" ><?=$count;?></td>
			<td align="center" class="style"><?=$joined;?></td> 			
            </tr>
			<? }
		
		}?>

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Sat Dec 09, 2006 1:31 am
by volka
Just give your count() field a name (count(...) as xyz) and add an ORDER BY with this name/alias.