fetch SQL-join-statement without alias in PHP

Question

Following my code:

$list = $pdo->prepare("SELECT * FROM table_a INNER JOIN table_b ON table_a.id = table_b.blabla_id");
$list_result = $list->execute();
while($element = $list->fetch()) {
    //CONTENT
}

Now I would like to fetch the columns with something like echo $element['table_a.id']; - which doesn't work. I don't want to write an alias for every single column. Is there a way to deal with this? :)

SOLUTION:

$list = $pdo->prepare("SELECT * FROM table_a INNER JOIN table_b ON table_a.id = table_b.blabla_id");
$list->execute();
while($element = $list->fetch(PDO::FETCH_ASSOC)) {
    $a = [];
    $i = 0;
    foreach ( $element as $k => $v ) {
        $meta = $list->getColumnMeta($i);
        $a[ $meta['table'] . '.' . $k ] = $v;
        $i++;
    }
    echo $a['table_b.blabla'];
}

As kmoser mentioned, it's possible to improve the effectivity, as it's not necessary to check the column-names every loop, as they don't change.

Thanks to everyone.

`while($element = $list->fetch())` < you're not using the right variable here, which is most probably why your attempt failed. — Funk Forty Niner, Apr 13 '20 at 20:11
What's the status of this question? I posted a comment also; none have been tended to. You posted a comment [in your (what I thought was a duplicate) question](https://stackoverflow.com/q/61197381/1415724) and I reopened because I was following the question. "That", is also a form of responding, something to which I would appreciate the same courtesy/respect. Getting answers to questions is a two-way street. — Funk Forty Niner, Apr 13 '20 at 23:40
@FunkFortyNiner sorry, I did not want to appear unfriendly: It was midnight in my country and I wanted to check all your answers today (and - of course - reply afterwards). I just asked for reopening to hopefully get answeres over night :) so, please give me some hours so I can try your answeres in my code :) — TheHayek, Apr 14 '20 at 08:24
Hi @FunkFortyNiner thank you for your comment! Do you mean to change `$list` to `$list_result`? If I change that, it causes an "fatal error", as `$list_result` only contains a BOOL-value. Instead of `$list_result = $list->execute();` I could also write `$list->execute();`. - Thank you for reopening btw :) — TheHayek, Apr 14 '20 at 18:43
That is correct. The variable should be `$list_result` where you had `while($element = $list->fetch())`. If something failed in the query, you would need to check for errors on the query. It appears that you may be using PDO, correct? If so, check for errors with the syntax here in the manual https://www.php.net/manual/en/pdo.error-handling.php — Funk Forty Niner, Apr 14 '20 at 19:26

score 1 · Accepted Answer · answered Apr 13 '20 at 21:38

Once you've fixed your call to $list->fetch() by changing it to $list_result->fetch(), you can use $list_result->getColumnMeta($i) to get meta information (including the table name) of the column in position $i, where $i is the 0-indexed column in the result set.

You can then loop through the columns, retrieve their table names, and populate a new array with updated keys, and values from your original array:

while($element = $list->fetch()) {
    $a = []; // New array
    $i = 0;
    foreach ( $element as $k => $v ) { // For each element in the fetched row
        $meta = $list_result->getColumnMeta($i); // Get the meta info for column $i
        $a[ $meta->table . '.' . $k ] = $v; // E.g. $a[ 'table_a.id' ] = 'Foo'
        $i++; // Point to next column
    }
    $element = $a; // If you really need this variable name
}

Now you can use $element[ 'table_a.id' ].

You'll probably want to make my example more efficient by only looping through the meta info once, since the table names for each column will not change from row to row.

See https://www.php.net/manual/en/pdostatement.getcolumnmeta.php for more information.

Something similar is also described here: https://stackoverflow.com/a/15671366/378779 — kmoser, Apr 13 '20 at 21:43
Thank you so much! 1) I think `$list->fetch()` is correct, as `$list_result` just contains a BOOL (and would cause a "fatal error"; instead of `$list_result = $list->execute();` I can write `$list->execute();` as well.) 2) to get access to the `$meta`-Array, I have to change your code `$a[ $meta->table . '.' . $k ] = $v;` to `$a[ $meta['table'] . '.' . $k ] = $v;` 3) As my `$element`-Array sends every value twice (once with the column-name and once with an increasing number as key) I had to change your `$i++;` to `if(is_numeric($k)){$i++;}` | Can you maybe give me comments? - I'll update after — TheHayek, Apr 14 '20 at 18:38
You could change `fetch()` to `fetch( PDO::FETCH_ASSOC )` which will only give you back string keys (not numeric keys). That way you won't have to test `is_numeric($k)`. — kmoser, Apr 14 '20 at 19:17

fetch SQL-join-statement without alias in PHP

1 Answers1