PHP“数组查询”/多个查询

Question

I have a mysql table where I like to perform a query on. My table looks as following:

    date        activity    amount 
    --------    ------      -------- 
    day 1       drink       0 
    day 1       eat         1 
    day 1       breath      1 
    day 2       drink       0 
    day 2       eat         0 
    day 2       breath      0 
    day 3       drink       1 
    day 3       breath      0 
    day 4       eat         1 
    etc          
    etc          
    etc          

What i'd like to do is to see when eat is 1, and for the days that is the fact, i'd like to display all activities for those days

//What I was doing right now is:

$activityarray = array();



$result = mysql_query("SELECT * FROM table WHERE activity='eat' AND amount='1'");
$row = mysql_fetch_assoc($result);

//this returns all rows where activity=eat and amount=1


do{
     //perform for each result row a new query; look for the 'date'=$row[date] from the first query and show all activities that have been done that day (activity=1)

     $result2 = mysql_query("SELECT * FROM table WHERE date='".$row[date]."'");
     $row2 = mysql_fetch_assoc($result2);

     do{
         array_push($activityarray,$row2['activity']);
     }while($row2 = mysql_fetch_assoc($result2));

}while($row = mysql_fetch_assoc($result));


print_r($activityarray);

Since there are thousands of days and dozens of activities per day, this seems not to be the most efficient method to me. Is there a way I can do this more efficient with one query? (So: check all activities for the days that eat=1). Hope anyone could me me out!

Answer 1

Use a self-join:

SELECT t1.*
FROM table AS t1
JOIN table AS t2 ON t1.date = t2.date
WHERE t2.activity = 'eat' AND t2.amount = 1