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

[diafol]

where out_phone=$hp status='Failed'

You need an AND keyword:

where out_phone=$hp AND status='Failed'

I haven't checked your code thoroughly, but this jumped out.

[Muath2000]

Dear ardav, I was so tired I haven't see this mistake. Thank you so much.

Here is my 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"); ?>

Do you know how to make my code more simpler using 1 statement instead of 3?

Thank you.

[diafol]

Sorry, haven't got time at the moment - will probably be busy for a day or so. Anybody?

[Muath2000]

Thanks man.

[diafol]

OK, back in the game - finished that particular piece of work!
Right, could you please attach your table schema (fields + datatypes).

From a cursory look, I'd say you could:

SELECT out_phone, status, count(out_phone) AS num FROM muath_sms_out GROUP BY out_phone, status ORDER BY out_phone, status

THat should give you simple output (up to 2 records per number). This doesn't sound right, but, I think it's easier than trying to get MySQL to mess around with your data. I suggest using a php loop to extract info from the records, e.g.

$rec = "";
if(mysql_num_rows($rs) > 0){
  while($row = mysql_fetch_array($rs)){
    //build up your html string bit by bit:
    if($rec != $row['out_phone']){
      //this is a new number so start a new line in your table
      //...
      $rec = $row['out_phone'];
    };
    if($row['status'] == 'Failure'){
      //your failure count into the failure string - set to zero initially
    }else{
      //your success count into the success string - set to zero initially
    
    }

  }
}

I'd do something like that, but with a couple of functions - it's a little bare bones, and probably wouldn't work properly, but at least it gives you an idea.