1

Consider the following mysql query: SELECT a.*, b.* FROM a INNER JOIN b ON b.id = a.b_id. These two tables have identicaly named columns (id for example). I was under the assumption, that if I run this query with php, I will be able to access the fields from each returned row of the query the following way:

 $sql = "SELECT a.*, b.* FROM a INNER JOIN b ON b.id = a.b_id";
 $result = mysqli_query($connection, $sql);
 $row = mysqli_fetch_assoc($result));
 print_r($row);

My expected outcome from a similar script would need to be an array along the following lines:

array(
  'a.id'      => ...,
  'b.id'      => ...,
  'a.b_id'  => ...
)

Instead, what I get is something like this:

array(
  'id'      => ...,
  'b_id'  => ...
)

If I run the same query in mysql, I get the results from both tables in the result set (so columns with overlaping names aren't retrieved only once). Is there any way to simulate the same thing with php, and still maintain using wildcard in the query?

I am well aware of why wildcard should not be used in queries, but taking into account, that I will need to use ALL columns from these two tables in my script, and we are talking about a bunch of columns, I would prefer to use it this way.

Adam Baranyai
  • 3,635
  • 3
  • 29
  • 68
  • You should not use `*` anyway. It is error prone. – juergen d Jun 15 '20 at 07:22
  • @juegend "I am well aware of why wildcard should not be used in queries, but taking into account, that I will need to use ALL columns from these two tables in my script, and we are talking about a bunch of columns, I would prefer to use it this way." – Adam Baranyai Jun 15 '20 at 07:24
  • WIth PDO you can use https://stackoverflow.com/questions/21071353/pdostatementfetch-and-duplicate-field-names – Nigel Ren Jun 15 '20 at 07:24
  • @nigelRen The suggested solution does not solve my issue. I know that I can list all my column names, and alias them, I am interested in a solution, which auto prefixes for example the column names with the table they come from, using a wildcard. – Adam Baranyai Jun 15 '20 at 07:26
  • 1
    Sometimes, what you prefer and what is possible means you may have to change what/how you do things. If this is something you may want to do regularly, you could add some naming convention to your database tables to mean that columns have some form of prefix (which IMHO is not good). – Nigel Ren Jun 15 '20 at 07:26
  • @NigelRen no, I wouldn't want to do that, but this being an external project, which is already a complete mess, I have no way of resolving this issue in the correct way. The client wouldn't want to pay us for additional hundreds of hours of work, to make the whole thing at least OOP for example. So I have to work with what they brew. I was just looking for a possible solution for this issue, in which I don't need to list 30-40 columns manually in each query, and then copy them over each time I want to do a simmilar query. – Adam Baranyai Jun 15 '20 at 07:28
  • 1
    The above answer should work for PDO, not sure if https://stackoverflow.com/a/1417018/1213708 works for mysqli, you may have to try it. (although fetching fields by position is prone to errors if anything changes). – Nigel Ren Jun 15 '20 at 07:33
  • @AdamBaranyai: I read that. That is no reason in my opinion. – juergen d Jun 15 '20 at 09:15

0 Answers0