CODEWITHSUNDEEP
phpmysql
Peter
Peter

Reputation: 3495

Pull unique values from mysql table and order by number of occurrences

I'm having a little trouble with this, I found how to get unique values from multiple columns, and I know how to sort normally, but I can't seem to mix the two

The original sql statement is here:

$sql="SELECT DISTINCT UserID, UserIP FROM Visits WHERE PageID='".$_GET['p']."'";

It only gets the first instance of where both values are unique, so doing the 'mysql_numrows' thing doesn't work.

This is from within the fetch array loop, it can count the occurrences fine, but at that point it's a bit too late to sort

$sql5="SELECT Date, VisitID FROM Visits WHERE PageID='".$_GET['p']."' AND UserID='".$userid."' AND UserIP='".$userip."' ORDER BY Date";
$result5 = mysql_query($sql5);
$num5 = mysql_numrows($result5);

Basically, it should get unique values based on both columns, count how many times the appear, sort by count, then potentially also sort by the highest matching date if possible

Any suggestions would be great, cheers :)
Also it's just for a personal site, I'm not actually doing anything with the info, just trying to learn some more php

Upvotes: 0

Views: 66

Answers (2)

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try using GROUP BY, like this:

SELECT UserID, UserIP FROM Visits 
WHERE PageID = ###
GROUP BY UserID, UserIP
ORDER BY COUNT(*) DESC, max(`Date`) DESC

Upvotes: 1

Jigar Chaudhary
Jigar Chaudhary

Reputation: 169

Please change your query like 

$sql = "SELECT UserID, UserIP FROM Visits 
WHERE PageID = '".$_GET['p']."'
GROUP BY UserID, UserIP
ORDER BY COUNT(UserID) DESC";

let me know if helpful to you.

Thanks

Upvotes: 1

Related Questions