SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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->create());
    while($rowPgr = mysql_fetch_assoc($rst)) {

    $SqlSub ="SELECT col5 FROM tbldetails
    WHERE ccode = '$StrCode'
    AND pagerno = '$rowPgr[pagerno]'
    AND col1 <> 'invalid'
    AND col6 <= '$strDte'
    AND edate <= '$strDte'
    order by detailid desc limit 0,1";

    $rstSub = mysql_query($SqlSub, $Obj->create());
    while($rowCol = mysql_fetch_assoc($rstSub)) {
    if ($rowCol['col5']=="1"){
    $Active+=1;
    }
    }
    mysql_free_result($rstSub);

    }
    mysql_free_result($rst);
    mysql_close($Obj->create());

  2. #2
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,677
    Mentioned
    99 Post(s)
    Tagged
    4 Thread(s)
    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.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  3. #3
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,804
    Mentioned
    157 Post(s)
    Tagged
    3 Thread(s)
    Thread moved to MySql Forum
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,993
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    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?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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;

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,993
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Are you storing multiple numbers separated by commas in the same field?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no mr.space, not comma seperated. 0 = active, 1=terminate etc

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,491
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •