MYSQLI / PHP: num_rows returns nothing/null

Question

I´ve read many topics with the same problem, but neither of any solved my problem.
I´ve got following code:

$connection = mysqli_connect('...');
if(mysqli_connect_errno()) {
    die('Connect Error');
}
$connection->set_charset("utf8");
$success = $connection->query("INSERT INTO hilde_entrys (Comment, Refer_ID, Account_Adress) VALUES ('".$_POST["comment"]."','".$GET["id"]."','".$userData["user_address"]."')");
$success->store_result(); //I did this as it helped some people - it didn´t help me :(
$rows = $success->num_rows;
while($row = $success->fetch_row()) { //This whole while-loop helped some other guy on stackoverflow - not me
    $rows = $success->num_rows; //Incrementing by 1 each time
} 
$rows = $success->num_rows; // Finally the total count 

if ($rows > 0) { //What I actually intended to do with the num_rows...
    $success->free();
    $success->close();
    $connection->close();
} else {
    $success->free();
    $success->close();
    $connection->close();
    die("Query failed.");
}

Somehow, even though I tried everything possible, it just won´t return anything expected. (Yes, the query and everything else works. I tried it.). What is wrong?
Thank you for all your answers in advance,
VicStudio

Oh, btw: Even when i remove $success->free(); it doesn´t change anything with num_rows....

EDIT: After you guys told me that INSERT won´t return any rows, how can i then check if the query succeeded?

ONE MORE EDIT: First, someone told me to use affected_rows instead of num_rows, which I can todally understand. Somehow, it still didn´t work, so one of you asked me to activate error reporting in my php-script, and I now got following two error messages (one notice and one fatal-error):
Notice: Trying to get property of non-object in (my webpage) on line XX
Fatal error: Call to a member function close() on a non-object in (my webpage) on line XX
The notice refers to the line $rows = $success->affected_rows; (I replaced $rows = $success->num_rows with this). (I commented the while-loop away).
The second error, causing the screen to go blank, is referring to $success->close();, the one in the else-clause. This means
a) it doesn´t detect THAT something was changed and
b) that it has a problem with me closing the query.
Could somebody explain to me why? I checked my database and the INSERT-query succeeded, therefore it should have gone in the if, not in the else. And why doesn´t it want me closing the query? I assume both of these errors have something todo with eachother. Thanks in advance, guys. Sorry for the long time in between my new post, but it takes time figuring stuff out and especially writing it down.

An INSERT will not produce any rows for you to fetch. !!!!!!! — RiggsFolly, Jul 13 '17 at 11:16
Quote: _num_rows - Returns the number of rows in the result set._ You got no result set — RiggsFolly, Jul 13 '17 at 11:18
@jeroen, I know this is pretty unsecure, but it is (until now) only an unstable developer version. Therefore, I will add the security part later on. — , Jul 13 '17 at 11:18
@RiggsFolly That´s concludable, but why don´t i get one? I just did a query and want to check if it succeeded... — , Jul 13 '17 at 11:19
It's not just about security, it's about valid sql and quotes in the data will break yours. And security should never come later on. — jeroen, Jul 13 '17 at 11:20
[Take a dip in the Manual](http://php.net/manual/en/mysqli.affected-rows.php) Come on in the water is lovely — RiggsFolly, Jul 13 '17 at 11:20
@jeroen You can lead a horse to water, but you cannot make it _think_ — RiggsFolly, Jul 13 '17 at 11:23
I read it, and gave an answer as it was a bit long for a comment — RiggsFolly, Jul 13 '17 at 12:12

score 2 · Answer 1 · answered Jul 13 '17 at 11:18

2

For insertion you should use affected_rows $rows = $success->affected_rows; PHP affected_rows

Read about num_rows here PHP num_rows

answered Jul 13 '17 at 11:18

Dragin

432
3
11

Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser. – RiggsFolly Jul 13 '17 at 11:30
Can't test at the moment if that could be the case but did you try removing `$success->store_result()`? – Dragin Jul 13 '17 at 11:55

RiggsFolly · Accepted Answer · 2017-07-13T12:10:14.853

1

As an mysqli_query() running an INSERT query only returned either TRUE or FALSE then your $success will never be a mysqli_result object as it would in a SELECT Query.

Therefore your test for affected_rows should use the connection object like this. In fact the affected_rows property only even exists on the mysqli_object.

$connection->affected_rows;

So

$connection = mysqli_connect('...');
if(mysqli_connect_errno()) {
    die('Connect Error');
}
$connection->set_charset("utf8");
$success = $connection->query("AN INSERT QUERY");

$rows = $connection->affected_rows;

The PHP manual aint great in this area I admit, but it does state this

This also explains why

$success->free();
$success->close();

dont work as $success is not an object.

And also why

$success->store_result();

does not work. $success is not an object and there are no results to store.

edited Jul 13 '17 at 12:10

answered Jul 13 '17 at 12:01

RiggsFolly

93,638
21
103
149

You have solved the problem a)/notice, but there still is the fatal error saying that close() is trying to call a non-object in line 16 of the code snippet (it now took the correct claue, but still seems to have a problem with the close()-call...) – Jul 13 '17 at 12:16
Read all the answer, may need a page refresh – RiggsFolly Jul 13 '17 at 12:17
EDIT: Now that i saw your new edit, i just tried it again and...IT WORKS! Thank you so much for all your help! Also thanks to Dragin, bringing me near tot he goal and to jeroen, showing me that security actually is a thing. – Jul 13 '17 at 12:20

MYSQLI / PHP: num_rows returns nothing/null

2 Answers2