SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to select table names in field name

    Hi

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

    Example:
    Code:
    SELECT a.*, b.* FROM a, b WHERE a.fk = b.pk;
    Returns:
    Code:
    +-----+-----+----+----+-----+----+----+
    | apk | afk | a1 | a2 | bpk | b1 | b2 |
    +-----+-----+----+----+-----+----+----+
    But I need something like:
    Code:
    +-------+-------+------+------+-------+------+------+
    | 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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Vali View Post
    - I have to use SELECT table.*, I cannot list the fields here.
    in that case, the answer is no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Really? there's nothing like that \G command ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, not familiar with \G

    why are you required to use the dreaded, evil "select star" ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    command line? dude, i stopped using the command line when windows 2.0 came out in 1987

    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...
    Code:
    SELECT a.*
         , 'end of "a"' as a_marker
         , b.* 
         , 'end of "b"' as b_marker
         , ...
      FROM ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    command line? dude, i stopped using the command line when windows 2.0 came out in 1987
    lol, for linux mate... tx for your help.

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guessing you might be open to one of these:
    http://www.php.net/manual/en/mysqli-...etch-field.php
    http://www.php.net/manual/en/pdostat...columnmeta.php

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

  9. #9
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's the one, thank you!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •