Help retrieving username from table based on submitted e-mail

[zoldos]

This is my current code. It correctly connects to the DB, but even if I enter an e-mail that does exist in the xf_users table, it always echos “Account not found”. All I need it to do is fetch the userrname associated with the e-mail the user submits in a form and put it in $username for use later:

  $stmt = $db_site->prepare('SELECT * FROM xf_user WHERE email = ?');
  $s1tmt->bind_param('s', $email);
  $stmt->execute();
  $stmt->store_result();
  if($stmt->num_rows>0) {
  	$stmt->bind_result($username);
 	$stmt->fetch();
    echo $username;
    exit();
  } else {
  	echo "Account not found!";
  	exit();
  }

Any help would be most appreciated! I bet I need to declare $username. But I’m just not sure how to fix it…

[phdr]

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 will help you by reporting and displaying all the errors it detects?

Next, do you have error handling for all the database statements that can fail? If you are using php8+, php has finally set the default error mode for the msyqli extension to use exceptions for errors. This, combined with the php error settings above will ‘automatically’ cause the raw database errors to be displayed/logged the same as php errors.

Lastly, if you switch to the much simpler and more modern PDO extension, about half of that code will go away. PDO version -

$sql = 'SELECT username FROM xf_user WHERE email = ?';
$stmt = $pdo->prepare($sql);
$stmt->execute([ $email ]);
if($username = $stmt->fetchColumn())
{
	echo $username;
}
else
{
	echo "Account not found!";
}

[zoldos]

I’m using PHP 7 as I have code in another script on that domain which is deprecated on PHP 8.

Can I use this PDO version with my other MySQL code? Or does it all have to be one or the other?

In your code snippet, how do I select the database?

[phdr]

To enable exceptions for errors for the mysqli extension, before php8, add the following line of code before the point where you make the database connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

You can use the PDO extension along side the mysqli extension, by making a second connection using the PDO extension, but once you see how simple it is to use, you will want to convert all your code to use the PDO extension. Here is typical PDO connection code -

$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions (this is the default setting in php8+)
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries whenever possible
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // set default fetch mode to assoc so that you don't need to specify it in each fetch statement
			];
$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

[zoldos]

Wow, worked perfectly! And faster too! Thanks!