为什么我的mysqli_fetch_assoc没有抓取行信息，所以我可以在我的表中插入细节？

Question

First off, I know about sql injection and that my code is not foolproof, prone to injection etc. Will be working on that next.

Now : from my Android app to my PHP file I submit a JSON array of phone numbers like :

[{"phone_number":"[phone]"},
 {"phone_number":"[phone]"},
 {"phone_number":"34567890"},
 {"phone_number":"45678901"} 
 etc... etc...

These are contacts in my app user's phone. If these contacts are people who are also users of my app then I want to insert those numbers into my contacts table.

But I can't get it to work. mysqli_fetch_assoc isn't working correctly. I don't know why.

In my contacts table I have 3 columns - an auto increment, user_id and contact_id. The first two values are inserted correctly but the contact_id is always put in as '0', which is wrong.

Here is my code :

require('dbConnect.php');

//this is me, [phone], my user_id in the user table
$user_id = '20';

//post all contacts in my phone as a JSON array
$json  = $_POST['phonenumber'];
$array = json_decode($json);

foreach ($array as $value) {
    $phonenumber = $value->phone_number;

    $sql      = "SELECT username FROM user WHERE username = '$phonenumber'";
    $result   = mysqli_query($con, $sql);
    $num_rows = mysqli_num_rows($result);

    if ($num_rows > 0) {
        echo "phonenumber is " . $phonenumber . "<br>";

        // we want to put $phonenumber in the contacts table, as one of [phone] contacts
        // In the user table get the associated rows of $phonenumber
        while ($row = mysqli_fetch_assoc($result)) {
        // get the associated user_id in that row, that's what we want to put into the contacts table
            $contact_id                   = $row['user_id'];
            $insert_into_contacts_command = "INSERT INTO contacts VALUES(NULL, '$user_id','$contact_id')";
            $insert_into_contacts_table   = mysqli_query($con, $insert_into_contacts_command);
        }

    } //if [phone] is NOT in the user table...
    else {

        echo 'not a match.';
    }
}

Answer 1

Your query selects the column username, not userid.

You haven't posted anything about the table user, so it's hard to suggest a new query, but I guess it's the following:

$stmt = mysqli_prepare($con, "SELECT userid FROM user WHERE username = ?");
$stmt->bind_param("s", $phonenumber);
$stmt->execute();
$stmt->bind_result($userid);

while ($stmt->fetch()) {
    // Work with $userid
}

You'll note that this uses a prepared statement with a bound parameter. That way, your code is not prone to SQL injections.