SearchingSolutions SearchingSolutions - 28 days ago 11
PHP Question

PHP / mysqli: Prepared Statements with num_rows constantly returning nothing

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
)

Answer Source

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.