Get record from 2 tables using PHP returning multiple results

I have 2 tables. One called local_requestors and one called local_aid. They both have different sets of data, but they both share a column, the column in local_requestors called suser_scheme is the same as the data in the local_aid table column called scheme_id.

I want to query my tables to get all data from the local_requestors table, where the ID matches that of the row in the local_aid table.

I’ve used the following…

$stmt = $conn->prepare('SELECT * 
                        FROM local_requestors 
                            INNER JOIN local_aid 
                        WHERE local_requestors.user_scheme=local_aid.scheme_id 
                        AND local_requestors.id = :aid_id');

$stmt->execute(['aid_id' => $user_id]);
while($row = $stmt->fetch()) {
    echo '<p>' . $row['user_name'] . '</p>';
}

This is all on a local page that I can only access, but to query the table I post some $_GET[] variables…

$user_id = $_GET['id'];
$scheme_id = $_GET['scheme_id'];

No matter what I try, I keep getting the same row from the local_requestors table, repeated around 10 times.

My goal is to get print the name of the user in the record from local_aid and a number of related records from local_requstors.

echo 'Hello ' . $aid_name . ', there is ' . $rowCount . ' people that need help in your area.;

Answer

I think you may have ment

SELECT * 
FROM local_requestors 
    INNER JOIN local_aid ON local_requestors.user_scheme=local_aid.scheme_id 
WHERE local_requestors.id = :aid_id

And from your description of what you want you may want a LEFT JOIN instead of a INNER JOIN

Advertisement

Answer