How can I get an unknown username given an ID?

I got a numerical ID of 20 characters witch looks like 10527391670258314752, given this ID, how can I get the username associated with it?

The table looks like this:

id                   | name | password | balance   
10527391670258314752 | Jhon | 12345    | 12.51

The username retrieved from the database should then be stored into $_SESSION['name'].

I've tried this:

$connection = mysqli_connect('localhost', 'root', '', 'user_data');
$id = '10527391670258314752';

$query = "SELECT username FROM user_data WHERE id = '$id'";
$result = mysqli_query($connection, $query);

$record = mysqli_fetch_array($id);
$_SESSION['id'] = $record;

echo $_SESSION['id'];

The output is Array() instead of the name.

Solution 1:

That's actually a very good question that has almost no good answers on Stack Overflow.

Basically you need the following steps to perform a SELECT query using mysqli:

• create a correct SQL SELECT statement and replace all variables in the query with with question marks (called placeholders or parameters)
• Prepare the resulting query
• Bind all variables to the previously prepared statement
• Execute the statement
• get the mysqli result variable from the statement.
• fetch your data

The detailed explanation can be found in my article, How to run a SELECT query using Mysqli, as well a helper function to simplify the routine.

Following this plan here is your code

$sql = "SELECT * FROM users WHERE id=?"; // SQL with parameters
$stmt = $conn->prepare($sql); 
$stmt->bind_param("s", $id);
$Stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$user = $result->fetch_assoc(); // fetch the data 

now you can store the username in the session variable:

$_SESSION['name'] = $user['name'];

Solution 2:

I would strongly recommend that you avoid the mysqli extension. You should use some database abstraction library instead of using the mysqli functions directly; the mysqli class is not suited to be used on its own.

If you really want to do it purely with the mysqli class, you have few options.

First, you need to open the connection properly. These 3 lines ensure you have the connection ready:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // enable error reporting
$mysqli = new mysqli('localhost', 'username', 'password', 'dbname');
$mysqli->set_charset('utf8mb4'); // always set the charset

Then you need to prepare a statement, bind the parameter and execute it.

$id = '10527391670258314752';
$stmt = $mysqli->prepare('SELECT username FROM user_data WHERE id=?');
$stmt->bind_param('s', $id);
$stmt->execute();

Once the statement is executed you need to fetch the results. If you have only one variable you could simply use bind_result()

$stmt->bind_result($_SESSION['name']);
$stmt->fetch();
echo $_SESSION['name'];

However, this approach is not recommended and not very flexible. Instead it's better to fetch the whole result set and get an array containing the first row.

$result = $stmt->get_result();
$row = $result->fetch_array();
$_SESSION['name'] = $row['username'];
echo $_SESSION['name'];

As an alternative with PDO the same code would look like this:

session_start();

$pdo = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass, [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES => false,
]);

$id = '10527391670258314752';

$stmt = $pdo->prepare('SELECT username FROM user_data WHERE id=?');
$stmt->execute([$id]);
$_SESSION['name'] = $stmt->fetchColumn();
echo $_SESSION['name'];