homepage Welcome to WebmasterWorld Guest from 54.226.80.196
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Query help
Marked




msg:4039860
 4:15 am on Dec 9, 2009 (gmt 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.

 

whoisgregg




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

Hello Marked! :)

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

Marked




msg:4041294
 12:43 pm on Dec 11, 2009 (gmt 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?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved