Count the rows with same ID in php

Question

I'm trying to count the rows with the same id

|curs_code|cursistnr|Mark|Payed| C# 20 6 50 PHP 25 8 100 C# 8 7 100

the output needs to be

C#, 2times

I've tried

$sql = "SELECT count(curs_code) as 'count_of_curs' FROM cursus where curs_code='$id'"; $query = $magazijn->query($sql); return $query->fetchAll());

but gives me this output:

C# array (size=1) 0 => array (size=2) 'count_of_curs' => string '1' (length=1) 0 => string '1' (length=1) PHP array (size=1) 0 => array (size=2) 'count_of_curs' => string '1' (length=1) 0 => string '1' (length=1)

your thought about this?

It looks like the problem you are reporting is that you expect a count of 2 to be returned, but the count is returning 1. I think what this means is that the two occurrences of C# appear to be the same to you, but they are actually different values, likely due to "hidden" whitespace characters in one of the values. As a test, try a query like SELECT curs_code, HEX(curs_code), CHAR_LENGTH(curs_code) FROM cursus WHERE curs_code LIKE '%C#%'. — spencer7593
– spencer7593, Commented Jun 17, 2015 at 14:19
For your query, the group by is not required, you count all rows given by the where clause. — Benno Eggnauer
– Benno Eggnauer, Commented Jun 17, 2015 at 14:20

Alexandre · Accepted Answer · 2015-06-17 14:18:49Z

3

SELECT curs_code, count(*) as 'count_of_curs' FROM cursus where curs_code='$id' group by curs_code

Remove the where if you want the entire list.

answered Jun 17, 2015 at 14:18

Alexandre

3021 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Floeske Over a year ago

You need to echo it with $countDown[0]['count_of_curs'] though

spencer7593 · Accepted Answer · 2015-06-17 14:27:41Z

It looks like the question you are asking is "Why is the expression COUNT(curs_code) returning a value of 1 when I expect a value of 2?"

I think what this means is that the two occurrences of C# that appear to be the same to you are actually different values. This is likely due to "hidden" whitespace characters in one of the values.

To return all values of curs_code that contain the string C#, and to "see" what is actually stored, run a query like this:

SELECT curs_code , HEX(curs_code) , CHAR_LENGTH(curs_code) FROM cursus WHERE curs_code LIKE '%C#%'

As a demonstration:

 SELECT cc , HEX(cc) , CHAR_LENGTH(cc) FROM ( SELECT 'C#' AS cc UNION ALL SELECT 'C#\t' UNION ALL SELECT 'C#\0' ) c WHERE cc LIKE '%C#%' cc HEX(cc) CHAR_LENGTH(cc) --- ------- --------------- C# 4323 2 C# 432309 3 C# 432300 3

If you just want to find "duplicate" values of curs_code:

 SELECT c.curs_code , COUNT(1) AS count_of_curs_code FROM cursus GROUP BY c.curs_code HAVING COUNT(1) > 1 ORDER BY c.curs_code

Collectives™ on Stack Overflow

Count the rows with same ID in php

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related