0

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;
}

enter image description here

Yves Gurcan
  • 1,096
  • 1
  • 10
  • 25
Thecoder
  • 63
  • 1
  • 9
  • Thanks @showdev I could not find it yet. May be something wrong with my logic. =/ – Thecoder Jun 15 '15 at 23:36
  • Are you using MySQLi? It might be helpful to see if any error messages are generated. See [mysqli_error](http://php.net/manual/en/mysqli.error.php). – showdev Jun 15 '15 at 23:40
  • I'd say the $conex handle should be tossed into `var_dump()`. That will tell you what kind of datatype PHP sees it as being. – Anthony Rutledge Jun 15 '15 at 23:52
  • conex is ok but the var_dump($stmt) is returning false – Thecoder Jun 16 '15 at 00:25
  • $stmt is returning false because there was an error; your code needs to check for that condition *before* it attempts to call `bind_param`. See my answer for one of the issues with the SQL statement. – spencer7593 Jun 16 '15 at 00:27
  • thanks @spencer7593 . It says 'error in prepare invalid use of group function' – Thecoder Jun 16 '15 at 00:31
  • What I am trying to do is to check wich user has't already referred 2 by counting ID_REF – Thecoder Jun 16 '15 at 00:51
  • @showdev I've edited the question to better describe you what I am trying to do. Thanks – Thecoder Jun 16 '15 at 01:02
  • 1
    `WHERE` is applied before the group, so you need to use `GROUP BY id HAVING COUNT(id_ref) < ?` rather than invoking `WHERE`. – Michael Berkowski Jun 16 '15 at 01:03
  • @MichaelBerkowski it returs '0' when I try SELECT id_ref FROM tb_modules GROUP BY id HAVING COUNT(id_ref) < ? – Thecoder Jun 16 '15 at 01:37

3 Answers3

1

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.

Darren
  • 13,050
  • 4
  • 41
  • 79
0

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.

Anthony Rutledge
  • 6,980
  • 2
  • 39
  • 44
  • That's obvious. You could've posted that as a comment. You should alternatively edit your answer to attempt to answer the question. I.e. - `if(!$stmt) { echo 'the error...'; } else { bind the query.... }` – Darren Jun 16 '15 at 00:08
  • I'll keep that in mind, but I think teaching robust coding techniques is beyond the scope. If not, why stop there? Tell him to set the mysqli_options so that only exceptions are thrown? Then it can be done with try\catch\finally. – Anthony Rutledge Jun 16 '15 at 00:08
  • Remember, you're here to answer the question, not tell him how to debug it ;-) But i completely understand where you are coming from. If people followed the manual, they'd see how to achieve this properly! – Darren Jun 16 '15 at 00:19
  • Thankfully, I don't have to care about opinions of the sheep while answering. – Anthony Rutledge Jun 16 '15 at 16:24
0

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140