Transpose a 2d array of column values from 3 different database tables

Question

I have three MySQL tables that I need to query for all of the rows. Upon getting all of the rows from each table, I need to create a multidimensional array whereby each index of that array contains only value from each of the tables. What I have right now works. But, something is telling me that there has got to be a better way of accomplishing this.

$tables = array('table_one', 'table_two', 'table_three');
$final = array();

foreach($tables as $table) {
    $sql = "SELECT * FROM ".$table."";
    $query = mysqli_query($con, $sql)or die(mysqli_error($con));
    $num = mysqli_num_rows($query);
    $i = 0;

    while($row = mysql_fetch_array($query)) {
        $id[$i] = $row['user_id'];

        $i++;
    }

    for ($i = 0; $i < $num; $i++) {
       if (!is_array($final[$i])) {
            $final[$i] = array($id[$i]);
       } else {
            array_push($final[$i], $id[$i]);
       }
    }
}

The end results is something that looks like this

$final = array(
    array('table_one_row_one_val', 'table_two_row_one_val', 'table_three_row_one_val'),
    array('table_one_row_two_val', 'table_two_row_two_val', 'table_three_row_two_val'),
    array('table_one_row_three_val', 'table_two_row_three_val', 'table_three_row_three_val')
);

I get the gut felling that this could be done much more effectively, but I'm not sure how.

Answer 1

You can make your code simpler if you make your query more explicit in selecting the columns you want in the order you want them. So for example:

$sql = 'SELECT table_one.user_id as u1, table_two.user_id as u2, table_three.user_id as u3 FROM ' . implode(',', $tables);

Then each row of your result set will have the columns in the proper order making the construction of your arrays less involved. For example:

$query = mysqli_query($con, $sql)or die(mysqli_error($con));

while($row = mysql_fetch_array($query)) {
    $final[] = array($row['u1'], $row['u2'], $row['u3']);
}

There may be issues with the order and relationships of the data in these arrays (especially if all 3 tables don't have the same number of rows), but working just off the information above, this is another way you could approach it. Something to think about anyway.