How to assign php variable in select statement where condition

Question

I tried various ways to get the id from database but I can't get that Please help me!

Below is my query :

$username = $_POST['username'];

$query = "SELECT id FROM wp_users WHERE user_login ='".$username."'";
$result=mysqli_query($conn, $query);
if(mysqli_num_rows($result) == 1)
{
    while($row = mysqli_fetch_array($result))
    {
        $id = $row['id'];
    }
}

echo $id;

Also I tried this:

$query = "SELECT id FROM wp_users WHERE user_login ='$username'";

You are only testing for 1 result. `> 0` is a better test. Also your `echo` is outside the loop, so that would only echo the last `$id` in the loop. You should also test for `isset($_POST['username']){ /* put everything in here */ }`. Hopefully you're sending your username to the Server via JavaScript AJAX. — StackSlave, Nov 27 '19 at 02:57
Thanks for your kind update. I have also tried >0 in if statement but it is also not showing the results. — surya, Nov 27 '19 at 03:31

waterloomatt · Answer 1 · 2019-11-27T11:23:17.100

You should, at the very least, be using prepared statements in your queries if you're passing them user-supplied data.

use prepared statements ? and bind_param
use bind_result to bind the column to a variable. This variable is now bound by reference which means it will be updated on every iteration of the loop.
it is important to realize that you want to access the $id variable inside the loop as you're iterating over the dataset. If you use it outside/below the loop you are only working with the final row of data because it is being overwritten on every iteration.
turn on error reporting

Finally, I left your loop in place but usually, you'd only have a single user for a given username so you could use mysqli_fetch_assoc - Single Result from Database by using mySQLi

<?php
ini_set('display_errors',1);
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$username = $_POST['username'];

$query = "SELECT id FROM wp_users WHERE user_login = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param('s', $username); 
$stmt->execute();
$stmt->bind_result($id);

while ($stmt->fetch()) {
    echo "ID: $id\n";
}

$stmt->close();

while ($stmt->fetch()) { echo "ID: $id\n"; } while is not executing the result. when I put echo $stmt->bind_result($id); it shows the result as 1. But while loop is not printing the ID. — surya, Nov 27 '19 at 03:43

How to assign php variable in select statement where condition

1 Answers1