Linking data from one DB table with another to display full result

[LiamH]

Not a good night for me tonight. 

 

I have two tables in my DB. One contains article data another contains category data. I have a create article page which has a drop down list displaying all of the categories stored in the DB. When the user submits the article it looks at what category was selected and stores the ID for that category in the articles table. That works fine.

 

The problem I am having is when it comes to viewing the data in a page. I have a table that brings up all of the article information. I want to display the category that was selected for the article, which means translating the id stored in the articles table into the actual category name stored in the category table. How do I do this? Thanks

[LiamH]

If anyone can point me in the direction of an article as well, that would be great. I don't know what I'm looking for which is the problem I'm having more than anything!

[LiamH]

Right I've realised it was a JOIN that was required. And in a way have it working.

 

I have the two tables that are joined. Table A contains article information with just an id number for a category. Table B contains category information, with the id from table a corasponding to a category name in table b. So for example categoryid 1 in table A would link with categoryid 1 in table b which would be general interest.

 

The result I get I wasn't really expecting. I am getting a double copy of the category name, so it's printing general interest general interest. I'm not sure why, I've played around with the code but the only change I have managed is for it to print the category name 3 times instead of 2. Is it possible to just print it once?

 

$query = "SELECT a.CategoryID, b.CategoryName FROM tblA a LEFT JOIN tblB b ON a.CategoryID = b.CategoryID";
//I have also tried
//$query = "SELECT a.CategoryID, b.CategoryName FROM tblA a LEFT JOIN tblB b ON a.CategoryID = b.CategoryName";

$result = mysql_query($query) or die("Query failed: ".mysql_error()); 
   
   if (mysql_num_rows($result) > 0) { 
   echo "<table>";
   while($row = mysql_fetch_assoc($result)) {
   echo "<tr>";
   echo "<td>".$row['CategoryName']."</td>";
//Also tried .$row['CategoryID']
   echo "</tr>";
   
       }
    echo "</table>";
} 
else { 
    // no 
    // print status message 
    echo "No rows found!"; 
}

 

Could anybody point me in the direction I have gone wrong? Is it even possible for it to just return the result once?