Fetch COUNT DISTINCT data with prepared statements

Question

I have this code to get a COUNT DISTINCT data:

 $param = 'email';

    $stmt = $conn->stmt_init();
    $stmt = $conn->prepare("SELECT COUNT(DISTINCT(?)) FROM contatos");
    $stmt->bind_param('s',$param);
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($count);

    while ($stmt->fetch()) {
        echo $count;
    }

But echo $count always returns 1, but i have dozens of records...

What is wrong?

Thanks

it's because of the DISTINCT and probably passing a unique email address. — Funk Forty Niner, Aug 24 '17 at 21:02

score 2 · Answer 1 · answered Aug 24 '17 at 21:07

2

Binding is not allowed for column names (or table names). Your query is not executing correctly. You need to directly pass the name of the field.

$stmt = $conn->prepare("SELECT COUNT(DISTINCT(email)) FROM contatos");

answered Aug 24 '17 at 21:07

ishegg

9,685
3
16
31

It persist to return 1....:( – Pedro Antônio Aug 24 '17 at 21:16
That's weird... Try `SELECT`ing the field `email`, instead of the `COUNT()`, and see which email it returns... and you can start from there to figure out what's wrong. Are you sure you're selecting the same database in the same host? – ishegg Aug 24 '17 at 21:22
Yes, is the same database... I solve this alternatively: I use only `SELECT DISTINCT(email) FROM contatos`, using `$stmt->get_result()` and `echo $result->num_rows;` – Pedro Antônio Aug 24 '17 at 21:29
Maybe you had some NULL emails... Glad you could solve it! – ishegg Aug 24 '17 at 21:32

Fetch COUNT DISTINCT data with prepared statements

1 Answers1