How to retrieve data from MySQL PHP

Question

I am creating a detailpage for my website where the information will be retrieved from the databse(mysql). I created a small php page where it does retrieve everything from my person table exept from my address table. My person table has a relation with address table. But when I output my person_address table which is connected to address_id it just shows the id and not the information.

<?php
$servername = "localhost";
$username = "root";
$password = "usbw";
$dbname = "persons";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT person_id, person_firstname, person_lastname, 
               person_email, person_phonenumber, person_cv, 
               person_address 
        FROM person";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
    echo "id: " . $row["person_id"]. " -firstname: " . $row["person_firstname"]. "lastname " . $row["person_address"]. "email " . $row["person_email"]. "phonenumber " . $row["person_phonenumber"]. "cv " . $row["person_cv"]. "address " . $row["person_address"]."<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>

I am trying to program OOP and I hope you guys can help me. I am new to PHP and I know that my select query isn't good because I also need to select my address table but I am not able to change the query in a way that it will work. Of course later I will use Javascript and AJAX and i'll make a selection drop down menu where the admin can select address_city or address_state to show just the persons living in the selected area.

Answer 1

You are printing person_address here, which is ID in your person table.So you need to fetch the details from address table using this ID

For that you need to change your query:-

$sql = "SELECT person_id, person_firstname, person_lastname, 
               person_email, person_phonenumber, person_cv,  
               address_street,address_housenumber, 
               address_city,address_state,address_zipcode 
        FROM person 
           inner join address on address.address_id = person.person_address";