Drupal SQL "Select count distinct"

Question

Hi can someone help debug this?

Currently my script throws this error "Parse error: syntax error, unexpected '=' in includes/common.inc(1695) : eval()'d code on line 38" and I've narrowed it down to this SQL statement. It appears that counting results rows in Drupal isn't as straight forward as I'd hoped.

$sql_checkIP = "SELECT COUNT(*) 
FROM (SELECT DISTINCT v.hostname 
FROM {pollfield_votes} v 
WHERE v.hostname = '%s' AND v.nid =%d)";

$result0 = db_result(db_query($sql_checkIP, $hostname, $nid));

Thanks for any help :)

Answer 1

First thing I see is that you haven't wrote any alias for the derived table

sql_checkIP = "SELECT COUNT(*) FROM (SELECT DISTINCT v.hostname FROM {pollfield_votes} v WHERE v.hostname = '%s' AND v.nid =%d) as new_derived_table";

Now, I don't know how many results you'll have with your query, but db_result is used only when you have only one row as result. if it's the case ok, otherwise you should implement a loop like:

$result = db_query($sql_checkIP, $hostname, $nid); while($res=db_fetch_array($result)){
$count_row = $res['COUNT(*)'];
}

PS:Sorry for the source format, but i'm still trying to figure out how to well format the source here :p