是否有任何`affected_rows`等效于SELECT语句

Question

SELECT * FROM `products` WHERE `category` = ?

Is there anyway to know, using mysqli and without fetching, if this statement returned an empty result?

$sql = $mysqli->prepare("SELECT * FROM `products` WHERE `category` = ?");
$sql->bind_param('s', $category);
$sql->execute();

var_dump($sql);
exit();

As you can see, I'm using var_dump to see if there is any property I can use for this purpose. Here's the result:

object(mysqli_stmt)#2 (9) { 
["affected_rows"]=> int(-1)
["insert_id"]=> int(0)
["num_rows"]=> int(0)
["param_count"]=> int(1)
["field_count"]=> int(7)
["errno"]=> int(0) 
["error"]=> string(0) "" 
["sqlstate"]=> string(5) "00000"
["id"]=> int(2) }

I see nothing that can serve the purpose of knowing if the query returned some lines or not.

---

Alternatively, is it possible to solve this entire problem in the query itself? For example, will if (SELECT * FROM products WHERE category = ?, RETURN 1, RETURN 0) always return 1 if the query successfully returns some lines?

I'd like to delete a category from a database only if no products are associated with it: doing so in just a few lines of code would be much better, but I'm not totally sure if this will work

if (SELECT * FROM products WHERE category = ?, RETURN 1, (DELETE FROM categories WHERE name = ?))

Which is the best way to do this? Thanks.

---

edit: num_rows isn't working

$sql->bind_param('s', $c);
$sql->execute();

while($sql->fetch())
printf("one line - " . "
");

var_dump($sql->num_rows);
exit();

result:

one line - one line - one line - one line - one line - one line - int(0)

Answer 1

You can use mysqli_num_rows to achieve this. In your case I'd do something like this:

$result = $sql->query();
$row_cnt = $result->num_rows;

instead of $sql->execute();.