MySQL error: 'invalid use of a group function'

Question

I have a referral application where an ID can only have 2 direct referrals. I am trying to find IDs which appear less than 2 times in the ID_REF field with the code below.

$stmt = $conex->prepare("SELECT id FROM tb_modules WHERE count(id_ref) < ? ORDER BY id DESC LIMIT 1");
                            
$stmt->bind_param("s", $n);
$n = 2;
                            
$stmt->execute();
                            
$stmt->store_result();
$numrows = $stmt->num_rows;
                                                        
$stmt->bind_result($id);
                             
//$stmt->fetch();
                            
$stmt->close();
                            
//echo $id.' '.'oi';
while ($stmt->fetch()) {
    echo $id;
}

Answer 1

Do the following:

var_dump($conex);
$stmt = $conex->prepare("SELECT id FROM tb_modules WHERE count(id_ref) < ? ORDER BY id DESC LIMIT 1");

var_dump($stmt)

This should shed some light on the matter. I'm betting the $stmt variable is bool or null.

Answer 2

Your code isn't checking whether prepare returned successfully.

If the prepare encounters an error, it returns FALSE. instead of a statement object.

And a boolean FALSE value (the return from prepare) does not have a method/function bind_param.

  $stmt = $conex->prepare("...");
  if(!$stmt) {
      echo "error in prepare ". mysqli_error($conex) ;
  } else {
      // prepare returned a statement object so we can do a bind_param
      $stmt->bind_param(...);

---

To fix a syntax issue in the SQL statement that's causing an error in prepare, replace they keyword WHERE with keyword HAVING.

The predicates (conditions) in the WHERE clause are evaluated when rows are accessed. So the result of an aggregate function (e.g. COUNT() is not going to be available at the time those conditions in the WHERE are check. There's no way for that condition to be evaluated until after the rows are accessed, and the aggregate functions are evaluated. Predicates (conditions) in the HAVING clause get evaluated later, so it's valid to reference aggregates in a HAVING clause.

Just making that change to the SQL statement isn't likely going to get you the result you are looking for. Without a specification, we're just guessing what you are trying to return.

Answer 3

You aren't checking for any errors, that is completely bad development. You should start by ensuring that your prepare() is actually preparing the query correctly and not failing...

$stmt = $conex->prepare("SELECT id FROM tb_modules WHERE count(id_ref) < ? ORDER BY id DESC LIMIT 1");
// now check
if(!$stmt) {
    die($conex->error); // or mysqli_error($conex);
} else {
    $stmt->bind_param('s', $n);
}

Back to other matters. You seem to be trying to compare count(id_ref) which would be a number (int) against $n (which is an int too) but you're trying to pass it as a string.....?

You'll need to change that bind of yours to something like:

$stmt->bind_param('i', $n);

Also note, you can't set the $n variable after you call it.... that's going to throw an error.