php - Count instances in table and capture the id of every instance -

what i'm trying search through table , count instances of letter, , store id of each instance in mysql , php (pdo).

what had been messing around this:

$user = 'john'; $stmt = $pdo->prepare("select id, substr(surname, 1, 1) surname_init,count(*) count first_table user = :user group surname_init order count desc"); $stmt->bindvalue(":user", $user);  $stmt->execute(); $row = $stmt->fetchall(pdo::fetch_assoc);  var_dump($row); 

this result:

array (size=2)   0 =>      array (size=3)       'id' => string '3' (length=1)       'surname_init' => string 'b' (length=1)       'count' => string '2' (length=1)   1 =>      array (size=3)       'id' => string '7' (length=1)       'surname_init' => string 'd' (length=1)       'count' => string '1' (length=1) 

so have 2 results: b has 2 instances, d has one. "b" has 1 resulting id returned two.

is possible? think i'm missing here..

use group_concat() rows matched group by, like:

select     group_concat(id) id_list,     substr(surname, 1, 1) surname_init,     count(*) count first_table user = :user group surname_init order count desc