I’ve asked this before, but never got it working. Basically, my custom invite system asks prospective members which existing member referred them. So, using MySQLi prepared statements, I’d like to perform a simple check of the site’s main DB and produce an error if the entered value does NOT exist in the DB.
Thanks!
Just run a query such as "SELECT * FROM users WHERE username=‘a-member-name’
Then, check the number of rows found. If zero, you did not get any and throw the error.
Basically, not existing means you found none in the database. Does that make sense to you?
Actually, no don’t (ever) use SELECT * . In this case, you should SELECT COUNT(*), execute the query, fetch, and then test the result of the COUNT(*).
In one of the OP’s previous threads for this same/similar subject, he was probably (with no php/database error reporting) getting an out of sync error due to using multiple prepared queries, but not fetching the data from one (just testing the number of rows), before running another one. You MUST either fetch all the data from a prepared query or you must close the prepared statement so that you can execute another query on the same connection.
How do I do that? 
I now use this right after my prepared statement:
$stmt->close();
Is that what you mean?
I tried this:
if ($stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?')) {
$stmt->bind_param('s', $username);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows=0) {
$user2 = "<br><span class='error_msg'>Referring username not found!</span>";
}
}
$stmt->close();
But it doesn’t work (it doesn’t catch that the submitted data is not in the DB).
@phdr What’s wrong with select *? I thought prepared statements are safe?
I never use count() function in a select as it is a waste of time. Why make the server do extra work when you can get the count in the results without asking for it? This type of routine should work for you:
$stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?'));
$stmt->bind_param('s', $username);
$stmt->execute();
if ($stmt->num_rows=0) {
// No data located!
$user2 = "<br><span class='error_msg'>Referring username not found!</span>";
} else {
// Data found, now process it
$user_data = $result->fetch_assoc());
echo "user was located, handle the data here (" . $user_data['username'] . ")";
}
$stmt->close();
Not sure what you need to do with the data found, but, it is stored in $user_data variable array.
I showed it displaying the username as an example. Hope that works for you.
At this point, it’s hard to decide to reply because several points have already been given in previous replies and that I am just repeating now. I recommend that you re-read and actually do the things that have already been suggested.
The absence of a message being set only when several logic statements are successful doesn’t mean that the data was or wasn’t found, it just means that the code where the message is being set at (or where the message is being displaying at), didn’t execute. Any one of the logic statements can fail and produce the same end result. A list of several possible reasons your code can fail and ways to check what is occurring was previously given. The current code has even one more reason. It is trying to assign a zero to the num_rows properly, which produces a fatal run-time error. One = is an assignment operator, two == is a comparison operator. Your code for this step was previously using a < 1 comparison. What happened? This also indicates you don’t have php’s error related settings set up to either display or log all errors, which was something else that was previously pointed out.
Your example doesn’t seem to work (produces a blank page and/or simply doesn’t do the check I want).
Basically, I want to only check if the submitted var “username” does NOT exist in the DB and then produce the error. I know this is strange, but I’m checking to see if the value a user enters doesn’t already exist to check if they are just making stuff up, or were actually referred by a real member (at which point they will continue the process).
@phdr Cool, I appreciate the feedback. I couldn’t get the earlier help/ideas to work, which is why I decided to start a new thread. But I will go back and review previous replies.
This tells you if it is NOT found. Basically, if no data, no number of rows, nothing was found for the query.
I tried this:
if ($stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?')) {
$stmt->bind_param('s', $referral);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows=0) {
$user2 = "<br><span class='error_msg'>Referring username not found!</span>";
}
}
$stmt->close();
And it produces a blank page. I also tried if ($stmt->num_rows==0) { and if ($stmt->num_rows<0) { and if ($stmt->num_rows<=0) { and it simply proceeds despite the data not found in the DB. 
You can’t compare a prepare statement… Does not make sense.
Try the example I posted. Run the query and THEN check for results.
You are testing before there is anything to test!
What simply proceeds? It sounds like you are unconditionally running some later code.
This has just become more than I feel like messing with it and I decided not to further develop it. Thanks very much, esp. to @phdr and @ErnieAlex and everyone else for your polite help! 
Well, I had posted code that would work for you, you can’t test a prepare. Nothing to test.
But, I know how it feels to be frustrated with code. Good luck on the rest of your projects.
Thanks! I know where to go for further help. Everyone here has been very nice! 
Taking another look at it. But, this part of your example produces a blank page:
if ($stmt->num_rows=0) {
?
Sorry… ($stmt->num_rows==0) PHP likes double equal signs… I miss-typed it…
Okay I’m working on this again. I already have a prepared statement that checks to see if something already exists in the DB as shown here:
if ($stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?')) {
$stmt->bind_param('s', $username);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows>0) {
$user_error = "<br><span class='error_msg'>Username already taken!</span>";
}
}
$stmt->close();
But now I need to do the opposite: checking to see if a var does NOT exist in the DB and produce an error. Logically, I tried: if($stmt->num_row<0) { and if($stmt->num_rows=<0) { if($stmt->num_rows==0) { and it didn’t work or produced a blank page.
Someone did say something about adding a constraint to the DB and try an insert? But I’m not sure.
Does anyone have some fresh ideas? Thanks!
Those are two different problems and the steps to find what’s causing each one is different. For a specific version of your code, you need to troubleshoot what’s causing the symptom or error. If all you are doing is trying a bunch of different things, it will take you forever to find the cause of the problem.
This won’t work because your goal is to find if num_rows IS zero. It won’t ever be less than zero.
This should work because it includes the case where num_rows IS zero.
This should also work because it is correctly testing if num_rows IS zero.
Blank php pages are due to either 1) php syntax errors, 2) fatal runtime errors (which is the case with the single = in this specific code), or 3) code that simply isn’t being executed and outputting anything.
To get php to help you, set php’s error_reporting to E_ALL and set display_errors to ON. These settings should be in the php.ini on your system so that you can set/change them at a single point and so that they will help you find ALL php errors (you can put these settings in your code, but since your code never runs for php syntax errors, you will still get a blank page in this case.)
If setting php’s error related settings doesn’t help narrow down the problem, you will need to post all your current code so that someone can see the bigger picture of what you are doing.
Well, again, as I mentioned before, you can’t do it that way. You can’t test the “prepare”. That always comes out positive! It’s just a prepare!
You need to run the query and after the ->execute, test the results, nothing before that means anything.
At that point, you can check for results. So, it would be like this:
$db->prepare('SELECT * FROM xf_user WHERE username = ?');
$stmt->bind_param('s', $username);
$stmt->execute();
if ($stmt->num_rows!=0) {
// If $stmt exists, then you got results and you can handle the data...
$results = $stmt->fetch_assoc(); Or, if multiple query, while($row=$stmt->fetch_assoc()) {
} else {
// No results found, handle as needed...
$user_error = "<br><span class='error_msg'>Username already taken!</span>";
}
$stmt->close();
This is a more correct way to handle the system. But, there are other ways. Just remember you can not test a “prepare”, you need to test the results after the execute…