Get PHP MySQL PDO column table name as a result of joining

Question

I have fields that have the same name in different tables that I join. For example, ticket.status , user.status and transaction.status . Currently, the request returns only status .

How can I get the name of a table so that it overlaps similar field names from being overwritten and so I can distinguish between fields.

Simply put:

 $data = array($eventId); $statement = $this->db->prepare("SELECT * FROM ticket, user, transaction WHERE ticket.eventId = ? AND ticket.userId = user.userId AND ticket.transactionId = transaction.transactionId"); $statement->execute($data); $rows = $statement->fetchAll(PDO::FETCH_ASSOC); 

In my research, I found the constant PDO::ATTR_FETCH_TABLE_NAMES , which looks like it can help, but I don’t know how to implement it (I assume through $statement->setAttribute(); somehow).

I also have concerns that this will not work, as the PHP documentation mentions that it depends on the driver.

thanks

Answer 1

Just add new aliases to your select statements

 $statement = $this->db->prepare(" SELECT *, ticket.status AS ticket_status, user.status AS user_status, transaction.status AS transaction_status FROM ticket, user, transaction WHERE ticket.eventId = ? AND ticket.userId = user.userId AND ticket.transactionId = transaction.transactionId "); 

Then you can do

 $rows[0]['user_status']; $rows[0]['ticket_status']; $rows[0]['transaction_status']; 

If you are really concerned about performance, the amount of data returned will be greater, so instead of adding new aliases, you can select each individual column, and while you do this, add an alias to the status column.

Answer 2

Why not change the actual connection instead:

 SELECT t.status as ticket_status, u.status as user_status, tr.status as trans_status FROM ticket as t inner join user as u on t.userId = u.userId inner join transaction as tr on t.transactionId = tr.transactionId where t.eventId = ? 

You don't even need to throw tables with as something , but I find it tidier.

Note that this is a casting of columns that will actually fix this problem, not a join method.

Answer 3

The most obvious comment is "don't do this so that pseudonyms exist." But there is still a good basic question: does MySQL output information about where the column with the result comes from (table, view or calculation)?

This seems to be happening because the PDOStatement object has an experimental method called getColumnMeta () . I tested and returns an associative array where the key is table

• contains the source table if the column comes from a table or view
• - empty row if column is being calculated

Of course, I still followed pseudonyms. Being able to use associative arrays is a killer feature for me.