SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2002
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql select help

    Hello Everyone,

    I'm new, please be kind :-). I am writing a script to store a small knowledge base. I have a database structured as so, can you help me with the first select statement?

    articles ->
    article_id, category_id, staff_id, modified, flash, read_times, title, body

    articles_related ->
    article_id, related_id

    categories ->
    category_id, category_name, totals

    staff ->
    staff_id, name, alias, email, password, display

    contributors ->
    contributor_id, article_id, status, name, alias, email, display, type, message

    I want to select from articles (flash, title, category_name) and the number of articles in each cateogry sorted by category_name.

    What I'm shooting for is on the index.php of the knowledge base I have a list of all article categories with the number of entries in each cateogry next to it. When a user clicks on the category name it will drop down a list of all that categories articles by title[i already have the javascript for this ]. I'm not really sure how my initial select statement should go. I'm using MySQL 3.23.56.

    Thanks,
    wassimk

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your first query is:
    Code:
    select categories.category_id
         , category_name
         , count(article_id) as numberofarticles
      from categories
    left outer
      join articles
        on categories.category_id
         = articles.category_id
    group
        by category_name
    order
        by category_name
    but your second query will require more than javascript (unless you use a different first query which sends all articles in all categories, and you do the counts yourself, but this is a strategy i recommend against)

    your second query will be
    Code:
    select modified, flash, read_times, title, body
      from articles
     where category_id = $catid
    order
        by modified desc
    where $catid is the value passed back when the user clicked on category name

    rudy

  3. #3
    SitePoint Member
    Join Date
    Dec 2002
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy for the post! What I was wanting to do is also have the list of articles below each category, for example, http://www.clientexec.com/demo/ click on Forgot Password, it drops down the list. I now realize that'll require a query per category? I'm going to use the queries you've provided and test it out today. I appreciate the post :-).

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    for example, http://www.clientexec.com/demo/ click on Forgot Password, it drops down the list
    nope, what i see when i click on Forgot Password is "Submit your Email or Username if you have forgotten your password" and a text field and submit button

    if your users don't mind the "page weight" of sending all articles in all categories, you can get the display you want, but then you'll have to do the counts yourself


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •