SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy how do I filter ranked results from mysql table?

    I have the following code working perfectly well to rank my students in different classes by highest mark, but when I want to filter by year it's returning Column 'year' in where clause is ambiguous. Help!!!

    PHP Code:
    <?php

    $currentPage 
    $_SERVER["PHP_SELF"];

    $colname_Recordset2 "-1";
    if (isset(
    $_GET['recordID'])) {
      
    $colname_Recordset2 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']);
    }
    mysql_select_db($database_arundel$arundel);
    $query_Recordset2 sprintf("SELECT v1.s_surname, v1.mark, v1.s_name, v1.year, v1.class, COUNT(v2.mark) AS Rank   
    FROM biology v1 
    JOIN biology v2 ON v1.mark < v2.mark OR (v1.mark=v2.mark and v1.s_surname = v2.s_surname)
    WHERE `year` = '%s'
    GROUP BY v1.s_surname, v1.mark 
    ORDER BY  v1.mark DESC"
    $colname_Recordset2);
    $Recordset2 mysql_query($query_Recordset2$arundel) or die(mysql_error());
    $row_Recordset2 mysql_fetch_assoc($Recordset2);
    $totalRows_Recordset2 mysql_num_rows($Recordset2);

    ?>
    Last edited by SpacePhoenix; Feb 27, 2011 at 19:35. Reason: put php tags round php code to improve readability

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you've got two biology tables in the query

    what you want is WHERE v1.year = '%s' or WHERE v2.year = '%s'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi there, thanks fro the response. If I type either v1.year = '%s' or v2.year = '%s' its returning undefined variable, I wonder why its doing that,

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If YEAR exists in more than one table then you have to preface it with the table name or alias (i.e. v1.year) otherwise it won't know which column you are referring to.

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you should specify the table alias as well.
    so instead of year
    use
    v1.year
    It is probably because v2 also has a 'year' column?

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,998
    Mentioned
    100 Post(s)
    Tagged
    0 Thread(s)
    Can you post the output of a SHOW CREATE TABLE for the biology table?

    When working with joins, unless your using an alias for a field you should always refer to a field using the syntax table_name.field_name
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wink

    Oh thanks a lot! the v1.year worked!!!


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
  •