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

  php

Q(Question):

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

A(Answer):

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

A(Answer):

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>";
}
?>

A(Answer):

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.

A(Answer):

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

A(Answer):

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

A(Answer):

Thank you Rabbit the index part you mentioned needed to be changed 😀

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 $row[8]; echo "</td>";
echo "</tr>";
echo "</table>";
}
?>

LEAVE A COMMENT