Forum Moderators: open
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.
Could you post the queries you've tried so far for us to take a look at?
Ok, here's the code(only relevant to the queries):
//.. Get lasted tutorials
$query = "SELECT *
FROM tutorials_articles
ORDER BY a_date DESC
LIMIT {$numOfUser}";
//echo $query;
$db->setQuery($query);
$tutorial_list = $db->loadObjectList();
//Start array for latest tutorials
if(is_array($tutorial_list)){
foreach ($tutorial_list as $tutorial){
//Query: Gets each tutorials category information
$query = "SELECT *
FROM tutorials_cats
WHERE c_id='$tutorial->a_cat'";
//echo $query;
$db->setQuery($query);
$tutorial_cat = $db->loadObject();
//Query: Gets each tutorials author information
$query0 = "SELECT *
FROM members
WHERE member_id='$tutorial->a_mid'";
//echo $query;
$db->setQuery($query0);
$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?
