Alternate Way to Check Result num_rows >0

Question

Up to now I was checking num_rows >0 the following way:

$sql = "SELECT name FROM tbl_criteria WHERE ID =?";
$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt, $sql)){
 header("location: /XX");
}else{
 mysqli_stmt_bind_param($stmt, "i", $cId);
 mysqli_stmt_execute($stmt);
 $result = mysqli_stmt_get_result($stmt);
 mysqli_fetch_all($result,MYSQLI_ASSOC);
 if($result-> num_rows >0{
  echo 'Appear if rows >0';
  foreach($result as $row){
   echo 'Appear for each result';
   }
  }
}

So suddenly this is not working anymore. I always get 0 results. I have read that it is important to call mysqli_stmt_store_result() before accessing num_rows otherwise it would usually return 0. I thought that I'm storing the results with $result = mysqli_stmt_get_result($stmt); or am I wrong? I am hosting my website on a webserver so it could be because of an update as well. A couple of days ago it was working fine.

You really should switch to PDO, but if you want to keep on using mysqli you should know that using `num_rows` is a bad practice. — Dharman, Jun 07 '20 at 21:02
You need to stop manually checking for errors. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) and [Should I manually check for errors when calling “mysqli_stmt_prepare”?](https://stackoverflow.com/q/62216426/1839439) — Dharman, Jun 07 '20 at 21:03
*"A couple of days ago it was working fine."* - Not for what you posted, that's a for sure. — Funk Forty Niner, Jun 07 '20 at 21:05

score 1 · Answer 1 · answered Jun 07 '20 at 21:07

1

Your code is not working most likely due to multiple syntax errors you have. I recommend to read How to get the error message in MySQLi?

If you would like to continue using mysqli, then there is no need for this overly complex code. You can simply fetch all results into an array. There's no need to ever use num_rows.

$value = "cId";
$stmt = $conn->prepare("SELECT name FROM tbl_criteria WHERE ID =?");
$stmt->bind_param('s', $value);
$stmt->execute();
$result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// If no rows were fetched the array will be empty and condition will be false
if ($result) {
    echo 'Appear if rows >0';
    foreach ($result as $row) {
        echo 'Appear for each result';
    }
}

answered Jun 07 '20 at 21:07

Dharman

30,962
25
85
135

Okey, sorry for the confusion I didn't copy the code properly before. I tried it with PDO and got the following code now `$stmt = $conn->prepare("SELECT name FROM tbl_criteria WHERE ID=:criteria"); $stmt->bindParam(':criteria', $cId); $stmt->execute(); $result = $stmt->fetch(PDO::FETCH_ASSOC); while($row = $stmt->fetch()) { echo $row['name']; foreach($result as $row){ echo 'test'; } }` Is this good or can I simplify the code again? – Robin Jun 08 '20 at 21:03
@Robin Comments are not good for such discussions. If you have a new question then you can start a new question on the site. I recommend you to read these articles first; you might learn some tricks about using PDO: https://phpdelusions.net/ – Dharman Jun 08 '20 at 21:05
well ok it works for me so I don't need to ask another question, but thanks – Robin Jun 08 '20 at 21:07

Alternate Way to Check Result num_rows >0

1 Answers1