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!
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:
You would have to change names here to match what you have in your file and your database.
Hope this helps.
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/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Display results from one table if has record in another
By rjbathgate - July 28, 2010
Hey,
Thanks for the reply.
Your code does look alot simplier.
I'm returning all records and then applying a if none exist... so your count sounds better.
Thanks heaps,
Rob
Thanks for the reply.
Your code does look alot simplier.
I'm returning all records and then applying a if none exist... so your count sounds better.
Thanks heaps,
Rob