Welcome to WebmasterWorld Guest from 54.146.221.231

Forum Moderators: open

Message Too Old, No Replies

Query help

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

Junior Member

5+ Year Member

joined:June 30, 2009
posts:74
votes: 0


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)

Senior Member

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

joined:Dec 9, 2003
posts:3416
votes: 0


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)

Junior Member

5+ Year Member

joined:June 30, 2009
posts:74
votes: 0


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
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?

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members