Help with prepared statements syntax, check to see if string *NOT* in DB

General PHP Help
#1

This doesn’t appear to work. I also tried num_rows<0. It’s supposed to see if the entered string ($username) is NOT in the DB, and display the error. I’m not sure if the syntax is correct. Help please!

if ($stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?')) {
	  $stmt->bind_param('s', $username);
          $stmt->execute();
	  $stmt->store_result();
  	  if($stmt->num_rows<1) {
            $user_error = "<br><span class='error_msg'>Member not found!</span>";	     
          }           
}
#2 
if ($stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?')) {
	  $stmt->bind_param('s', $username);
      $stmt->execute();
	  $stmt->bind_result($count);
      $stmt->fetch();
  	  if($count == 0) {
            $user_error = "<br><span class='error_msg'>Member not found!</span>";	     
      }           
}
#3

Mysqli looks so hideous. In PDO this would be much cleaner and simpler

$stmt = $pdo->prepare('SELECT * FROM xf_user WHERE username = ?');
$stmt->execute([$username]);
$result = $stmt->fetch();

if (!$result) {
    $user_error = "<br><span class='error_msg'>Member not found!</span>";
}

Here is a complete PDO example.

<?php declare (strict_types = 1);

$host = '127.0.0.1';
$db = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

$pdo = new PDO($dsn, $user, $pass, $options);

$stmt = $pdo->prepare('SELECT * FROM xf_user WHERE username = ?');
$stmt->execute([$username]);
$result = $stmt->fetch();

if (!$result) {
    $user_error = "<br><span class='error_msg'>Member not found!</span>";
}
1 Like
#4 
if ($stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?')) {
  	$stmt->bind_param('s', $username);
  	$stmt->execute();		
        $stmt->bind_result($count);
      	$stmt->fetch();
  	if($count == 0) {
        	$user_error = "<br><span class='error_msg'>Member not found!</span>";	     
  	}           
}

This doesn’t work. It returns false whether the string is in the DB already or not! :frowning:

#5

I can name a number reasons you might not get the ‘not found’ message. The absence of this message doesn’t mean that the submitted value was found, it just means that the logic never produced and output that message. Do you in fact have code following the posted code that is producing any output on the web page?

A list of problems that could be producing no message -

  1. Is the posted code even being executed? Can you echo something inside the posted code and see it being output? Are you detecting that a post method form was submitted before running any of the form processing code?
  2. Does the $username actually contain what you think? Are you validating the input data before using it?
  3. Could either the $username or the database column contain non-printing or white-space characters that would cause a mismatch?
  4. If the prepare() call fails, your current error handling logic doesn’t do anything, so you cannot distinguish between an error and the absence of the not found message. If you switch to use exceptions for errors and in most cases let php catch and handle the exception, database errors will get displayed or logged the same as php errors.
  5. Do you have php’s error_reporting set to E_ALL and display_errors set to ON, preferably in the php.ini on your system, so that php would help you find problems that could explain the wrong result?

Rather than posting this tail-end snippet of code, you basically have a whole web page that isn’t working. It would take seeing all the code needed to reproduce the problem in order to narrow down the possibility to just a few that can be investigated further.

1 Like
#6

Okay, here’s the entire script. :slight_smile: Thanks!

  $db = mysqli_connect('localhost', 'xxxx', 'xxxxx', xxxx');

  if (isset($_POST['register'])) {
  	$username = $_POST['username'];
  	$email = $_POST['email'];  	

 	if ($stmt = $db->prepare('SELECT * FROM xf_user WHERE email = ?')) {
	  $stmt->bind_param('s', $email);
        $stmt->execute();
	  $stmt->store_result();
  	  if($stmt->num_rows>0) {
           $mailuser_error = "<br><span class='error_msg'>E-mail belongs to existing member!</span>";	     
        }
       
	}

	if ($stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?')) {
	  	$stmt->bind_param('s', $username);
      	$stmt->execute();		
	      $stmt->bind_result($count);
      	$stmt->fetch();
  	  	if($count == 0) {
            	$user_error = "<br><span class='error_msg'>Member not found!</span>";	     
      	}           
	}
	
	if (empty($username)) {
    	  $name_error = "<br><span class='error_msg'>Username cannot be blank!</span>";
  	}else if((!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL, FILTER_NULL_ON_FAILURE)) or (empty($email))) {
 	  $email_error = "<br><span class='error_msg'>Invalid e-mail!</span>";  
  	}else if ((!isset($mailuser_error)) or (!isset($user_error))) {
	  $link = $base.$email;
	  $who2 = $base.$username;

	  $subject = "You have been invited by member $username to join";

	  $headers = "From: z <[email protected]>";

	  $message = "Behold, you've been invited by forum member $username \n
Proceed to the following secure link to continue, and you must mention the member who referred you: https://xxxxxxxxx/index.php?email2=$link&user=$who2\n
Regards,\n
zoldos - Admin";

	mail($email, $subject, $message, $headers);
	header('location: thanks.html');      
	}
  }
#7

It does look cool, but I’ve gotten used to Mysqli. :smiley:

#8

So what about the checklist that was posted? Don’t you want to work it out?

1 Like
#9

I just realized what is probably happening, after seeing the context of where the query is being executed at. After you complete items #4 and #5 on my list, you should be getting an error about commands out of sync. You must either fetch all the data from a prepared SELECT query or close the prepared statement after running one SELECT query, before you can run another one.

This is not how to test if a value already exists in a database -

  1. If you do have a case where you need to get a count of the number of matching rows, but don’t need the data that the query matched (typically done for things like pagination, not for deciding if data already exists), use a SELECT COUNT(*) … query, then fetch and test the count value.
  2. Just do what we have already told you multiple times in your previous thread and simply define unique indexes in the database table, attempt to insert the data, and detect if a duplicate key error occurred.
1 Like
#10

The html/script works, just the bit trying to send a message if an entered value does NOT exist is what I’m having problems with.

I have a few ideas tho that I’m gonna play around with.

#11

Okay thanks! I’ll see what I can do. :smiley:

#12

I’m giving this another try. Basically I am trying to see if the entered var “referral” exists in the DB and if it does, then continue, if NOT, then produce the error, and inform the user. It loads, but when I try to submit the code as a whole, I get a blank page. Not sure how to enable/check PHP errors. I was told this code simply produces a yes/no result, 1 if found, 0 if not.

Any tips would be appreciated!! :slight_smile:

$db = 'SELECT count(1) FROM xf_user WHERE username = ?';
$stmt = $mysqli->prepare($db);
$stmt->bind_param('s', $referral);
$stmt->execute();
$stmt->bind_result($found);
$stmt->fetch();
if ($found = 0) {
  $ref_error = "<span class='error_msg'>Referring username not found!</span>";
}

Thanks!

#13

As you are quering a database, you should expect $found to be an array, maybe an array of arrays, but you could debug nearly every variable with var_dump(), so have a look at that. And single equality signs assign data to variables, double equality signs make a comparison.

1 Like
#14

Okay, I changed it to this:

if ($stmt = $db->prepare('SELECT * FROM xf_user WHERE username = ?')) {
  $stmt->bind_param('s', $referral);
  $stmt->execute();
  $stmt->bind_result($found);
  $stmt->fetch();
  if ($found == 0) {
    $ref_error = "<br><span class='error_msg'>Referring username not found!</span>";
    }
}

No more blank screen, but, it’s returning the error whether the var is found in the DB or not! Also, how do I use var_dump? Thanks!

#15

you can use it like any other function, e.g. var_dump($found)

#16

Okay I tried this, altering the fetch statement (for testing purposes):

if ($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 (empty($found)) {
    $ref_error = "<br><span class='error_msg'>Referring username not found!</span>";
    }
    var_dump($found);
}

No blank pages, but it proceeds if rows are found or not. :frowning: Also, the echo didn’t work, (I used this later in the HTML portion of the script and it didn’t work):

<?php echo $found; ?>

:frowning:

#17

that’s totally unclear, did you have a look at what var_dump gives out? i can’t see it.

#18

The var_dump doesn’t show anything at all…

#19

then the code is not even executed. var_dump always has some output when run, at least

NULL
bool(false)
int(0)
string(0) ""
array(0) {
}
#20 
$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->fetchAll();
 if ($stmt->rowCount() > 0) {
     $ref_error = "<br><span class='error_msg'>Referring username not found!</span>";
 }
 var_dump($found);
Sponsor our Newsletter | Privacy Policy | Terms of Service