如何将MySQL行拆分为连接中每个表的单独变量

Question

If I am fetching results from a JOIN query, how do I get separate variables for each table in the join per each row?

For example:

$result = $mysql->query(sprintf('SELECT *
    FROM `TableOne` one
    JOIN `TableTwo` two ON (one.TwoID = two.TwoID)
    JOIN `TableThree` three ON (one.ThreeID = three.ThreeID)
    WHERE one.OneID = %u',
    (int)$primary_key));
while ($row = $result->fetch_assoc()) {
    // Instead of $row, I want:
    // $one: all fields from TableOne
    // $other: all fields from TableTwo and TableThree
}

Answer 1

Solution:

function mysqli_fetch_assoc_per_table(mysqli_result $result, $instruction) {
    $row = $result->fetch_assoc();
    if (!$row) return false;
    $fields = $result->fetch_fields();

    $groups = array();
    foreach (explode(',', $instruction) as $arg) {

        $group = array();
        foreach (explode('|', $arg) as $table) {
            foreach ($fields as $field) {
                if ($field->table == $table) {
                    $group[$field->name] = $row[$field->name];
                }
            }
        }

        $groups[] = $group;
    }

    return $groups;
}

Usage:

while (list($one, $other) = mysqli_fetch_assoc_per_table($result, 'one,two|three')) {

}