sql query in php does not return the value in stored in the table but does correctly in phpmyadmin

I'm trying to set ID as a session variable however I cannot manage to retrieve the ID value from the 'Users' table using an SQL statement, the retrieved value is 1 no matter what field you try to SELECT e.g. I've tried with Firstname instead if ID in the SQL statement.

This is the example of code in my Php page:

$sql = "SELECT `ID` FROM `Users` WHERE `Username` = '$username' AND `Password` = '$password'";
echo $sql;
$result = mysqli_multi_query($link,$sql);
echo $result;`

On the webpage it returns:

SELECT `ID` FROM `Users` WHERE `Username` = 'x' AND `Password` = 'x'1

This shows that it has the correct SQL statement but the value returned should be the ID not 1. It could be that maybe it returns how many rows that contains those values?

However, when I put the same query into PhpMyAdmin (with the correct values for the variables) it returns the correct ID. ID is auto increment maybe this could have something to do with it?

Solution

mysqli_multi_query() is not the best choiche in your case. As documentation, it Performs one or more queries on the database. The documentation says that the return value of mysqli_multi_query() is false if the first statement failed (and let suppose that is true or it's a compatible value like the 1 you received, if the execution has not failed). From the documentation we know that For queries which produce a result set, such as SELECT, SHOW, DESCRIBE or EXPLAIN, mysqli_use_result() or mysqli_store_result() can be used to retrieve the result set.

I also notice that you are using string concatenation to compose the query, that can cause not wanted sql injection

This can be an example of how you can proceed:

<?php
//ini_set('display_errors', 1);
//ini_set('display_startup_errors', 1);
//error_reporting(E_ALL);

$sql = 'SELECT `ID` FROM `Users` WHERE `Username` = ? AND `Password` = ?';

$stmt = mysqli_prepare($link, $sql);
mysqli_stmt_bind_param($stmt, 'ss', $username, $password);

if (mysqli_stmt_execute($stmt)) {
    if (($result = mysqli_stmt_get_result ($stmt))  &&  mysqli_num_rows($result)) {
        $data = mysqli_fetch_array($result);
        $id = $data[0];
    }
}