PHP Relational Databases

I'm still a beginner at PHP and having worked all day so far on other things my brain has kind of hit capacity, so apologies if this is easy.

Anyway, my table is set up so I have

library
filename
artist name
track name
catid

then I have a category table
id
category

My catid input is set to use the different fields that are in category input.


But how do I display the category name when I specify the library catid?

My code currently is as follows

// Get all the data from the "library" table
$result = mysql_query("SELECT * FROM library") 
or die(mysql_error());  

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
    // yes
    // print them one after another
    echo "<table>";
    while($row = mysql_fetch_row($result)) {
        echo "<tr>";
        echo "<td>".$row[1]."</td>";
        echo "<td>".$row[2]."</td>";
        echo "<td>".$row[3]."</td>";
        echo "<td>".$row[4]."</td>";
        echo "</tr>";
    }
    echo "</table>";
}
else {
    // no
    // print status message
    echo "No rows found!";
} 

PHP:

row4 is the catid in the library database. How do I get this bit to show the category name?

Thanks in advance,
Ed

 

You do an extra SELECT inside your WHILE, like 'SELECT * FROM `category` WHERE `id` = $row[4]'.

 

You can do this with one query.

$query = <<<QUERY
select
    library.filename,
    library.artist_name,
    library.track_name,
    library.catid,
    category.id,
    category.name
from
    library, category
where
    library.catid = category.id
QUERY;

PHP: