SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Bulgaria
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Join return result

    Hi i am not entirely sure if this question is for here or for the php forum but i'll try here first. Here is my problem:
    select table1.*, table2.* from table1 left join table2 on table1.field1=table2.field1
    In php i receve a result:
    Array ( [field1] => 1 [field2] => something else )
    I want to receive result:
    Array ( [table1.field1] => 1 [table1.field2] => something [table2.field1] => 1 [table2.field2] => something else )
    What i need to do to get the result set i want? Are there any settings that switch the behaviour to what i want
    Thank you

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    definitely a php question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Bulgaria
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The reason i think it might be for here is that when i run that sql query in the mysql command line (or any other utility i've tried) it doesn't display "tableX." it displays:
    Code:
    +--------+-------------+--------+-------------------+
    | field1 |    field2   | field1 |        field2     |
    +--------+-------------+--------+-------------------+
    | 1      |  something  |   1    |   something else  |
    +--------+-------------+--------+-------------------+
    while it shoud display:
    Code:
    +-------------+-------------+-------------+-----------------+
    |table1.field1|table1.field2|table2.field1|  table2.field2  |
    +-------------+-------------+-------------+-----------------+
    |       1     | something   |      1      |  something else |
    +-------------+-------------+-------------+-----------------+
    or am i wrong? Is this normal?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, this is normal

    the result set of any query has column names which are derived from the tables, or else are column aliases assigned in the query, but there's no way they carry the name of the table they came from

    what you can do is assign column aliases --
    Code:
    select table1.field1 as t1f1
         , table1.field2 as t1f2
         , table2.field1 as t2f1
         , table2.field2 as t2f2
      from table1
    left outer
      join table2         
        on table1.field1
         = table2.field1
    then use t1f1, t1f2, t2f1, t2f2 in your php script
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Bulgaria
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again i tried to post in the PHP part of the forum but they pointed me back here so i'll continue here.
    I knew even before you told me that I can use "table1.field1 AS t1f1" the problem is that i have more than 100 scripts that i have to fix if i go that way...
    These scripts were written for sqlite and they are working but now i want to migrate them to MySQL so i am looking for a way not to fix all my sql queries (and the scripts with them) so i need some way to enable? php or mysql to return me result as "table1.field1" and not just as field1...
    I would appreciate any help at least if you can point me where to look for info.
    Thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    tell whoever it is in the php forum that it's a php issue, not mysql

    if you don't want to use aliases, then the column names will come back as field1, field2, field1, field2, ...

    there is no way the result set will have a tablename appended to the front of the column names

    if there is some way in php to access your columns positionally rather than by name, you could do that

    of course, that would be a change to your script as disruptive as using aliases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •