MattBlack MattBlack - 3 months ago 18
SQL Question

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;
$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();



?>





Any help much appreciated.

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.