CODEWITHSUNDEEP
phpmysql
CircAnalyzer
CircAnalyzer

Reputation: 704

PHP Query MySQL Tables from IDs from other tables

I have a database with several tables. I'm able to query IDs from a single table. What I'd like to do is Use those IDs to query another tables IDs, then use these new IDs to query fields from the final table. Here is what I am currently doing:

Here is how I acquire the first set of IDs:

$returnedPost = mysqli_query($con, "SELECT Region_ID FROM Region WHERE RegionName='" . $queryVar . "'");

function resultToArray($result) {
    $rows = array();
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
   }
    return $rows;
}
$rows = resultToArray($returnedPost);
//$rows[x]['Region_ID'];//returns Region_ID 1...n

I'd like to use the IDs in $rows to be able to query a new set of IDs from other tables as follows:

$newTbl = mysqli_query($con, "SELECT Location_ID FROM Location WHERE Region_ID=" . $rows[$x]['Region_ID']);
$rows2 = resultToArray($newTbl);
$finalTbl = mysqli_query($con, "SELECT Field1, Field2 FROM Posts WHERE Location_ID=" . $rows2[$x]['Location_ID']);

Can someone please tell me how I can accomplish this? Thanks.

Upvotes: 0

Views: 35

Answers (1)

Tin Tran
Tin Tran

Reputation: 6202

you can use INNER JOIN in one query to get at this data, maybe something like this

SELECT P.Field1,P.Field2
FROM Region R
INNER JOIN Location L ON R.Region_ID = L.Region_ID
INNER JOIN Posts P ON L.Location_ID = P.Location_ID
WHERE R.RegionName = Your_Region_QueryVar

Upvotes: 1

Related Questions