SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Advanced MySQL

  1. #1
    SitePoint Addict
    Join Date
    Nov 2000
    Location
    London, UK
    Posts
    223
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advanced MySQL

    Hi,

    I have a question that I've been trying to find the answer to for awhile now. Say I have a table that looks somewhat like this:
    QA:
    INT ID | TEXT Name | TEXT Value | INT Number |INT Parent | CHAR Type

    ID is obviously the index, and is auto_incremented. The others are more or less obvious, except for type which can be:
    - q(uestion)
    - a(answer)
    and Parent, which only applies to answers, and which is the ID of the question that answer corresponds to.
    What I'd like is:
    When I have a question, I want its number to be equal to the sum of the Numbers of all answers who have their Parent equal to that question's ID.

    Is there any way of doing this? I would imagine there is a =SUM() thing, or something like that...

    Thanks a lot,
    Danny

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Since I think I know the answer, your question is beginning mysql not advanced mysql:
    Code:
    $sql = "SELECT SUM(Number) FROM table WHERE Parent = '$id'";
    Here is a link for basic sql syntax:

    http://www.sqlcourse2.com/agg_functions.html
    Last edited by 7stud; Jun 5, 2001 at 11:30.

  3. #3
    SitePoint Addict
    Join Date
    Nov 2000
    Location
    London, UK
    Posts
    223
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    The reason its advanced is because thats not what I want, although you are close

    What I'd like is to have one of my fields automatically update itself. An insert statement might look like: (obviously this isn't real)
    INSERT INTO QA SET Name='dog?',Number=SUM(Number WHERE Parent=this.ID)'

    Do you see what I mean? I want one of my values in the row to update itself automatically whenever the Number for one of its child values changes. Thats why its kinda complicated

    ... what's the world coming to?

  4. #4
    SitePoint Addict
    Join Date
    Nov 2000
    Location
    London, UK
    Posts
    223
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Anybody????

    Hmm...
    Perhaps I should reconsider using this method... Still, does ANYBODY else have anything to say?? Please??

    Thanks,
    Danny


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
  •