PHP Loop through results

I am trying to loop through my database and check to see if the user already exists in another table. If they do then I want to increment a value, if they don’t then I want to add the user.

When I run the code below it happily loops through all the results:

<?php

$servername = "p:10*********";
$username = "*******";
$password = "*******";
$dbname = "******";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM payroll WHERE user != ' ' ";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo $result->num_rows;

    while($row = $result->fetch_assoc()) {
        $user = $row['user'];
        $time = $row['time'];
        $id = $row['id'];
        echo $id;
        echo $user;
    }
} else {
    echo "0 results";
}
$conn->close();

?>

However when I add in the SQL to check to see if they exist in the other table the loop no longer functions correctly and echos the same user each time.

<?php

$servername = "*******";
$username = "******";
$password = "********";
$dbname = "*****";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM payroll WHERE user != ' ' ";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo $result->num_rows;

    while($row = $result->fetch_assoc()) {
        $user = $row['user'];
        $time = $row['time'];
        $id = $row['id'];
        echo $id;
        echo $user;

        // Added existing user check:
        $sql = "SELECT * FROM smsreport WHERE user = '$user'";
        $result = $conn->query($sql);

        if ($result->num_rows > 0) {
            echo "found";
        } else {
            echo "USER NOT FOUND";
        }
    }
} else {
    echo "0 results";
}
$conn->close();

?>

Answer

In the open eye: Rename the inside $result variable. It is over writting the first $result.

It could be the problem. Not tested though.

Advertisement

Answer