PHP get single value from MySQL with variable

Question

I can't seem to get simple query working to find UserID from the table of Users by UserEmail

I have simple function to suppose to return UserID functions.php

function get_userID($UEml) 
{
// Check database connection
  if( ($DB instanceof MySQLi) == false) {
    return array(status => false, message => 'MySQL connection is invalid');
  }
  $qSQL = "SELECT UsID FROM Users WHERE UsEml=? LIMIT 1";
  $qSQL = $DB->prepare($qSQL);
  $UEml = $DB->real_escape_string($UEml);
  $qSQL->bind_param("s", $UEml);
  $qSQL->execute();
  $result = $qSQL->get_result();
  while ($row = $result->fetch_row()) {
    return $row[0];
  }
//  return $row[0];
  if($qSQL) {
    return array(status => true);
  }
  else {
    return array(status => false, message => 'Not Found');
  }
}

and I call it from php script check-User.php

<?php
require_once("db-config.php");
include 'functions.php';
...
$UsID = get_userID("joe@example.com");
echo 'UserID: <span style="color: blue">'. $UsID ."</span>";
...
?>

db-config.php

<?php
// Two options for connecting to the database:

define('HOST_DIRECT', 'example.com'); // Standard connection
define('HOST_LOCAL', '127.0.0.1');    // Secure connection, slower performance

define('DB_HOST', HOST_DIRECT);         // Choose HOST_DIRECT or HOST_STUNNEL, depending on your application's requirements

define('DB_USER', 'dbUser');    // MySQL account username
define('DB_PASS', 'SecretPas');    // MySQL account password
define('DB_NAME', 'DBName');     // Name of database
 
// Connect to the database
$DB = new MySQLi(DB_HOST, DB_USER, DB_PASS, DB_NAME);

if ($DB->connect_error) {
  die("Connection failed: " . $DB->connect_error);
}
//echo "Connected successfully";
?>

I tried many variations, but no luck and also checked many similar posts here, but just can't get it working. Thanks

Don't escape data if you're using prepared queries. It's not necessary and can lead to corruption. — Tangentially Perpendicular, Jan 31 '21 at 20:26
@u_mulder so it's not enough to include it in the main php file. I tried put include into the function, but still no result — DenisZ, Jan 31 '21 at 20:30
I get nothing - no value- @GiacomoM even though SQL statement works. I put escape statement at the end when I was out of ideas what else is wrong — DenisZ, Jan 31 '21 at 20:31
Obviously `$DB` is not defined. Your question is very unclear since you should see something at least a notice — Dharman, Jan 31 '21 at 20:34
@Dharman when I removed escape statment I get result `#Array` — DenisZ, Jan 31 '21 at 20:46
Where is `$DB` meant to come from? Did you create mysqli connection anywhere? You need to pass it as a parameter to the function. — Dharman, Jan 31 '21 at 20:48
Yes I have a connection php file, where I define parameters and initiate db. I also have another function in **functions.php** which INSERT into db and is working fine. Something is wrong in returning array instead of string, but not sure how to return a string — DenisZ, Jan 31 '21 at 20:50
That was a problem .... pasing **$DB** to the function... Now is working. I somehow overlooked it — DenisZ, Jan 31 '21 at 21:06

Giacomo M · Accepted Answer · 2021-01-31T20:56:13.513

You have a couple of errors here:

$DB is not available in the function
the echo statement is wrong

This is the code without these 2 errors:

function get_userID($DB, $UEml) 
{
    // Check database connection
    if ( ($DB instanceof MySQLi) == false) {
        return array(status => false, message => 'MySQL connection is invalid');
    }

    $qSQL = "SELECT UsID FROM Users WHERE UsEml=? LIMIT 1";
    $qSQL = $DB->prepare($qSQL);
    $qSQL->bind_param("s", $UEml);
    $qSQL->execute();
    $result = $qSQL->get_result();
    while ($row = $result->fetch_row()) {
        return $row[0];
    }
    //  return $row[0];
    // I do not know why you wrote this code. If you get an user this code will not be executed
    if ($qSQL) {
        return array(status => true);
    } else {
        return array(status => false, message => 'Not Found');
    }
}

And your echo:

// ...
$UsID = get_userID($DB, "joe@example.com");
echo "UserID: <span style=\"color: blue\">{$UsID}</span>";

@Denis are you sure $DB variable is available in the PHP file? — Giacomo M, Jan 31 '21 at 20:54

PHP get single value from MySQL with variable

1 Answers1