PHP/MySQL Retrieve the last action of each user to make a list
i m developing a dynamic website but i have a problem with a query. I m trying to build a map dynamically by retrieving date from the database. I enclose the db structure:
countries (code, name)
data (user_id, dateaction, container)
users (user_id, country_code)
I want to display a list of the AVG container for each country, but the average value should be calculated from last action of each user and the data should be submitted the current day. In the case of non-value of "container" for any country the value is 0. I tried many queries but i couldnt achieve the target. I attach some of the queries i tried.
$query = "SELECT AVG(data.container), data.user_id, users.country_code ".
"FROM data, users ".
"WHERE data.user_id = users.user_id AND dateaction >= '".date('Y-m-d').' 00:00:00'."' AND date < '".date('Y-m-d').' 23:59:59'."'".
"GROUP 开发者_如何学运维BY users.country_code ";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$cid=$row['country_code'];
$queryy=mysql_query("select * FROM Countries WHERE country_code='$cid'") or die("Error Occured,plz try again1");
$rowy = mysql_fetch_array( $queryy );
$cname=$rowy['country_name'];
echo '<area title="'.strtoupper($cname).'" mc_name="'.strtoupper($cid).'"></area>'; }
Please may you help me to solve this logical problem?
$query = "SELECT a.user_id, ifnull(a.country_code, 0) as country_code, avg(a.container) ".
"FROM data a INNER JOIN ".
"(SELECT data.user_id, max(dateaction) dateaction ".
"FROM data".
"WHERE dateaction >= '".date('Y-m-d').' 00:00:00'."' AND date < '".date('Y-m-d').' 23:59:59'."'".
"GROUP BY data.user_id) b ".
"ON a.user_id = b.user_id and a.date_action = b.date_action ".
"GROUP BY a.user_id, ifnull(a.country_code, 0) "
精彩评论