Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Jindabyne, Australia

    Unanswered: Why does array from a JOIN display incorrectly?

    I have the following code.
    $result = mysql_query("SELECT * FROM topics LEFT JOIN quiz ON topics.managerId = quiz.managerId WHERE quiz.userId = '$userId' AND userId= '$userId' AND $egroup = 1 GROUP BY topics.title")or die(mysql_error());
    while ($row = mysql_fetch_array($result)){
    echo "{$row['quizId']} <br />\n";
    echo "{$row['title']} <br />\n";
    echo "{$row['passState']} <br />\n";
    My problem is that the last element "passState" (row 5) echoed in the series of arrays will only display the data taken from the first record encountered by the query. This data is repeated without change throughout the displayed array. "quizId" and "title" come from the "topics" table and "passState" comes from the "quiz" table.
    The display from the other two rows (rows 3 and 4) is fine.
    ($userId and $egroup are values created during the login process and relate to the logged-in user.)
    "passState" is tinyInt and always has a value of 0 or 1. The table has a mix of these values but always repeats the first valeu encountered.
    Can anyone help me with this?

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    you have several problems with that query

    first, since you have WHERE quiz.userId = '$userId' that means that quiz.userId can never be NULL so you should have INNER JOIN instead of LEFT OUTER JOIN

    second, you also have AND userId= '$userId' which makes me wonder if the topics table also has a userid column, but then logic says it cannot, because if it did you'd get the "ambiguous column" error message, so this AND condition is redundant

    you also have a GROUP BY clause but there are no aggregate functions in the SELECT clause, making me wonder what exactly you think you need to aggregate

    finally, the source of your actual problem is likely that you haven't applied any column aliases for columns which happen to have the same name in both tables, due to your use of the dreaded, evil "select star"

    fix these things first, run it again, and then post the new query if it still doesn't work correctly | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Jindabyne, Australia
    Many thanks. I'm at work at the moment. I'll work through your suggestions tonight and will respond in about 8 hours.

Posting Permissions

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