Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL problem.

Options
  • 21-11-2005 1:25pm
    #1
    sirpsycho
    Registered Users Posts: 876 ✭✭✭


    ("SELECT nickstats.nick AS ns, seeder, ip, port, uploaded, downloaded, to_go, UNIX_TIMESTAMP(started) AS st, connectable, UNIX_TIMESTAMP(last_action) AS la FROM (torrent_peers INNER JOIN nickstats_login ON torrent_peers.ip = nickstats_login.ip) INNER JOIN nickstats ON nickstats_login.nickstats_id = nickstats.id WHERE torrent = '$id'")

    I'm having problems running this query which links 3 separate tables. I'm using it in a .php page. Can anyone see any probs with it?

    I'm getting the following error:

    Error executing SQL Query SELECT nickstats.nick AS ns, seeder, ip,
    port, uploaded, downloaded, to_go, UNIX_TIMESTAMP(started) AS st,
    connectable, UNIX_TIMESTAMP(last_action) AS la FROM (torrent_peers
    INNER JOIN nickstats_login ON torrent_peers.ip = nickstats_login.ip)
    INNER JOIN nickstats ON nickstats_login.nickstats_id = nickstats.id
    WHERE torrent = '5192'
    Error ID: 1052
    Error Message: Column 'ip' in field list is ambiguous


Comments

  • Options
    #2
    drag0n79
    Registered Users Posts: 196 ✭✭drag0n79


    ("SELECT nickstats.nick AS ns, seeder, nickstats_login.ip, port, uploaded, downloaded, to_go, UNIX_TIMESTAMP(started) AS st, connectable, UNIX_TIMESTAMP(last_action) AS la FROM (torrent_peers INNER JOIN nickstats_login ON torrent_peers.ip = nickstats_login.ip) INNER JOIN nickstats ON nickstats_login.nickstats_id = nickstats.id WHERE torrent = '$id'")

    The IP field appears in (at least) 2 of your tables, so you've gotta explicitly say which one you want to SELECT.


  • Options
    #3
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭Beano


    Your query starts with

    SELECT nickstats.nick AS ns, seeder, ip

    you need to qualify the columns in the select list with the table alias if the same column exists in more than one of the tables selected from. Even if the column exists in only one of the tables it is still good practice.


  • Options
    #4
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭Beano


    damn, beaten to it.


  • Options
    #5
    Zaph0d
    Closed Accounts Posts: 756 ✭✭✭Zaph0d


    The SQL compiler doesn't know which table to use and it wants you to be more specific. Change ip in the first line to torrent_peers.ip or nickstats_login.ip depending on which one you want.


  • Options
    #6
    sirpsycho
    Registered Users Posts: 876 ✭✭✭sirpsycho


    Damn fast replies :) Thanks, i'll fix it up and let you know how I get on.


  • Advertisement
  • Options
    #7
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    As it says, the 'ip' field in the select query is ambiguous because it exists in more than one table or subquery.
    Try 
    ("SELECT nickstats.nick AS ns, sq.seeder, sq.ip, sq.port, sq.uploaded, sq.downloaded, sq.to_go, UNIX_TIMESTAMP(sq.started) AS st, sq.connectable, UNIX_TIMESTAMP(sq.last_action) AS la FROM (torrent_peers INNER JOIN nickstats_login ON torrent_peers.ip = nickstats_login.ip) sq INNER JOIN nickstats ON nickstats_login.nickstats_id = nickstats.id WHERE torrent = '$id'")
    This sets up an alias for the table/query in the FROM area, and tells the SELECT to explicitly choose the columns from that alias.

    It may need a bit of tweaking before it works.

    [Edit: LOL, well beaten to it]


Advertisement