Welcome to WebmasterWorld Guest from

Forum Moderators: open

Query help

4:15 am on Dec 9, 2009 (gmt 0)

5+ Year Member

Hi everyone,

I need help with writing a mysql query, which I THINK needs to use the join function, but I'm not sure.

The query is to get the latest articles from a specific category, which includes multiple sub-categories. For example, this is the category structure:

-Category 1
---Sub Category 1
---Sub Category 2
---Sub Category 3
-Category 2
---Sub Category 1
---Sub Category 2
---Sub Category 3

So I want to write a query to create a list of the latest articles, from Category 1(and all its sub categories) only. Make sense?

There are two tables we need data from: tutorials_articles & tutorials_cats.

Tutorials_articles have the following columns(only showing the relevant ones): a_id(the id of the article), a_cat(the id of the category the article is in) and a_updated(the date the article was created).

Tutorials_cats have the following columns(only showing the relevant ones): c_id(the id of the category), and then there is c_parent. In my example above, Category 1 would have the c_parent column empty(it is a parent/root category), but each of the sub categories would have a c_parent value of Category 1's c_id. That's the main point of this topic, I dont know how to write a query to display only articles from sub forums with a c_parent value of Category 1.

I hope I displayed all the relevant information and didn't intimidate anyone with the length of my post...

Thanks in advance.

9:33 pm on Dec 10, 2009 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member

Hello Marked! :)

Could you post the queries you've tried so far for us to take a look at?

12:43 pm on Dec 11, 2009 (gmt 0)

5+ Year Member

Sure, although I didn't attempt to do this...because i thought it needed to use the join function which im lost with.

Ok, here's the code(only relevant to the queries):

//.. Get lasted tutorials
$query = "SELECT *
FROM tutorials_articles
LIMIT {$numOfUser}";
//echo $query;
$tutorial_list = $db->loadObjectList();

//Start array for latest tutorials
foreach ($tutorial_list as $tutorial){

//Query: Gets each tutorials category information
$query = "SELECT *
FROM tutorials_cats
WHERE c_id='$tutorial->a_cat'";
//echo $query;
$tutorial_cat = $db->loadObject();

//Query: Gets each tutorials author information
$query0 = "SELECT *
FROM members
WHERE member_id='$tutorial->a_mid'";
//echo $query;
$author = $db->loadObject();

This is written with the Joomla framework which has its own functions. $db is the database obviously, it does the same as any code that runs a query. The top query is the only really relevant one to my problem. I need it to get the article info from one category only....

Does this help?


Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month