SELECT DISTINCT with INNER JOIN

PHP
1

I’m trying to solve this…

$sql="SELECT DISTINCT courseid FROM ".$prefix."_stroke
INNER JOIN ".$prefix."_courses ON ".$prefix."_stroke.courseid = ".$prefix."_courses.courseid
WHERE user='$userid'";

This doesn’t get any records, but if I remove the INNER JOIN I get the course id’s… Where do I go wrong?

2

Use “group by”:


SELECT courseid
FROM stroke
INNER JOIN courses
  ON stroke.courseid = courses.courseid
WHERE user = ?
GROUP BY courseid
3

Tryied to set it up like this:

$sql="SELECT courseid
			FROM ".$prefix."_stroke
			INNER JOIN ".$prefix."_courses
			  ON ".$prefix."_stroke.courseid = ".$prefix."_courses.courseid
			WHERE user = '$userid'
			GROUP BY courseid";
			
	$result = mysql_query($sql);
	while($row = mysql_fetch_array($result)){

But get error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/www/mysite.com/test.php on line 33

Any idea?

4

To see the MySql error:


$result = mysql_query($sql) or trigger_error(mysql_error());

Probably, the problem is that courseid is ambiguous. Refer it with the full name (table.column)

5

Ahhh… Thanks. That did the trick :slight_smile:

6