0

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?

3
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by Mikav6
1

I have selected only required columns and echoed query before running it.
When you run this page, copy the query output from browser and run it in phpmyadmin, and see what comes as result

<?php
$query="SELECT topics.quizId, topics.title, quiz.passState' FROM topics LEFT JOIN quiz ON topics.managerId = quiz.managerId WHERE quiz.userId = '$userId' AND userId= '$userId' AND $egroup = 1 GROUP BY topics.title";

echo $query;//when you run this page, copy the query output from browser and run it in phpmyadmin, and see what comes as result

$result = mysql_query($query)OR die(mysql_error());
?>

Edited by urtrivedi: n/a

0

The query seems to contain at least few possible errors:
* 2nd userId : ..AND userId= '$userId'.. should it be topics.userId='$userId' ? (or it's not needed at all). Currently it's open to interpretation as to which userId it points to (if both tables contain this column).
* AND $egroup = 1 .. seems wrong ( '$egroup'=1 ?)
* ..GROUP BY...should this be ORDER BY ? you're not grouping as there are no SUM,MIN,COUNT,etc. statements.
As previous poster said, it's good to explicitly state the columns you select (SELECT topics.quicId, topics.title, quiz.passStare). With small queries it doesn't matter much, but with bigger tables it can have significant impact on server side query optimization and thus on speed.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.