PHP CMS Design - Implementing categories by referencing another table

Question

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!

Also, if this is a new CMS then you really shouldn’t be using the mysql_ functions; they’re deprecated: php.net/function.mysql-connect — Martin Bean
– Martin Bean, Commented Dec 27, 2013 at 16:00
Quite right Martin, I'm more familiar with mysql so I'm planning on getting it functional with some of the deprecated mysql_ functions and will then convert to mysqli before going live. — MKC
– MKC, Commented Dec 27, 2013 at 16:39
Why not just use MySQLi from the get-go and get into the habit of it? You’re just creating more work for yourself then. — Martin Bean
– Martin Bean, Commented Dec 28, 2013 at 10:19

Neograph734 · Accepted Answer · 2013-12-27 15:58:17Z

1

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;

answered Dec 27, 2013 at 15:58

Neograph734

1,7642 gold badges18 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP CMS Design - Implementing categories by referencing another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related