How to select table names in field name

Databases
1

Hi

Is there a way to append the table name to the names of the fields returned by a select?

Example:


SELECT a.*, b.* FROM a, b WHERE a.fk = b.pk;

Returns:


+-----+-----+----+----+-----+----+----+
| apk | afk | a1 | a2 | bpk | b1 | b2 |
+-----+-----+----+----+-----+----+----+

But I need something like:


+-------+-------+------+------+-------+------+------+
| a.apk | a.afk | a.a1 | a.a2 | b.bpk | b.b1 | b.b2 |
+-------+-------+------+------+-------+------+------+

Is this possible in MySQL? (or PHP)

Ps:

  • I have to use SELECT table.*, I cannot list the fields here.
2

in that case, the answer is no

3

Really? there’s nothing like that \G command ?

4

sorry, not familiar with \G

why are you required to use the dreaded, evil “select star” ?

5

I have a generic DTO model that selects everything in a table, and generates an object from it.

So, instead of always writing SELECT x,y,z WHERE … in all my pages, I only have: $obj = new Obj($id);

But, this object lazy loads other objects (related tables, only when needed), and sometimes when you know something will be used, I want to the object to preload it’s relationships, so it selects from more than one table at a time.

Problem is, I need to generate more than one object from each select (so select from 3 tables, and create 3 objects, 1 for each table), without having to list all the columns in the table inside my object (SELECT a., b. FROM a,b; will list all fields from table a first, and then those from b, so i can tell them apart, if and only if i know how many fields each table has, and in what order they are).

In other words, it simplifies allot of the code and makes just one place for optimizations/fixes instead of multiple places scattered across the app.

The \G command will display your results vertically instead of horizontally. (easier to read on command line),

6

command line? dude, i stopped using the command line when windows 2.0 came out in 1987 :slight_smile:

as for DTO, i have no idea what that means – i don’t even know what an object is or why it might be useful…

as for “if i know how many fields each table has, and in what order they are” try this…

SELECT a.*
     , 'end of "a"' as a_marker
     , b.* 
     , 'end of "b"' as b_marker
     , ...
  FROM ...
7

lol, for linux mate… tx for your help.

8

Guessing you might be open to one of these:
http://www.php.net/manual/en/mysqli-result.fetch-field.php
http://www.php.net/manual/en/pdostatement.getcolumnmeta.php

You might need the database name too though…which they don’t seem to provide.

9

That’s the one, thank you!