SQL question

Question

<?php


$query = mysql_query("SELECT * FROM threads
                      INNER JOIN users
       ON threads.poster = users.id
       WHERE threads.forum_id = 11");

while($row = mysql_fetch_array($query)) {


<a href="thread.php?id=<?=$row['id']?>">Link</a>

} ?>

Theres a column named id in both threads and users. It prints the user id. Any way i got get it to print the thread id without re-naming one of them.

Thanks in advance!

Answer 1

SELECT *, threads.id as thread_id FROM ...

Answer 2

SELECT *, threads.id AS thread_id ...

Then you can reference thread_id from $row.

$row['thread_id']

Answer 3

you'd need to name the column explicitly. If you put in "SELECT USERS.*, THREADS.ID, USERS.ID FROM threads INNER JOIN users ON threads.poster = users.id WHERE threads.forum_id = 11" That ought to do it.

Answer 4

Why are you using * in a query? It's better to only call the fields you need, than to pull extra data that is not needed. Also you need to specify which ID you want to use by either calling the table_name.field_name format as listed above.

What I like to do when you start getting some complex joins is to alias the table names with short letters. Just help simplify things a bit.

SELECT t.ID FROM threads t INNER JOIN users ON t.poster = users.id WHERE t.forum_id = 11