[SOLVED] pulling variables with same name from joined tables

[j33baS]

okay iv got 3 tables set up in mySQL ...

 

(fixtures)

id <--- primary key

cat_id <--- foreign key

league_id <--- foreign key

versus

winlose

score

date

report

 

(categories)

id <--- primary key

cat

 

(leagues)

id <--- primary key

league

 

and my php...

 

<table border="1" align="center">
<?php
$titlequery = "SELECT fixtures.*, categories.*, leagues.* FROM fixtures, categories, leagues
WHERE fixtures.cat_id = categories.id AND fixtures.league_id = leagues.id
ORDER BY date ASC";
$titleresult = mysql_query($titlequery);
while($row = mysql_fetch_array($titleresult)){
echo "<tr>";
echo "<td width=\"70\">" . $row['cat'] . "</td>";
echo "<td>" . $row['league'] . "</td>";
echo "<td>" . $row['versus'] . "</td>";
echo "<td>" . $row['win/lose'] . "</td>";
echo "<td>" . $row['score'] . "</td>";
echo "<td>" . $row['date'] . "</td>";
echo "<td><a href='viewfixture.php?id=" . $row['id'] . "'>Report</a></td>";
echo "</tr>";
      }
    ?>
</table>

 

as you can see I have the results displayed in a table, that's working fine, but where I set up the link to the individual report for each entry.... $row['id'] .... identifies the individual entry, but it is currently taking the "id" from the "categories" table and NOT from the fixtures table which would uniquely identify the entry I am after!

 

is it something to do with the way i have joined the tables? cant think how to sort it out ! Any help is appreciated, thanks

[j33baS]

bump!

 

duno if i made it clear enough, but to reiterate,

 

this line

 

echo "<td><a href='viewfixture.php?id=" . $row['id'] . "'>Report</a></td>";

 

is taking $row['id'] from the categories table and i need to to be pulling "id" from the fixtures table.

 

Should i be renaming the id field in each table? or is there some way around it like selecting $row['fixtures.id']

 

im stumped and love any suggestions!

 

cheers

[trq]

You can use sql's AS to alias field names as you select them. eg;

 

SELECT foo AS bar FROM tbl