59 users online (1 members and 58 guests)  


  Results 1 to 2 of 2

Related

  1. hair extensions new york ny    Forum: General Discussion
    Replies: 0
  2. Help with SQL query    Forum: Databases
    Replies: 1
  3. Pulling from a directory    Forum: Website Scripts Forum
    Replies: 0
  4. My sql query    Forum: PHP Forum
    Replies: 0
  5. Nested Tables... pulling out my hair    Forum: HTML Forum
    Replies: 4
  1. #1
    canreo's Avatar
    New User

    Status
    Offline
    Join Date
    Aug 2005
    Posts
    1

    Pulling my hair out with this query

    Hello all. I have a PHP/MySQL database that has student grade data. Every student has at least one row of data, though they may have up to 3 rows depending on how many years they have been enrolled (each year gets one row).

    Here is a simple view:

    NAME|YEAR|TEST1|
    -------|------|--------|
    John-|2002|---80|
    Anna-|2002|---67|
    John--|2003|---78|
    Bill----|2003|---76|
    Anna-|2003|---77|
    John-|2004|---88|
    Bill----|2004|---77|
    Phil---|2004|---76|

    Note that John is there for all 3 years, Anna for only the first two, Bill for only the last 2, and Phil came in only in the last year.

    The data needs to be displayed on the web page ordered by name with the 3 years in columns exactly like this:

    Name|2002|2003|2004|
    -------|------|-------|------|
    Anna-|--67-|--77--|null-|
    Bill----|null--|--76--|--77|
    John--|--80-|--78--|--88|
    Phil---|null--|null---|--76|

    Currently I created 4 dataset queries like this:

    "Select distinctrow NAME from mydata ORDER BY NAME"; //returns all names
    "SELECT * FROM mydata WHERE `YEAR` = 2002 ORDER BY NAME";
    "SELECT * FROM mydata WHERE `YEAR` = 2003 ORDER BY NAME";
    "SELECT * FROM mydata WHERE `YEAR` = 2004 ORDER BY NAME";

    I then use the result set to populate the columns.

    The problem is that since not every student has data in all 3 years, the columns begin to shift and the data is placed in the incorrect row like this:

    Name|2002|2003|2004|
    -------|-------|------|------|
    Anna-|--67--|--77-|--77|
    Bill----|--80--|--76-|--88|
    John--|-------|--78-|--76|
    Phil---|-------|null--|-----|

    Is there a way to create ONE query to return data for all of the students, but insert null values where there is no data? Is this done through a "join" statement? What's the best approach?

    Please help, I'm pulling my hair out!

  2. #2
    vinyl-junkie's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    721

    Re: Pulling my hair out with this query

    Is it always going to be the case where you will have no more than 3 years' worth of data per student? If so, you might want to consider restructuring your database like so:

    Student
    Year1
    Test1
    Year2
    Test2
    Year3
    Test3
    If that isn't feasible/practical to do, just make one query like this:

    Code:
    "SELECT * FROM mydata WHERE `YEAR` >= 2002
    AND `YEAR` <= 2002 ORDER BY NAME";
    In your code, you'll need to save each name and print a row only when the name changes, or you've reached the end of the result set. Here's a pseudo-code example of how to process the data:

    Code:
    $row = 1;
    while ($row = mysql_fetch_array($result)) {
    if ($row == 1) {
     // move name to $save_name
    }
    if (name == $save_name) {
     // figure out which cell to put data into
    } else {
     // write out the row
    }
    $row++;
    }
    // write out the last row of data
    Hope this helps.



Tags for this Thread