I get an http error 500 with your revision. 
$found = $stmt->fetch();
if ($found->num_rows == 1) {
I’m use to PDO, not mysqli
Okay, I ended up with this:
$stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?');
$stmt->bind_param('s', $referral);
$stmt->execute();
$stmt->store_result();
$stmt->get_result($found);
$found = $stmt->fetch();
if ($found->num_rows == 1) {
$ref_error = "<br><span class='error_msg'>Referring username not found!</span>";
}
And despite what I input for $referral, it does not produce the intended error at all. If I change it to:
if ($found->num_rows == 0) {
It ALWAYS shows the error even if I input something I know is in the DB (it’s supposed to produce the error if $referral does NOT exist in the DB).
Print out the num_rows to see what it holds. If it returns anything, that should be the count of that records set, if it doesn’t, that should be zero.
I’m not sure I follow. Do I need to use var_dump? The usernames are all unique in the DB, so if the $referral matches, it would be 1 row? And 0 if not? I’m confused.
var_dump is useful to see what that value is.
Correct, it should be 1 or 0 depending on if there is a constraint. However, if there is a constraint, you could just insert the record and watch for a duplicate exception to be returned as well, rather than checking and inserting.
1 Like
I’m not sure if there is a constraint on the username row. Would it hurt anything to add one? How do I tell if there already is one? 
I see how to insert, but am not sure how to catch the exception and produce an error if the var is NOT in the DB. Can you further assist? Thanks!!
Because the query, code, and variables are being changed without reason from post to post in this thread, you have a ‘moving target’ problem, in addition to the real problem. You need to pick one coding method and stick with it. If something doesn’t work, you need to find out why, rather than to throw it away and try something else.
Some of the php statements are being used incorrectly and should be producing errors. That you got a http 500 error for one version of the code means that the php error related settings are not set up to display all errors. To get php to help you, find the php.ini that php is using and set error_reporting to E_ALL and set display_errors to ON. Stop and start your web server to get any changes made to the php.ini to take effect and use a .php script with a phpinfo(); statement in it to confirm that the changes actually worked.
Next, the query you use must be appropriate for the task. There are two ways to do this - 1) Use a SELECT COUNT(*) … query, fetch and then test the count value (this is the preferred method if you don’t want any of the actual data), 2) Use a query that selects something, then either test the number of rows the query matched or just fetch the data and test if a row was fetched. Since your username column should already be defined as a unique index, you should expect at most one row of data. A problem with this second method, with prepared queries, if you don’t fetch all the data from a query OR close the prepared statement, you will get out of sync errors on other queries.
Some of your queries using SELECT * …, and then comparing the first fetched column value with a number can produce any result, depending on what the first column actually is. You should list out any column(s) you are selecting, so that any reference to a specific column in the fetched data will be for a known value.
Also, you were advised to use exceptions for errors and in most cases let php catch the exception, where it will use its error related settings to control what happens with the actual error information. This will let you remove the existing error handling logic you have now, which since it is not doing anything when there is an error can be contributing to some of the wrong results. Since you didn’t ask any specific question about doing that, I’ll assume you managed to set the mysqli error mode to exceptions.
So, Keep It Simple, and use a SELECT COUNT(*) … query, and only those php statements have have something to do with the task. Assuming you have php’s error related settings set to display all errors and are using exceptions for database statement errors, the following should work or it/php should tell you why it doesn’t -
$stmt = $db->prepare('SELECT COUNT(*) FROM xf_user WHERE username = ?');
$stmt->bind_param('s', $referral); // what does var_dump($referral); show or is the variable actually $username?
$stmt->execute();
$stmt->bind_result($count); // the fetched value will be in $count
$stmt->fetch();
if($count == 0)
{
$ref_error = "<br><span class='error_msg'>Referring username not found!</span>";
}
1 Like
Okay, got it. I moved the ref_error statement further down the script to catch it at the right spot, and it works now!
Thank you so much @phdr , @chorn , and @astonecipher and everyone else’s time!