Daily Archives: July 12, 2012

Group By Status

Posted on by | Leave a reply

I have Table with Columns Name, EmailId, Process, Status Like Below. Now what i would like to have is the emailId and Status of people whoes Status are closed for all the Process – Process1, process2, process 3

Input
 

Output

Query1

  SELECT  fruit_email_id
    FROM  Fruits AS t
   GROUP BY fruit_email_id
  HAVING COUNT( CASE WHEN Status  'Closed' 
                     THEN 1                       
                      END) = 0;

Query2

   SELECT  t.fruit_email_id
     FROM  (SELECT DISTINCT fruit_email_id
              FROM Fruits) AS t
              LEFT JOIN Fruits AS tt ON  tt.fruit_email_id = t.fruit_email_id AND 
                   tt.Status  'Closed'
    WHERE tt.fruit_email_id IS NULL;
Posted in SQL.

Creating JSON File With PHP

Posted on by | Leave a reply 
    mysql_connect('localhost', 'root', '');
    mysql_select_db('sampleDB');
    
    $strSQL = 'SELECT * 
                 FROM tbAmenities 
                LIMIT 10';
                         
    $Result = mysql_query($strSQL); 

    $Response = array();
    $Posts    = array();
    
    while($row=mysql_fetch_array($Result)) 
    { 
        $id             =    $row['id']; 
        $amenityname    = $row['amenityname']; 
        
        $amenity[] = array('id'=> $id, 'amenity'=> $amenityname);    
    } 

    $Response['posts'] = $amenity;

    $fp = fopen('results.json', 'w');
    fwrite($fp, json_encode($Response));
    fclose($fp);