Q:

what to add in where clause to get the desired result? 

I am trying to get max(date) ,count(id) and *look_name for max(date)* from this table:
id | member_id | look_week | look_name            | is_pinned | date 

1  |   1       |   3       | the improviser       | yes       | 2013-11-19 21:57:04
2  |   1       |   2       | destined for stardom | yes       | 2013-11-19 21:56:00
2  |   1       |   1       | fashinably corporate | no        | 2013-11-19 21:54:00

This is my query:-
$sql="SELECT COUNT(id) as total_pins,MAX(pinned_date) as last_activity_date FROM pin_info WHERE member_id='1' AND is_pinned='yes'";

I am getting this array as out put. 
//[total_pins] => 2
//[last_activity_date] => 2013-11-19 21:57:04
//[lookname_for_last_date] => i am stuck at this? 

How can I manipulate this query so that i can get look_name for max(date) in this array? 

A:

Step 1: First, you have to select the MAX(pinned_date).  I assume this is for the one particular member and is_pinned='yes'
Step 2: Then, you have to select the look_name where the pinned_date is equal to the max-date found above.  You can do this by making Step 1 and inner query inside Step 2
Step 3: Finally, the query from Step 2 goes as the third column in your main query
    SELECT COUNT(*) as total_pins, MAX(pinned_date) as last_activity_date,
     (select look_name 
      from pin_info B 
      where A.member_id=B.member_id and A.is_pinned=B.is_pinned
        and pinned_date in (
         select max(pinned_date) 
         from pin_info C 
         where B.member_id=C.member_id and B.is_pinned=C.is_pinned
        ) AS lookname_for_last_date
     ),
       (
         select max(pinned_date) 
         from pin_info C 
         where A.member_id=C.member_id and A.is_pinned=C.is_pinned
        ) AS CHK_LAST_DATE

    FROM pin_info A
    WHERE member_id='1' 
    AND is_pinned='yes'

To check this, or to tweak it, see this SQL Fiddle here. 
most of it is similar..This gives me the exact result. 
 $sub_query="SELECT MAX(pinned_date) FROM pin_info WHERE member_id='$member_id' AND is_pinned='yes'";

$sql = "SELECT COUNT(*) as total_pins, MAX(pinned_date) as last_activity_date,(SELECT look_name FROM pin_info WHERE member_id='$member_id' AND pinned_date=($sub_query)) as last_pinned_look FROM pin_info WHERE member_id='$member_id' AND is_pinned='yes'";

