PHP: How to loop and skip over database tables without corresponding data in another table?

Question

I'm relatively new to PHP and still learning, and I recognize that this is a rookie question, but I'd love and appreciate any help that you can provide.

I'm currently working on coding a website that uses PHP and MySQL to pull and organize data from both a "courses" and "categories" database table. Each row in the "courses" table is associated with a particular course category through the column "category_id," which is linked to the relevant "id" column in the "categories" table.

So, for example, if category 3 in the "categories" table is "Music" and a particular course is on music, that course's "category_id" column value will be set to "3" in the "courses" table. (I hope that I explained all of that appropriately; my apologies if it's unclear).

I'd like to display all of the course category names that currently contain courses, and also display all of the course names within each of those categories underneath each category name. So, for example:

Music: (1) Rock, (2) Jazz
Painting: (1) Watercolor, (2) Acrylic

Here is the code that I'm using to do this. I'm using an MVC framework in which I've passed all of the data from both the "courses" and "categories" tables into this view as the associative arrays $categories and $courses.

<?php foreach ($categories as $category) : ?>

    <h4><?php echo ($category['name']); ?></h4>

    <?php foreach ($courses as $course) {

        if ($course['category_id'] == $category['id']) : ?>
            <p><?php echo ($course['name']); ?></p>
        <?php endif;
    }

endforeach; ?>

The problem that I'm running into is that I have category rows in my "categories" table for which I don't currently yet have any corresponding courses in the "courses" table. As I build out the website, this will change, but for now and for organization sake I don't want to delete those rows from the "categories" table.

So now, given the above code, I'm outputting a list of categories and courses that looks like this:

Dance:
Sculpture:
Music: (1) Rock, (2) Jazz
Painting: (1) Watercolor, (2) Acrylic

So finally, my question: how would I go about looping through each of the categories, and only displaying the category name if there are corresponding courses associated with that category_id in the "courses" table? (In reference to the example above, to skip over and not display the "Dance" and "Sculpture" categories because there aren't yet any courses associated with those categories).

Again, my apologies if anything above is unclear, and I very much appreciate anyone's help!

Best, Josh

provide sql queries how you load data

Tomáš Mafi Vinduška
– Tomáš Mafi Vinduška

2020-02-17 18:27:19 +00:00
Commented Feb 17, 2020 at 18:27 — Tomáš Mafi Vinduška
– Tomáš Mafi Vinduška, Commented Feb 17, 2020 at 18:27

Tomáš Mafi Vinduška · Accepted Answer · 2021-03-30 16:04:43Z

1

you can use something like this to select categories only with record in courses

select * from categories inner join courses where categories.id=courses.category_id group by categories.id

for better understanding https://www.w3schools.com/sql/sql_join_inner.asp

edited Mar 30, 2021 at 16:04

answered Feb 17, 2020 at 18:36

Tomáš Mafi Vinduška

777 bronze badges

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

1 Comment

Toryn Over a year ago

Thanks so much for that advice, Tomas!

Paul Spiegel · Accepted Answer · 2020-02-17 18:59:09Z

1

This isn't how I would do it my own code - But starting from what you already have, I would generate a nested array which maps category IDs to an array of corresponding courses:

<?php
    $coursesByCategory = [];
    foreach ($courses as $course) {
        $coursesByCategory[$course['category_id']][] = $course;
    }
?>

The array should look like:

[
    3 => ['Rock', 'Jazz'],
    4 => ['Watercolor', 'Acrylic']
]

Where 3 is the ID of Music and 4 the ID of Painting.

Then change your template code to:

<?php foreach ($categories as $category) : ?>
    <?php if (!empty($coursesByCategory[$category['id']])) : ?>

        <h4><?php echo $category['name']; ?></h4>

        <?php foreach ($coursesByCategory[$category['id']] as $course) : ?>
            <p><?php echo $course['name']; ?></p>
        <?php endforeach; ?>

    <?php endif; ?>
<?php endforeach; ?>

answered Feb 17, 2020 at 18:59

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

3 Comments

Toryn Over a year ago

Thank you, Paul! I really appreciate your insights. You mention that you'd set the code up differently from the get-go. I'm curious, if you're willing to explain, how you'd go about it. I really appreciate it, and am always looking to learn!

Paul Spiegel Over a year ago

@Toryn In short: Get only what you need from the DB (using a JOIN) and generate a single nested array in PHP, so you can use it like

foreach ($categories as $category) { echo "<h4>$category->name</h4>"; foreach ($category->courses as $course) { echo "<p>$course->name</p>"; }  }

.

Toryn Over a year ago

That makes sense. Thanks, Paul! I really appreciate the opportunity to learn from you.

Collectives™ on Stack Overflow

PHP: How to loop and skip over database tables without corresponding data in another table?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related