Why does array from a JOIN display incorrectly?

facarroll · #1 (**permalink**) 08-01-11, 20:12

I have the following code.

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?

r937 · #2 (**permalink**) 08-01-11, 20:44

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

facarroll · #3 (**permalink**) 08-01-11, 21:41

Many thanks. I'm at work at the moment. I'll work through your suggestions tonight and will respond in about 8 hours.

Internet Services

Web Development

Digital Marketing

Consumer Tech