highlight duplicate values in query

wkilc · Jul 10, 2020

So...

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

Thank you again.

NogDog · Jul 10, 2020

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
}

Ahmed3363 · Jul 11, 2020

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.

Weedpacket · Jul 13, 2020

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

wkilc · Jul 17, 2020

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 · Jul 18, 2020

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 · Jul 20, 2020

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 · Jul 20, 2020

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

wkilc · Jul 21, 2020

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 · Jul 22, 2020

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 · Jul 22, 2020

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 · Jul 22, 2020

Use the loop later on the page.

wkilc · Jul 22, 2020

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 · Jul 25, 2020

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.

wkilc · Jul 27, 2020

I appreciate your patience with me.

What I shared was it... there's not more code. Perhaps that's the problem.

This worked, returned the expected result, but executed before the HTML and therefore appeared at the top of the page before the HTML tag:

//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 doesn't return the result:

//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

Then in the body of the HTML:

<?php
print_r($tied_scores);
?>

Returns: Array ()

I laterally close the connection on the next line, start the HTML, and attempt to "print_r"... same result.

Weedpacket · Jul 27, 2020

I notice your second example hasn't quite the same query as the first. As well as removing all the linebreaks, you've swapped your use of ' and ". PHP doesn't interpolate variables (like $judgeid) in single-quoted strings*. So unless there is a judge ID that really is "$judgeid", dollar sign and all, then the query won't find anything and you'll get an empty array.

Try

$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 />';
}

In other words, try the query that does get results.

And unless $judgeid is likely to contain things like % then the LIKE could just be = and really it should be a bound parameter.

*(And many DBMSs would complain about the double quotes around $judgeid.)

wkilc · Jul 27, 2020

Thanks again.

Getting closer, I think.
print_r($tied_scores);
now returns:
Array ( [0] => Array ( [0] => Smith [1] => 10 [2] => 2 ) )

The original query would return:
2 students are tied with 10

"Smith" is the name of the $judgeid in this instance.

Weedpacket · Jul 27, 2020

Remember through all this I don't use MySQL, so I don't use MySQLi functions.

Checking the documentation for mysqli_result::fetch_all I see that it returns a numerically-indexed array, and needs to be told explicitly if you want array elements to actually be named instead. (I usually use the PDO interface, which does this by default.)

Ahmed3363 · Jul 28, 2020

Using common table expression (CTE) , one can select only duplicate records ! you'll need read access to your database and a tool to query your database.

wkilc · Jul 28, 2020

Success! (I think.)

$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)

Then later, in the page HTML, I use:
print '<mark>' . $tied_score[2] . ' students are tied with ' . $tied_score[1] . '</mark><br />';

[2] gives me count (of tied students), [1] gives me score that is tied, and [0] gives me the judge's name.

Thank you so much... especially you @Weedpacket!

highlight duplicate values in query

Wwkilc

NogDog

AAhmed3363

Weedpacket

Wwkilc

Weedpacket

Wwkilc

Weedpacket

Wwkilc

Weedpacket

Wwkilc

Weedpacket

Wwkilc

Weedpacket

Wwkilc

Weedpacket

Wwkilc

Weedpacket

AAhmed3363

Wwkilc