CODEWITHSUNDEEP
phpmysql
MKC
MKC

Reputation: 188

PHP CMS Design - Implementing categories by referencing another table

I'm currently trying to develop a small CMS and I am having difficulty implementing a category system.

I currently have a loop that displays all rows from the table CLASSES.

When it gets to the CATEGORYID column for each row I need the CATEGORYID to match itself to the foreign key, ID from CATCLASSES, then display the NAME instead.

// connect to the database
include('../include/connect-db.php');

// get results from database
$result = mysql_query("SELECT * FROM classes") 
       or die(mysql_error());  


echo "<table border='1' cellpadding='10'>";
echo "<tr> <th>ID</th> <th>Title</th> <th>Summary</th> <th>Content</th> <th>Category</th> </tr>";

// loop through results of database query, displaying them in the table
while ($row = mysql_fetch_array($result)) {
    // echo out the contents of each row into a table
    echo "<tr>";
    echo '<td>' . $row['id'] . '</td>';
    echo '<td>' . $row['title'] . '</td>';
    echo '<td>' . $row['summary'] . '</td>';
echo '<td>' . $row['content'] . '</td>';
echo '<td>' . $row['categoryid'] . '</td>';
    echo '<td><a href="editClasses.php?id=' . $row['id'] . '">Edit</a></td>';
    echo '<td><a href="deleteClasses.php?id=' . $row['id'] . '">Delete</a></td>';
    echo "</tr>"; 
} 

// close table>
echo "</table>";

I'd be really grateful for any help you can provide, this one has had me stuck for a couple of days now!

Upvotes: 0

Views: 100

Answers (1)

Neograph734
Neograph734

Reputation: 1754

Have a look at mysql JOIN functionality. It will cost you only one query, rather then two and all values will be in one result set:

http://dev.mysql.com/doc/refman/5.7/en/join.html

Your query will look like this: 

SELECT * FROM classes LEFT JOIN catclasses ON classes.categoryid=catclasses.id;

Upvotes: 1

Related Questions