Mahbs Mahbs - 1 month ago 6
MySQL Question

How to return data from a joined table

I'm quite to using PHP, so please bare with me. The purpose behind this, is for me to be able to extract data from a table thats been formed via INNER JOIN of two other tables.

I've mapped a tariff name to a username successfully. But what i want to do is to be able to return data from this new table that was created via INNER JOIN in a text field on android studio.

My main concern at the moment is figuring out the right query to do be able to return the data. I've tried researching this but have had no luck/
I have 2 databases. A useraccount database and a tariff database.

useraccount database consists of the following columns:

ID
Name

Surname

EmailAddress

PostCode

City

PhoneNumber

Username

Password

ConfirmPassword
tariffs

And my Tariff database consists of the following column:

ID
Name

I have joined the two tables using INNER JOIN, and have linked the username column with the tariff name column, essentially, i ended up with a table like this:

Username|Tariff

Here is the code for that:


$query = "SELECT useraccount.Username, tariff.Name as tariffs
FROM useraccount
INNER JOIN tariff ON useraccount.tariffs = tariff.id";

$result = mysqli_query($conn,$query);
if($result->num_rows){
while($row = $result ->fetch_object()){
echo "{$row->Username} ({$row->tariffs}) <br>";
}
}else{
echo "No results";

}


$query2 = "SELECT";
$result2 = mysqli_query($conn,$query2);


Question:

How would i return data from the joined table?

Apologies for the long post, and thank you in advance to those who have taken the time to read this post! :)

This image shows the result of the execution of the PHP file

Answer

Joining two tables together does not create a third table, but creates a result set from the combined records of the two tables. In your code example, you already have access to all the data contained in the useraccounts and tariff tables. To gain access to this data, simply modify your select statement so that it references the desired columns. You could also just use the '*' wildcard to include all columns in your result set.

 // Using a wildcard to get all useraccount data. 
$query = "SELECT useraccount.*, tariff.Name as tariffs
FROM useraccount
INNER JOIN tariff ON useraccount.tariffs = tariff.id"; 

$result = mysqli_query($conn,$query);
if($result->num_rows){
    while($row = $result ->fetch_object()){      
        // Reference any user data you want.
        echo "{$row->Username} {$row->EmailAddress} {$row->PostCode} ({$row->tariffs}) <br>";
    }
}
Comments