Use of distinct function

Hello all,
below is my code which fetches the information of recent visits for profile. Here the problem is that if X has visited your profile 5 times day then it will show it five times. I tried using Distinct but in that case it is showing date is not defined. I want that it only shows a particular user once in 24hrs with date. After 24hr, if he sees again then will be displayed.

<?php
$visitormessage=" ";
$sql="SELECT * FROM visitors WHERE username='$log_username'  ORDER BY date ASC " ;
	$query = mysqli_query($db_conx, $sql);
	$numrows = mysqli_num_rows($query);
    if ($numrows > 0 ) {
 while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
	$visitor = $row["visitor"];
	$date = $row["date"];
	  $date = date_create($date);
	  $datelog=date_format($date, 'd/m/y');

}
        $visitormessage .= '<div style="float:left; margin-left:3%; width:200px; margin-top:1%; margin-bottom:1%; border:1px solid silver; overflow:hidden;background-color:#D1D1D1;  ">'.$visitor1.' on '.$datelog.'</div>';
}
	}
	else
	    $visitormessage = '<div class="sty">You dont have any recent visitors</div>'  ;

?>

EDIT: Maybe you need a GROUP BY visitor

SELECT *
FROM visitors
WHERE username='$log_username'
GROUP BY visitor
ORDER BY `date` ASC 

$sql="SELECT * FROM visitors WHERE username=‘$log_username’ ORDER BY date ASC " ;

i know it’s not actually related to the question but you should use filtering and preparing for a query params

such as for example:

$stmt = mysqli_prepare($link, "SELECT District FROM City WHERE Name=?")
mysqli_stmt_bind_param($stmt, "s", $city);
mysqli_stmt_execute($stmt);

I’m not entirely sure but I think that if you want to use DISTINCT you have to SELECT specific columns. Normally you’d use DISTINCT if you have double results in your result set caused by selecting only a few columns from the table or because a GROUP BY causes the same result.

I also agree with the suggestion from @elibyy btw…

Or alternatively… if you’re only ever going to use the distinct records… simply dont create the duplicate records in the first place? (Better unique-identifiers of the table)

1 Like

hello all,

the problem is solved now…

my database was taking the datetime format earlier, now i changed it to date format only. On this simple distinct function is working… :smile: