Jump to content

Mysql select latest record.


cobusbo
Go to solution Solved by Barand,

Recommended Posts

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());

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by cobusbo
Link to comment
Share on other sites

  • Solution

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)

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.