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

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>";	     
          }           
}
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>";	     
      }           
}

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
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:

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

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 <zoldos@xxxxxxxxxx.net>";

	  $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');      
	}
  }

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

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

1 Like

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

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.

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

Sponsor our Newsletter | Privacy Policy | Terms of Service