How to replace int ID from one table with a string in another?

[Xicer]

Hello everyone i hope you can help me.

I have the following 2 tables.

albums: AlbumID, GenreID, AlbumTitle, AlbumAuthor, NumberOfSongs, Price, Rating.

rating: ratid(1,2,3,4,5 ) which is a primary key,rating(bad, ok,medium,good, great).

And here is the code for a page which is set up to give reults from these two tables:

<?php
include("db.php");

$mts=$_GET["mts"];
$Field=$_GET["Field"];


$query="SELECT * from albums WHERE $Field like '%$mts%'";

$res2=mysql_query($query);

while($row=mysql_fetch_row($res2))
{
        
echo "<table class=bottom_addr border=1 align=center cellpadding=30 cellspacing=4>"; 
echo "<tr>"; 
echo "<td width=50% align=center>"; echo $row[3]; echo "</td>";
echo "<td width=50% align=center>"; echo $row[2]; echo "</td>"; 
echo "<td width=50% align=center>"; echo $row[5]; echo " Euro"; echo "</td>";
echo "<td width=50% align=center>"; echo $row[1]; echo "</td>"; 
echo "<td width=50% align=center>"; echo $row[6]; echo "</td>"; 
echo "</tr>";
echo "</table>";

}

?>

Where $row[6] is Rating from albums which is showing me a number.

I want instead of the number to show the string from the rating table.

I hope i was clear enough.

Thanks in advance

[Rabbit]

INNER or OUTER join the albums table to the ratings table.

[Xicer]

Nope tried that here is what i did with the inner join and i don't get the desired result

<?php
include("db.php");

$mts=$_GET["mts"];
$Field=$_GET["Field"];


$query="SELECT * from albums INNER JOIN rating ON
albums.Rating = rating.ratid
WHERE $Field like '%$mts%'";

$res2=mysql_query($query);

while($row=mysql_fetch_row($res2))
{
        
echo "<table class=bottom_addr border=1 align=center cellpadding=30 cellspacing=4>"; 
echo "<tr>"; 
echo "<td width=50% align=center>"; echo $row[3]; echo "</td>";
echo "<td width=50% align=center>"; echo $row[2]; echo "</td>"; 
echo "<td width=50% align=center>"; echo $row[5]; echo " Euro"; echo "</td>";
echo "<td width=50% align=center>"; echo $row[1]; echo "</td>"; 
echo "<td width=50% align=center>"; echo $row[6]; echo "</td>"; 
echo "</tr>";
echo "</table>";

}

?>

[Rabbit]

I don't know if you can just use * when you have multiple tables. You may have to qualify the *.

If you don't have to qualify it and * works, then you'll need to change the index because the field you want isn't column 6.

[Xicer]

* seems to work ok with the inner join code i posted.
Could you show me in code how would changing the index look like

[Rabbit]

SELECT albums.*, rating.rating FROM ...

[Xicer]

Thank you Rabbit the index part you mentioned needed to be changed :D

Here is the change in code for reference

<?php
include("db.php");

$mts=$_GET["mts"];
$Field=$_GET["Field"];


$query="SELECT * from albums,rating WHERE albums.Rating = rating.ratid AND  $Field like '%$mts%'";

$res2=mysql_query($query);

while($row=mysql_fetch_row($res2))
{
    
echo "<table class=bottom_addr border=1 width=500 align=center cellpadding=20 cellspacing=4>"; 
echo "<tr>"; 
echo "<td width=50% align=center>"; echo $row[3]; echo "</td>";
echo "<td width=50% align=center>"; echo $row[2]; echo "</td>"; 
echo "<td width=50% align=center>"; echo $row[5]; echo " Euro"; echo "</td>";
echo "<td width=50% align=center>"; echo $row[1]; echo "</td>"; 
echo "<td width=30% align=center>"; echo [B]$row[8][/B]; echo "</td>"; 
echo "</tr>";
echo "</table>";

}

?>