SMS retrieve with status

[Muath2000]

Dear Friends,

I'm having a problem for a few days with an sql statement.

 

Idea: I'm trying to retrieve from table (sms_out) a list of mobile numbers. Each number with it's status of sent i.e Failed or Success.

 

The output should be like this:

Mobile No. ------------ Failed--------------Success--------------Total

0171112554                  5                      10                        15

 

My effort until now was this:

This retrieve the mobile number. Then in Query1, it take the mobile number and get the number of FAILED. Query2 will get the number of SUCCESS.

 

$query="SELECT out_phone, count(status), count(*) AS total FROM muath_sms_out GROUP BY out_phone, status";
$row=mysql_fetch_array($result)

			$hp=$row["out_phone"];

			$query1="SELECT count(out_phone) as Failed FROM muath_sms_out where out_phone=$hp status='Failed'";
			$result1=mysql_query($query1);
			$row1=mysql_fetch_array($result1);

			$query2="SELECT count(out_phone) as Success FROM muath_sms_out where out_phone=$hp status='Success'";
			$result2=mysql_query($query2);
			$row2=mysql_fetch_array($result2);

 

I got a warning for this:

$row1=mysql_fetch_array($result1);

And

$row2=mysql_fetch_array($result2);

 

 

Gentlemen, I seek your help as i'm very desperate into solving this problem.

 

MySQL client version: 5.0.51a

 

Thank you.

 

Best Regards,

Muath

[mikosiko]

first...

- you should put this 2 lines (look bottom in my signature) at the beginning of your code to control/display your errors (if you don't have it)

 

next:

- where are you executing your "$query"?... your code seems incomplete

I got a warning for this:

- which warning?....

 

and last... according to what you described as a goal you can obtain your results with just one query... why are you using 3?... hint:  look for the usage of IF clause in your SQL query (CASE also will work)

 

 

 

[Muath2000]

This is my full code:

<?php
@session_start();
error_reporting(E_ALL);
ini_set("display_errors", 1);
require_once("Connections/ernDB.php");
require_once("configchecksession.php");
require_once("top.php");
require_once("level1_check.php");

		$query="SELECT out_phone, count(status), count(*) AS total FROM muath_sms_out GROUP BY out_phone";
		$result=mysql_query($query);
		?>
			<br><br><br><table width="200" border="1">
			  <tr>
			    <td><div align="center">Handphone</div></td>
			    <td><div align="center">Sent SMS</div></td>
			    <td><div align="center">Failed</div></td>
			    <td><div align="center">Success</div></td>
		      </tr><?php

		while($row=mysql_fetch_array($result))
		{
			$hp=$row["out_phone"];

			$query1="SELECT count(out_phone) FROM muath_sms_out where out_phone='$hp' and status='Failed'";
			$result1=mysql_query($query1);
			$row1=mysql_fetch_array($result1);

			$query2="SELECT count(out_phone) FROM muath_sms_out where out_phone='$hp' and status='Success'";
			$result2=mysql_query($query2);
			$row2=mysql_fetch_array($result2);?>
			  <tr>
				<td width="70"><div align="center"><?php echo $row["out_phone"];?></div></td>
				<td width="30"><div align="center"><?php echo $row1["count(out_phone)"]+$row2["count(out_phone)"];?></div></td>
				<td width="30"><div align="center"><?php echo $row1["count(out_phone)"];?></div></td>
				<td width="30"><div align="center"><?php echo $row2["count(out_phone)"];?></div></td>
			  </tr>
		<?php }?>
            	</table>

<?php require("bottom.php"); ?>

 

How to make it in 1 statement?

 

I like your block of code in your signiture. It helps a lot.

 

Thank you for your cooperation and help.

 

Muath

[mikosiko]

...

How to make it in 1 statement?

...

 

maybe you missed this in my previous post

hint:  look for the usage of IF clause in your SQL query (CASE also will work)

 

other hint..... or almost the code

....
SUM(IF(status='Failed',1,0)) AS failed,
SUM(IF(status='Success',1,0)) AS success,
...

[Muath2000]

Got it. Many Thanks for your help.

 

Best Regards,

Muath