Php, Mysql. Is it possible to make this as in one SQL query?

//*********************************
$Active=0;
$Sql =“SELECT distinct pagerno FROM tbldetails
WHERE ccode = ‘$StrCode’
AND col1 <> ‘invalid’
AND col6 <= ‘$strDte’
AND edate <= ‘$strDte’”;

$rst = mysql_query($Sql, $Obj-&gt;create());
while($rowPgr = mysql_fetch_assoc($rst)) {	
	
	$SqlSub ="SELECT col5 FROM tbldetails 
			WHERE ccode = '$StrCode'
			AND pagerno = '$rowPgr[pagerno]'
			AND col1 &lt;&gt; 'invalid'
			AND col6 &lt;= '$strDte'
			AND edate &lt;= '$strDte'
			order by detailid desc limit 0,1";
	
	$rstSub = mysql_query($SqlSub, $Obj-&gt;create());
	while($rowCol = mysql_fetch_assoc($rstSub)) {	
		if ($rowCol['col5']=="1"){
			$Active+=1;
		}
	}
	mysql_free_result($rstSub);
		
}
mysql_free_result($rst);	
mysql_close($Obj-&gt;create());

An inner join would be one way to do it, but really you need to be asking in the MySQL Forum how to combine them because an inner join is likely to be an expensive way to do it, compared with other ways that more expert techniques that are available.

Thread moved to MySql Forum

I’ve asked the mods to move this thread to the databases form. In the mean time stephenvs can you post a “Create Table” for the tbldetails table and what you want get from the table?

Following is the table structure. In the column name col5, i store some flags to such as 0,1,2,3. i need to retrieve count of each flags.

Scenario: Every customer using one or more pagers. every pagers has a own history. i need to summarize every customer active pagers and inactive pagers.

CREATE TABLE IF NOT EXISTS tbldetails (
detailid varchar(30) default NULL,
ccode varchar(25) default NULL,
pagerno varchar(10) default NULL,
months varchar(10) default NULL,
price varchar(10) default NULL,
disc varchar(10) default NULL,
tprice varchar(10) default NULL,
sdate date default NULL,
edate date default NULL,
status varchar(10) default NULL,
cdtby varchar(60) default NULL,
mdyfy varchar(60) default NULL,
remarks text,
col1 varchar(10) default NULL,
col2 varchar(1) default NULL,
col3 varchar(10) default NULL,
col4 datetime default NULL,
col5 varchar(20) default NULL,
col6 datetime default NULL,
col7 varchar(20) default NULL,
col8 varchar(20) default NULL,
col9 varchar(20) default NULL,
KEY t_idx (detailid),
KEY t_id (ccode,col1,col6,pagerno,col5)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Are you storing multiple numbers separated by commas in the same field?

no mr.space, not comma seperated. 0 = active, 1=terminate etc


SELECT 
    t1.col5,
    COUNT(*) AS qty
FROM tbldetails AS t1
INNER JOIN 
  (SELECT 
       pagerno
     , MAX(detailid) AS maxdetailid
   FROM tbldetails
   WHERE ccode = '$StrCode'
   AND col1 <> 'invalid'
   AND col6 <= '$strDte'
   AND edate <= '$strDte'        
   GROUP BY pagerno  
  ) AS t2
ON  t1.pagerno = t2.pagerno
AND t1.detailid = t2.maxdetailid
GROUP BY t1.col5