PHP / mysqli: Prepared Statements with num_rows constantly returning nothing

Question

In my test-surroundings there is a database containing some Person Information (Name, E-Mail, Adress etc.). These Informations can be inserted by anyone into the database via a form. In the background they are inserted with a parameterized INSERT into the database after submission.
What I now would like to do is to detect if some person tries to insert the same values into the database again, and if he does, not inserting the new values and instead showing an error message. (So every person name in the database is unique, there are no multiple rows linked to one name).
I had a numerous number of ideas on how to accomplish this. My first one was to use a query like REPLACE or INSERT IGNORE, but this method would not give me feedback so I can display the error message.
My second attempt was to first do a SELECT-query, checking if the row already exists, and if num_rows is greater than 0, exit with the error message (and else do the INSERT-part). For this to work I will have to use parameterized queries for the SELECT too, as I´m putting some user input into it. Figuring that parameterized queries need special functions for everything you could normally do with way less lines of code, I researched in the internet on how to get num_rows from my $statement parameterized-statement-object. This is what I had in the end:

$connection = new mysqli('x', 'x', 'x', 'x');
if (mysqli_connect_error()) {
    die("Connect Error");
}
$connection->set_charset("UTF-8");
$statement = $connection->stmt_init();
$statement = $connection->prepare('SELECT Name FROM test WHERE Name LIKE ?');
flags = "s";
$statement->bind_param($flags, $_POST["person_name"]);
$statement->execute();
$statement->store_result();
$result = $statement->get_result(); //Produces error
if ($result->num_rows >= 1) {
    $output = "Your already registered";
} else {
    $output = "Registering you...";
}
exit($output);

After all, I can´t get why mysqli still won´t give me num_rows from my statement. Any help is appreciated, thanks in advance!
Oh, and if you guys could explain to me what I have to do to get affected_rows,that would be awesome!

EDIT: I know I could to this by using unique constraints. I also found out that I can find out if INSERT IGNORE skipped the INSERT or not. But that won´t answer my complete question: Why does the SELECT num_rows alternative not work?

ANOTHER EDIT: I changed the code snippet to what I now have. Although my mysql(i)-version seems to be 5.6.33 (I echo´d it via $connection->server_info) get_result() produces the following error message:
Fatal error: Call to undefined method mysqli_stmt::get_result() in X on line X (line of get_result)

How about UNIQUE constraints on the database columns you want to protect? — Kevin_Kinsey, Sep 22 '17 at 20:10
@Kevin_Kinsey Why does the num_rows above not work, anyways? It would be useful for future projects knowing how to fetch num_rows and affected_rows with parameterized mysqli statements and queries. Thanks for the suggestion, though! — 3x071c, Sep 22 '17 at 20:19
@Machavity I´m already executing the query 3 lines above `fetch()`! Do you mean I should run `execute()` after `store_result()` and `bind_result()`? — 3x071c, Sep 23 '17 at 12:44
Ah, so you are. Can you use [get_result()](http://php.net/manual/en/mysqli-stmt.get-result.php)? — Machavity, Sep 23 '17 at 13:09
@Machavity Could you post the code I would have to write with get_result()? When I do it I get constantly a fatal error: Call to undefined method mysqli_stmt::get_result() — 3x071c, Sep 23 '17 at 13:16
It sounds like you can't use it with your web server. That function requires the server to be using MySQLND, which it isn't — Machavity, Sep 23 '17 at 13:18
@Machavity What are my options then on how to get `num_rows`? — 3x071c, Sep 23 '17 at 15:42
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/155163/discussion-between-searchingsolutions-and-machavity). — 3x071c, Sep 23 '17 at 17:35

Bud Damyanov · Accepted Answer · 2017-09-23T17:49:00.670

0

The behaviour of mysqli_num_rows() depends on whether buffered or unbuffered result sets are being used. For unbuffered result sets, mysqli_num_rows() will not return the correct number of rows until all the rows in the result have been retrieved. Note that if the number of rows is greater than PHP_INT_MAX, the number will be returned as a string.

Also make sure that you declare ->store_result() first. Moreover the function doesn't work with LIMIT used jointly with SQL_CALC_FOUND_ROWS. If you want to obtain the total rows found you must do it manually.

EDIT:

If nothing from the suggestions does not work for you, then I would propose to rewrite your SQL query:

 SELECT `Name`, (SELECT COUNT(*) FROM `Persons`) AS `num_rows` FROM `Persons` WHERE `Name` LIKE ?

This query will return the total number from your Persons table, as well as Name, if exist.

edited Sep 23 '17 at 17:49

answered Sep 23 '17 at 17:30

Bud Damyanov

30,171
6
44
52

I´m not dealing with many entries, so `PHP_INT_MAX` isn´t a problem for me. I am declaring `store_result()` as seen in the example above. I can´t use `get_result()` because it seems like my webbrowser does not support it. How can I fetch `num_rows()` then? – 3x071c Sep 23 '17 at 17:33
This query returns num_rows every time as 1, even when the value does not exist yet... :( – 3x071c Sep 23 '17 at 17:46
what do you mean with "return"? Do I have to use `->num_rows` or do I have to assign the `->prepare(yourquery)` to a variable which contains the count? Thanks for all the answers and edits, though! – 3x071c Sep 23 '17 at 18:07
The new EDIT worked fine with `->num_rows`, thanks! Interesting how long that query is compared to what I want to have ;) I will ask my Webserver-Hosters/-Providers why my 5.6 MySQL doesn´t provide the extention needed for `get_result()`... – 3x071c Sep 23 '17 at 18:59
Excuse me, but I´ve got 2 questions to the new query: – 3x071c Sep 24 '17 at 16:23
1. Does the Count(*) count as a result if I should fetch_assoc()? – 3x071c Sep 24 '17 at 16:24
2. Does the AS apply to all of the values (Name AND Count) or only to the count (Can I use the other results of the query, like Name, while using this query to fetch num_rows?) – 3x071c Sep 24 '17 at 16:25
Excuse all the questions in seperate comments, I probably have to get used to not do ENTER when intending to make a newline... – 3x071c Sep 24 '17 at 16:26

PHP / mysqli: Prepared Statements with num_rows constantly returning nothing

1 Answers1