SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql query question

    I have the staff_roll table within my mysql database.

    aid | staffid | Language | Roll | Aditional_Notes

    aid = this is the id for the anime that the staff is relateing to.
    staffid = this is the staffid for the staff information.
    language = this is the language on the version they worked on be it japanese Executive producer or german Executive producer.
    Roll = this is the roll they took eg Executive produce
    aditional_notes = maybe only a guest voice actor for one episode whatever can go in here.

    What i want to do is first get all the staff that worked on a different anime. And then per result all the information on that staff member. Then i want to be able to group results based on their roll.

    eg if i had data:

    1 | 1 | Japanese | Producer |
    1 | 2 | Japanese | 3D Designer |
    1 | 3 | Japanese | Animation |
    1 | 4 | Japanese | Animation |
    1 | 5 | English | Producer |
    1 | 6 | English | Animation |
    1 | 7 | English | Animation | Episode 15 only
    1 | 8 | german | Producer |

    Then i would like the display the data to be displayed as:

    Japanese Staff
    -Producer
    -- Staff1
    -3d Designer
    --Staff2
    -Animation
    --Staff3
    --Staff4
    English Staff
    -Producer
    --Staff5
    -Animation
    --Staff6
    --Staff7 (Episode 15 only)
    German Staff
    -Producer
    --Staff8

    Can i do this with one query? and how would i go about outputting the data in this manner?

    sorry if wrong board, posted here because i imagine the sql will be the easy part and that the solution will be something with some horrendus collection of nested loops

  2. #2
    SitePoint Zealot dustbuster's Avatar
    Join Date
    Jan 2003
    Location
    End-World
    Posts
    148
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you're asking for can be done in one query and simple looping.

    This is untested but it should work

    Code:
    $query="SELECT * FROM staff_roll ORDER BY aid, Language,id,Roll";
    $result=mysql_query($query);
    
    $anime_id=0;
    $language=null;
    
    while ($row=mysql_fetch_assoc($result)){
         if ($anime_id != $row['aid']){
              echo "<h1>Anime ID: $row[aid]</h1>\n"
              $anime_id=$row['aid'];
              $language=null;
         }
         if ($language != $row['Language']){
             echo "<h2>$row[Language] Staff</h2>\n";
             $language = $row['Language'];
         }
         echo "<h3>-$row[Roll]</h3>\n";
         echo "<h3>--Staff$row[staffid] ($row[Aditional_Notes])</h3>\n";
    }
    It'll probably print out funny because of the Header tags but you can use CSS for those or even just replace them with spans or something.
    "If all else fails brute force is always an option."
    M.E.

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks ALOT for that dustbuster. Leant most of my php/sql from looking at other code so dont have most of the actual knowlage in there lol. Yeah dont actualy want it formated like that but with the data being extracted in that manner i can do what i want with it thank you kindly

  4. #4
    SitePoint Zealot dustbuster's Avatar
    Join Date
    Jan 2003
    Location
    End-World
    Posts
    148
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you'll need to change this line:
    Code:
    $query="SELECT * FROM staff_roll ORDER BY aid, Language,id,Roll";
    to
    Code:
    $query="SELECT * FROM staff_roll ORDER BY aid, Language,Roll,id";
    This should make it so the roll's are unique
    "If all else fails brute force is always an option."
    M.E.

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah allready sorted on my own thank you cheers for the quick help

    sorry to keep pestering but how do i link this to the staff table by the staff id so instead of displaying the staffid i can display the linked staff's name. or do i have to do a seperate query?

    i can do a fair bit in php but probably the worst way with many queries. with this project trying to learn more about efficiency and best practice, particulalry database interaction.

    EDIT also is there any way to set a prefrence to which language comes first?



    as you can see this all works correctly but would like japanese to disaply first then english then the other languages can do as they wish
    Attached Images Attached Images

  6. #6
    SitePoint Zealot dustbuster's Avatar
    Join Date
    Jan 2003
    Location
    End-World
    Posts
    148
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can get the name in one query.

    Say your staff table has:

    staff
    -----
    id
    name

    Code:
    $query="SELECT s.name, sr.* FROM staff s, staff_roll sr WHERE s.id=sr.staffid";

    If you have a languages table with English as the first row and Japanese as the second langauge, etc. you can join this query to it and get it that way. Otherwise you can just use if statements in the loop to determine where they appear in the HTML.
    "If all else fails brute force is always an option."
    M.E.

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can do a Custom order using this sql code i discovered.


    Code SQL:
    SELECT info_staff_role.*, info_staff.name FROM info_staff_role, info_staff  WHERE info_staff_role.animeid = ".$animeid." AND info_staff.staffid = info_staff_role.staffid ORDER BY find_in_set(LANGUAGE,'Japanese,English,French,Spanish,German,Italian,Portugese'),ROLE

    find_in_set(fieldname, '1,2,3,4,5')

    where fieldname is some field in the table, and 1 through 5 is results, however i found that if there is a result in the database that isnt in the list that will come first tho so all possible values have to be listed


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
  •