The PHP documentation contains the reasons why an affected_rows statement might return 0, 1, -1 or even 3 in some scenarios, as does the answer to this particular stack question.
PHP / MySQLI: strange readings for affected_rows
But the wall I hit was that affected_rows was returning NULL. I did not find any stack question which currently answers this issue. So I figured out why it would return NULL.
Its because you are making a mistake in your syntax. I tend to associate the usage of affected_rows to the same as num_rows. But in fact the usage of both is different and that is why I got the particular NULL value.
num_rows usage:
$query = $handler->query("some select query");
$rows = $query->num_rows;
this would return the proper number of rows;
affected_rows usage:
$query = $handler->query("some update insert query");
$rows = $query->affected_rows;
this would return null as the $query variable unlike the previous $query would return true or false. Therefore in such a case you would always have to make calls to the $handler database connector object.
This is a problem I dealt with just now and now when I go back and look at the docs page I see that I've totally missed it. I think many people tend to treat affected_rows the same way as num_rows, but that's just me. But I think even though the question is a self-answering one it deserves a place here, because it took me almost 12 hours before I finally did a var_dump and got to the core of the issue.
If someone knows a better answer to this then please improve this answer.
The question lies in why are both num_rows and affected_rows treated differently in such a context?
