cobusbo Posted October 31, 2015 Share Posted October 31, 2015 Hi I'm trying to recall a list of names of people who posted something according to the StringyChat_time field within the hour. The problem is it doesn't select the latest record, but rather the oldest one within the hour how can I select the latest time record? $galleries = array('ADMIN','Moderator','Global Helper','Helper','!!!ANNOUNCEMENT!!!','!!!TOPIC CHANGE!!!'); $sql = "SELECT * FROM StringyChat WHERE StringyChat_time >= (UNIX_TIMESTAMP() - 3600) AND StringyChat_name NOT IN ( '" . implode($galleries, "', '") . "' ) GROUP BY StringyChat_name ORDER BY StringyChat_time DESC LIMIT $offset, $rowsperpage"; $result1 = mysql_query($sql, $db)or die($sql."<br/><br/>".mysql_error()); Quote Link to comment Share on other sites More sharing options...
Barand Posted October 31, 2015 Share Posted October 31, 2015 When you GROUP BY (in this case) name you get ONE row for each name. If a particular name has 100 records with different times it can only display the time from one of the records. The manuals states the choice will be arbitrary but it usually takes the first value from the group - hence the oldest. Quote Link to comment Share on other sites More sharing options...
cobusbo Posted October 31, 2015 Author Share Posted October 31, 2015 When you GROUP BY (in this case) name you get ONE row for each name. If a particular name has 100 records with different times it can only display the time from one of the records. The manuals states the choice will be arbitrary but it usually takes the first value from the group - hence the oldest. So there isn't a way to recall the Unique names and display it according to latest date? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 31, 2015 Share Posted October 31, 2015 Because you use SELECT * there is no way I can know what you really want from the table. You could SELECT name, MAX(date) FROM table GROUP BY name but, as I said, I don't know what else you want Quote Link to comment Share on other sites More sharing options...
cobusbo Posted October 31, 2015 Author Share Posted October 31, 2015 (edited) Because you use SELECT * there is no way I can know what you really want from the table. You could SELECT name, MAX(date) FROM table GROUP BY name but, as I said, I don't know what else you want I'm using a chat but with each post the person make it stores the current date in database in the StringyChat_time field. So What I'm basically trying to do is creating an active unique users list for the last hour sorted according to the latest post made in the chat, but since I'm grouping the Names it select the first date stored by the name and not the last post made <html><head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <meta name="mxit" content="clearscreen" /> </head> <body><br> <? include("chat_code_header.php"); header('Content-Type: text/html; charset=ISO-8859-1'); ////////Users///////// ?><br> <b><u>Chatters</u></b><br> <? // find out how many rows are in the table $galleries = array('ADMIN','Moderator','Global Helper','Helper','!!!ANNOUNCEMENT!!!','!!!TOPIC CHANGE!!!'); $sql = "SELECT COUNT(*) FROM StringyChat WHERE StringyChat_time >= (UNIX_TIMESTAMP() - 3600) AND StringyChat_name NOT IN ( '" . implode($galleries, "', '") . "' ) GROUP BY StringyChat_name"; $result = mysql_query($sql, $db) or trigger_error("SQL", E_USER_ERROR); $r = mysql_fetch_row($result); $numrows = $r[0]; // number of rows to show per page $rowsperpage = 10; // find out total pages $totalpages = ceil($numrows / $rowsperpage); // get the current page or set a default if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { // cast var as int $currentpage = (int) $_GET['currentpage']; } else { // default page num $currentpage = 1; } // end if // if current page is greater than total pages... if ($currentpage > $totalpages) { // set current page to last page $currentpage = $totalpages; } // end if // if current page is less than first page... if ($currentpage < 1) { // set current page to first page $currentpage = 1; } // end if // the offset of the list, based on current page $offset = ($currentpage - 1) * $rowsperpage; $testip = $_SERVER["HTTP_X_MXIT_USERID_R"]; if(!isset($testip)) { $testip = "Debater"; } $galleries = array('ADMIN','Moderator','Global Helper','Helper','!!!ANNOUNCEMENT!!!','!!!TOPIC CHANGE!!!'); $sql = "SELECT StringyChat_name, StringyChat_ip, StringyChat_time FROM StringyChat WHERE StringyChat_time >= (UNIX_TIMESTAMP() - 3600) AND StringyChat_name NOT IN ( '" . implode($galleries, "', '") . "' ) GROUP BY StringyChat_name ORDER BY StringyChat_time DESC LIMIT $offset, $rowsperpage"; $result1 = mysql_query($sql, $db)or die($sql."<br/><br/>".mysql_error()); while($myrow = mysql_fetch_array($result1)) { $ipi = $myrow['StringyChat_ip']; $sqlid = "SELECT * FROM Users2 WHERE mxitid = '$ipi'"; $resultid = mysql_query($sqlid, $db)or die($sqlid."<br/><br/>".mysql_error()); $myrowid = mysql_fetch_array($resultid); $time = time(); $id = $myrowid['ID']; $nname = $myrow['StringyChat_name']; $ip = mysql_real_escape_string($myrow["ip"]); $expire = $myrow["StringyChat_time"]; $dt1 = new DateTime(); $dt1->setTimestamp($expire); // SET TIMESTAMP VALUE (RTFM) $dt2=new DateTime(); // Now $dif = $dt2->diff($dt1); if ($dif->i < 1) { // if more than 1000 years $diff = 'now'; } else { $diff = $dif->format('%m mnths, %d d, %h hrs, %i min, %s secs'); } $banu = '<a href="' . $domain . '/ranklist.php?ban=' . 'demote' . '&nname=' . $nname . '&ip=' . $ipi .'">Demote</a>'; print '<a href="' . $domain . '/pmr.php?id=' . $id . '&ip=' . $ipi . '&nname=' . $nname . '">' . $myrow['StringyChat_name']. '</a>' . ' - ' . $diff . '<br>'; } Edited October 31, 2015 by cobusbo Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted October 31, 2015 Solution Share Posted October 31, 2015 try this SELECT sc.StringyChat_name , sc.StringyChat_ip , sc.StringyChat_time FROM StringyChat sc INNER JOIN ( SELECT StringyChat_name , MAX(StringyChat_time) as StringyChat_time FROM StringyChat GROUP BY StringyChat_name ) latest USING (StringyChat_name, StringyChat_time) WHERE sc.StringyChat_time >= (UNIX_TIMESTAMP() - 3600) AND StringyChat_name NOT IN ( '" . implode($galleries, "', '") . "' ) ORDER BY StringyChat_time DESC LIMIT $offset, $rowsperpage If that gets what you want then you need to JOIN to user2 to get the id's in the same query. (Don't run queries inside loops) 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.