PHP Query MySQL Tables from IDs from other tables

Question

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.

Can you provide table structure, sample data and desired result? — fusion3k, Apr 17 '16 at 20:19
@Mihai that solution does not start by using an associative array. — CircAnalyzer, Apr 17 '16 at 20:28
@Mihai I try to flatten it as follows: $rowsIDs = array_values($rows); echo (int)$rowsIDs[0];, however not working. — CircAnalyzer, Apr 17 '16 at 20:38

score 1 · Accepted Answer · answered Apr 17 '16 at 20:31

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

PHP Query MySQL Tables from IDs from other tables

1 Answers1