How to show only 1 entry from MYSQL when there are multiples of the Same entry.

[$username]

Hello All,

    I am stuck working on something in PHP.

What I am trying to do is make a History of accounts shown. But I am not sure on how to not list all of the entries that are made when some one goes to the same account multiple times. So what I have done is built a table that logs when some one goes to an account view. But this tracks when they go to the same account many times

 

So I will get this in the tables

        HistoryAccountNumber

        Account1

        Account1

        Account1

 

So in the History it will show all 3 of the same. I only want the one that is more current to be shown.

One thing that I thought I could do is have a date field that gets updated with the date when some one views a account. I dont know how to make the code to exclude the old history.

 

This is my thinking in code so far.

$sql206 = mysql_query("SELECT HistoryAccountNumber, HistoryAccountName, HistoryUserNumber, HistoryDateMOd FROM users WHERE HistoryUserNumber = '$UserVarNum' ORDER BY `HistoryDateMod` DESC LIMIT 0,4 ")
		$numHistory = mysql_num_rows($sql206);
	if ($numHistory => 1){

Past this I dont know what I could do to get exclude the ones that are dubs and only show the latest.

Any thought would help.

 

Thank you,

Brett

 

[Barand]

Use a subquery to pull latest date user accesed each account and match against that

 

$sql206 = mysql_query("SELECT u.HistoryAccountNumber, u.HistoryAccountName, u.HistoryUserNumber, u.HistoryDateMod 
            FROM users u
            INNER JOIN 
                (SELECT HistoryUserNumber, HistoryAccountNumber, MAX(HistoryDateMod) as latest
                    FROM users 
                    GROUP BY HistoryUserNumber, HistoryAccountNumber) as X 
                ON u.HistoryUserNumber = X.HistoryUserNumber 
                    AND u.HistoryAccountNumber = X.HistoryAccountNumber
                    AND u.HistoryDateMod = X.latest
            WHERE HistoryUserNumber = '$UserVarNum' 
            ORDER BY `HistoryDateMod` DESC 
            LIMIT 0,4 ");

[$username]

Thanks Barand, this helps me a lot.

 

Your time is appreciated.

 

Thank you,

Brett