CODEWITHSUNDEEP
phpmysql
Masoman
Masoman

Reputation: 85

Regarding calling how many categories are in each table in php

I am creating a school type CMS system in php to get more experience. I created a schedule.php page where it lists the school categories, for example:

Accounting Biology Math Computer Science

I want to add put a number next to the category Accounting(2) coming from the database because there is 2 accounting classes. for example Computer Science has 10 classes taught so it should be Computer Science(10)

My code only dispalys the name of the subject not the number of classes taught.

here is my code:

<?php


include('connect.php');

if(!isset($_GET['id']))
{
    ?>
    <table border="1" width="15%" height="137">
<tr>
    <td><?php

    //$query1 = mysql_query("SELECT * FROM categories ORDER BY category_name") or die(mysql_error());


    $query1 = mysql_query("SELECT CONCAT(a.category_name, ' (', COUNT(b.category_id), ')') AS category

FROM categories a LEFT JOIN classes b ON a.id = b.category_id GROUP BY a.id, a.category_name") or die(mysql_error());

    while($row = mysql_fetch_object($query1))
    {
        print "<a href='schedule.php?id=" . $row->id .  "'>" . $row->category . "</a><br/>";
    } ?></td>
</tr>


    <?php


}
//if you click on the subject
else if(isset($_GET['id']))
{
    $category = $_GET['id'];
    $sql = "SELECT * FROM classes WHERE category_id = " . $category;
    $query2 = mysql_query($sql) or die(mysql_error());


    while($row = mysql_fetch_object($query2))
    {
    ?>

    <center><h3><?php echo $row->class_name . '-' . $row->units; ?>  </h3></center>
    <table border ="0" wdith="100%">
    <tr>
    <td>
    <strong>Description: </strong>
    <?php echo $row->class_description; ?>
    </tr>
    </td>
    </table>
    <br/>
    <table border="1" width="44%">
<tr>
    <td width="60"><b>Ticket</b> </td>
    <td width="123"><b>Days</b></td>
    <td width="120"><b>Hours</b></td>
    <td width="64"><b>Room</b></td>
    <td><b>Instructor</b></td>
</tr>


    <tr>
    <td width="60"> <?php echo $row->ticket; ?> </td>
    <td width="123"><?php echo $row->days; ?></td>
    <td width="120"><?php echo $row->start_hours . $row->time_format . '-' . $row->end_hours . $row->time_format2 ;  ?> </td>
    <td width="64"> <?php echo $row->room_number; ?></td>
    <td><?php echo $row->instructor_name;  ?></td>
</tr>


    }//end while

}//end else if

?>

Thanks

Upvotes: 1

Views: 93

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

Assuming your schema is along the lines of:

+---------------+     +--------------------+
| categories    |     | classes            |
+---------------+     +--------------------+
| ID            |     | id                 |
| category_name |     | category_id        |
| etc..         |     | etc...             |
+---------------+     +--------------------+

Where classes.category_id is a foreign key referencing categories.id, there is quite a simple solution:

SELECT a.ID, CONCAT(a.category_name, ' (', COUNT(b.category_id), ')') AS category
FROM categories a
LEFT JOIN classes b ON a.ID = b.category_id
GROUP BY a.ID, a.category_name

This will concatenate the count of classes to each category. Ex.

Biology (4)
Computer Science (10)
etc...

And you can simply reference the category column of the query in your PHP.

while($row = mysql_fetch_object($query1))
{
    print "<a href='schedule.php?id=" . $row->ID .  "'>" . $row->category . "</a><br/>";
}

Upvotes: 1

ghbarratt
ghbarratt

Reputation: 11711

It will probably be best to use a query with a join and group by clause. It might look like this:

SELECT 
    ca.*, 
    COUNT(cl.id) AS class_count 
FROM 
    categories ca 
    LEFT JOIN classes cl ON cl.category_id = ca.id 
GROUP BY ca.id

Upvotes: 1

Related Questions