highlight duplicate values in query

wkilc

Hi all. I am a full-time teacher who dabbles in coding, mostly by reverse-engineering existing scripts ad looking at tutorials. Apologies in advance for anything dumb I say. :-)

So I have a table like so:

id	judge		student		score
1	Superman	Peter		58
2	Superman	Stewie		54
3	Superman	Meg		58
4	Batman		Chris		58
5	Batman		Brian		54

When I run a query to view just Superman's students, I get:

id	judge		student		score
1	Superman	Peter		58
2	Superman	Stewie		54
3	Superman	Meg		58

I would like to alert the judges to potential tied scores that need breaking. I'd love to highlight (or something) the duplicate column values under "score".... but it would need to find duplicates for JUST the current judge. In the above case, I don't need Chris's 58 to be highlighted.

id	judge		student	         score
1	Superman	Peter       <mark>58</mark>
2	Superman	Stewie		  54
3	Superman	Meg         <mark>58</mark>

...but at the same time, I don't need to highlight Stewie's 54 as a duplicate, since Brian's 54 belongs to a different judge.

I've enough research to tell me INNER JOIN would be part of the solution... but I am unsure how to implement it.

Many thanks.

NogDog

Maybe a sub-query would do the trick:

select
  id,
  student, 
  score,
  (
    select count(id)
    from the_table as t2
    where t2.id <> t1.id and t2.judge = t1.judge and t2.score = t1.score
  ) as num_ties
from the_table as t1
where t1.judge = 'superman'

PS: It should also work even if you don't limit the main query to just one "judge", since the sub-query matches on that column.

wkilc

Thank you so much! Will give it a shot.

Pardon my ignorance, so "num_ties" becomes the indicator to highlight the ties, correct?

How would I code the score value to frame the score with, say, <mark>$score</mark>?

NogDog

wkilc so "num_ties" becomes the indicator to highlight the ties, correct?

Yes, in that it will be 0 if that score is unique, otherwise some positive integer if it is not. With PHP's loose-typing, you could simply use it in a Boolean expression, e.g.:

if($row['num_ties']) {
    // flag it as tied
}

Weedpacket

I'd be inclined to use a common table to record scores that are tied and then look for those scores.

WITH ties AS (SELECT score
	 FROM the_table
	 WHERE judge = 'superman'
	 GROUP BY score
	 HAVING count(score) > 1)
 SELECT id, student, score, true AS tied
 	FROM the_table WHERE score IN (SELECT score FROM ties)
 	AND judge = 'superman'
 UNION
 SELECT id, student, score, false AS tied
 	FROM the_table WHERE score NOT IN (SELECT score FROM ties)
 	AND judge = 'superman'
 ORDER BY score

The transient ties relation collects all the distinct scores that have been earned by more than one student (restricting attention to Superman's students, of course) — if more than one student has the same score then that's a tie. Then the IDs and names of the students with tied scores are selected, and then combined with the rest of Superman's students who don't have tied scores.
The final ordering by score is just so that students with tied scores are consecutive. The tied column is true or false depending on whether the score is one that's tied (so look at the value of that column when deciding whether or not to emphasise the score in the listing).

wkilc

Thank you! I think I get it... so there is a new column called "tied" that I need to add to the original table, and each row will be assigned with a value "true" or "false"... correct?

Weedpacket

No, the query itself will add that to the result.

wkilc

So...

if ($tied = "true") {
  echo '<mark>'.$score.'</mark>';
} else {
  echo $score;
}

Thank you again.

Weedpacket

wkilc

Glancing back at my query now I realise a bug, which I've corrected. Basically, it's possible for students other than those Superman is judging to have scores the same as Superman's students' scores; you don't want them to be leaking into the list of "tied" students, so both branches need judge = 'superman' filters.

So, because I'm the sort of person to get annoyed by repetition, I now see that every mention of the_table is filtered by judge = 'superman' and therefore:

WITH by_judge AS (SELECT id, student, score
	FROM the_table
	WHERE judge = 'superman'),
ties AS (SELECT score
	 FROM by_judge
	 GROUP BY score
	 HAVING count(score) > 1)
 SELECT id, student, score, true AS tied
 	FROM by_judge WHERE score IN (SELECT score FROM ties)
 UNION
 SELECT id, student, score, false AS tied
 	FROM by_judge WHERE score NOT IN (SELECT score FROM ties)
 ORDER BY score

And in fact I suspect that the UNION could be eliminated by putting the IN check into the tied column directly: SELECT id, student, score, score IN (SELECT score FROM ties) AS tied

Ahmed3363

I think this will work properly to search repeated values in a particular column. This should also work, maybe give it try. If you want to find duplicate data (by one or several criterias) and select the actual rows.

wkilc

Thank you both. Forgive my noobness...

Weedpacket, in order to implement your solution would I need to create a new column in my table to record ties?

NogDog, I simplified my table to post here. My actual query is below. Can you possibly help me implement your subquery into it? When I tried, I kept getting "mysqli_num_rows() expects parameter 1 to be mysqli_result"

$query = "SELECT students.`id`, students.`sponsor`, students.`student`, students.`email`, students.`school`, students.`judge`, students.`level`, students.`instrument`, students.`score`
		FROM `students`
		WHERE ((students.judge LIKE '$judgeid')
		and (students.id LIKE '$id%')
		and (students.sponsor LIKE '$sponsor')
  	        and (students.student LIKE '%$student%')
  	      	and (students.email LIKE '$email')
	      	and (students.school LIKE '$school')
	      	and (students.judge LIKE '$judge')
	      	and (students.level LIKE '$level')
		and (students.instrument LIKE '$instrument')
		and (students.score LIKE '$score'))
      		ORDER BY students.$sort LIMIT $eu, $limit";

Weedpacket

wkilc Weedpacket, in order to implement your solution would I need to create a new column in my table to record ties?

No; like I said, that is being created in the query. The only things that need to already exist for that query to run is a table called the_table (because I didn't know what the table was called back then, so I used the same stand-in name as NogDog) that has columns named id, student, score, and judge.

wkilc

Thank you again!
That's what I thought, but I read "tied column" and thought it possibly to exist in the table beforehand.

I've had success with this:

//COUNT TIES
  $result = $rim -> query
  ("SELECT a.judge, a.score, COUNT(*) AS count FROM students a
  INNER JOIN students b ON a.score = b.score
  WHERE ((a.id <> b.id)
  AND (a.judge LIKE '$judgeid'))
  GROUP BY a.score
  ORDER BY COUNT(*) DESC");
  while ($row = mysqli_fetch_object($result)) {
  print  $row->count . ' students are tied with ' . $row->score . '<br />';
}
//END COUNT TIES

This just adds a note alerting the judge of every instance when X number of ties exist with a score of Y.

The problem is, this query runs at the very top of my page, before I close the mysqli connection.
Silly question: how can I take the results of this entire query echo it somewhere else on the page?

I've tried changing that last line to:
$ties = $row->count . ' students are tied with ' . $row->score . '<br />';

...but when I echo $ties it one shows ONE instance of tied scores, even if there are two or more.

Thank you again for your patience with me. 😅

Weedpacket

Use mysqli_fetch_all to get all the results into one array and then loop through the array later.

wkilc

Thank you.
I am looking at examples and cannot figure out how to apply it to my code, above. Every attempt I make breaks it. :-(
Sorry...

Weedpacket

Let's see your attempt. Generally it would be

$tied_score_query = $rim->query('....');
$tied_scores = $tied_score_query->fetch_all(); // Because if you're going to use one style on one line you might as well use the same style on the next line)
// Also variable names.

//... other goings-on go on...

foreach($tied_scores as $tied_score) {
   print  $tied_score['count'] . ' students are tied with ' . $tied_score['score'] . '<br />';
}

wkilc

Thank you again.

I think I am missing a step. No errors, but no results. How would I echo the results later on the page?

//COUNT TIES
  
$tied_score_query = $rim->query('SELECT a.judge, a.score, COUNT(*) AS count FROM students a INNER JOIN students b ON a.score = b.score WHERE (a.id <> b.id) AND (a.judge LIKE "$judgeid") GROUP BY a.score ORDER BY COUNT(*) DESC');
$tied_scores = $tied_score_query->fetch_all();
foreach($tied_scores AS $tied_score) {
print  $tied_score['count'] . ' students are tied with ' . $tied_score['score'] . '<br />';
}

//END COUNT TIES

Weedpacket

Use the loop later on the page.

wkilc

Thank you again.

Do you mean...

<?php
foreach($tied_scores AS $tied_score) {
print  $tied_score['count'] . ' students are tied with ' . $tied_score['score'] . '<br />';
}
?>

No errors, but no results... I used the same query that worked when it executed BEFORE my HTML, but now it seems to not work?

I also tried:

<?php
print_r($tied_scores);
?>

Returns:
Array()

Weedpacket

So something happened to $tied_scores between your populating it from the query and your using it. Remember I can't see your code so I don't know what it's doing.