Display results from one table if has record in another

3 posts by 2 authors in: Forums > CMS Builder
Last Post: July 28, 2010   (RSS)

By rjbathgate - July 27, 2010

Sorry, that subject is quite vague!

Two tables: News and Categories

Each News record has a Category assigned to it - i.e. linked list value.

On a page I want to display all categories, IF there is at least ONE news record assocaiated to that category.

At present, I do this by:

1) foreach category
2) list news records which have category num = this category
3) then, if there are no news records, continue to next category
4) else (ie if there are news records), then display category name

This works absolutely fine, but it has a large number of calls to the database, as for each category, it's calling the news table again and again.

So, I was wondering if there was a more efficient way to link the two tables without calling each one over and over again?

Or is this number of database calls not a problem to worry about?

Cheers!

Re: [rjbathgate] Display results from one table if has record in another

By Jason - July 28, 2010

Hi,

It depends on how many categories you have, but this is not a bad way of doing it.

In terms of making this more efficient, how are you checking to see if there are records in the news table? Are you returning a count of the records, or are you returning all of the records and then seeing if any exist? Just returning a count would be a lot more efficient. Here is an example of how you can do that:

<?php foreach($categoryRecords as $category): ?>
<?php $where = "category =".intval($category['num']); ?>
<?php $count = mysql_select_count_from('news',$where);?>
<?php if($count):?>
*OUTPUT CATEGORY NAME*
<?php endif ?>
<?php endforeach ?>

You would have to change names here to match what you have in your file and your database.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/