SQL problem.

sirpsycho

("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

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.

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.

Beano

damn, beaten to it.

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.

sirpsycho

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

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]